search

ziutek / mymysql

MySQL Client API written entirely in Go
Other
735 stars 161 forks source link

Datetime update fail on v1.4.8 #74

Open ex-troll opened 11 years ago

ex-troll commented 11 years ago

Hello.

I am try to use v1.4.8 mymysql with go1.0.3.

On SELECT requests DATETIME cell parse is normal. But can't UPDATE it, all DATETIME datas set as 0001-01-01 00:00:00 +0000 UTC. There is no exceptions or any error messages.

On old version v1.1 of mymysql all work well (for that version).

OS: CentOS MySQL: 5.0.95

ziutek commented 11 years ago

Can you provide some example code that I can use to reproduce this issue?

ex-troll commented 11 years ago

Hello. I apologize for the silence.

Example code:

/*
 * INIT DB:
USE `test`;
ALTER DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'testusr'@'192.168.123.102' IDENTIFIED BY 'testpwd';

CREATE TABLE IF NOT EXISTS `test`.`testdrv` (
    `id` INT UNSIGNED AUTO_INCREMENT NOT NULL ,
    `date` DATETIME NOT NULL ,
    PRIMARY KEY ( `id` ) ,
    UNIQUE ( `id` )
) ENGINE = INNODB DEFAULT CHARSET=utf8;
 */
package main

import (
    "fmt"; "log";
    "time";
    "database/sql";
    _ "github.com/ziutek/mymysql/godrv";
)

var (
    DB_PROTOCOL = "tcp"
    DB_ADDRESS  = "192.168.123.102"
    DB_PORT     = "3306"
    DB_NAME     = "test"
    DB_USER     = "testusr"
    DB_PASS     = "testpwd"
)
//Connect to DB
func OpenDB(proto, addr, port, name, user, pass string) (*sql.DB, error) {
    if proto != "" {
        db, err := sql.Open("mymysql", fmt.Sprintf("%s:%s:%s*%s/%s/%s", proto, addr, port, name, user, pass))

        if err != nil {
            log.Printf("sql.Open error: %v", err.Error())
        }

        return db, err
    } else {
        db, err := sql.Open("mymysql", fmt.Sprintf("%s:%s:%s*%s/%s/%s", DB_PROTOCOL, DB_ADDRESS, DB_PORT, DB_NAME, DB_USER, DB_PASS))

        if err != nil {
            log.Printf("sql.Open error: %v", err.Error())
        }

        return db, err
    }

    return nil, nil
}
//`test`.`testdrv`
type TestDrv struct {
    ID                  int64       `json:"id"`
    Date                time.Time   `json:"date"`
}

func (td *TestDrv) GetAllRecords() {
    db, err := OpenDB("", "", "", "", "", "")

    if err != nil {
        log.Printf("OpenDB error: %v", err.Error())
        return
    }

    defer db.Close()
    rows, err := db.Query("SELECT `id`, `date` FROM `test`.`testdrv` ORDER BY `date`")

    if err != nil {
        log.Printf(err.Error())
    } else {
        for rows.Next() {
            err = rows.Scan(&td.ID, &td.Date)

            if err != nil {
                log.Printf("rows.Scan error: %v", err.Error())
            }

            log.Printf("td: %v", td)
        }
    }

    return
}

func (td *TestDrv) CreateRecordWithID(id int64) (int64, error) {
    db, err := OpenDB("", "", "", "", "", "")

    if err != nil {
        log.Printf(err.Error())
        return -1, err
    }

    defer db.Close()
    res, err := db.Exec("INSERT INTO `test`.`testdrv` (`id`, `date`) VALUES (?, NOW())", id)

    if err != nil {
        log.Printf(err.Error())
        return -1, err
    }

    val, err := res.RowsAffected()

    if err != nil {
        log.Printf(err.Error())
        return -1, err
    }

    val, err = res.LastInsertId()

    if err != nil {
        log.Printf(err.Error())
        return -1, err
    }

    return val, nil
}

func (td *TestDrv) UpdateRecord() (int64, error) {
    db, err := OpenDB("", "", "", "", "", "")

    if err != nil {
        log.Printf(err.Error())
        return 0, err
    }

    defer db.Close()
    res, err := db.Exec("UPDATE `test`.`testdrv` SET `date` = ? WHERE `id` = ?", td.Date, td.ID)

    if err != nil {
        log.Printf(err.Error())
        return 0, err
    }

    val, err := res.RowsAffected()

    if err != nil {
        log.Printf(err.Error())
        return 0, err
    }

    return val, nil
}

func main() {
    td := new(TestDrv)

    for i := int64(1); i < 11; i++ {
        td.CreateRecordWithID(i)
        time.Sleep(1 * time.Second)
    }

    td.GetAllRecords()
    log.Printf("============================================================")

    for i := int64(1); i < 11; i++ {
        drtn, _ := time.ParseDuration("5m")
        tmp_td := new(TestDrv)
        tmp_td.ID = i
        tmp_td.Date = time.Now().Add(drtn)
        log.Printf("td: %v", tmp_td)
        ret_val, err := tmp_td.UpdateRecord()

        if err != nil || ret_val < 1 {
            if err != nil {
                log.Printf("Error: %s", err.Error())
            } else {
                log.Printf("Error: <nil> | Rows updated: %d", ret_val)
            }

            return
        }
    }

    log.Printf("============================================================")
    td.GetAllRecords()

    return
}

Go version: go1.0.3 mymysql version: v1.0.3

Execute it:

> go run test_mymysql.go 
2013/07/25 19:55:09 td: &{10 2013-07-25 19:55:08 +0400 MSK}
2013/07/25 19:55:09 td: &{9 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{8 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{7 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{6 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{5 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{4 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{3 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{2 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 td: &{1 2013-07-25 19:59:22 +0400 MSK}
2013/07/25 19:55:09 ============================================================
2013/07/25 19:55:09 td: &{1 2013-07-25 20:00:09.363022 +0400 MSK}
2013/07/25 19:55:09 td: &{2 2013-07-25 20:00:09.416793 +0400 MSK}
2013/07/25 19:55:09 td: &{3 2013-07-25 20:00:09.453085 +0400 MSK}
2013/07/25 19:55:09 td: &{4 2013-07-25 20:00:09.497305 +0400 MSK}
2013/07/25 19:55:09 td: &{5 2013-07-25 20:00:09.504555 +0400 MSK}
2013/07/25 19:55:09 td: &{6 2013-07-25 20:00:09.512891 +0400 MSK}
2013/07/25 19:55:09 td: &{7 2013-07-25 20:00:09.521378 +0400 MSK}
2013/07/25 19:55:09 td: &{8 2013-07-25 20:00:09.529544 +0400 MSK}
2013/07/25 19:55:09 td: &{9 2013-07-25 20:00:09.546086 +0400 MSK}
2013/07/25 19:55:09 td: &{10 2013-07-25 20:00:09.554723 +0400 MSK}
2013/07/25 19:55:09 ============================================================
2013/07/25 19:55:09 td: &{1 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{9 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{8 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{7 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{6 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{5 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{4 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{3 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{2 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:09 td: &{10 2013-07-25 20:00:09 +0400 MSK}
>

Go version: go1.0.3 mymysql version: v1.4.8

Execute it:

> go run test_mymysql.go 
2013/07/25 19:55:48 Received #1062 error from MySQL server: "Duplicate entry '1' for key 1"
2013/07/25 19:55:49 Received #1062 error from MySQL server: "Duplicate entry '2' for key 1"
2013/07/25 19:55:50 Received #1062 error from MySQL server: "Duplicate entry '3' for key 1"
2013/07/25 19:55:51 Received #1062 error from MySQL server: "Duplicate entry '4' for key 1"
2013/07/25 19:55:52 Received #1062 error from MySQL server: "Duplicate entry '5' for key 1"
2013/07/25 19:55:53 Received #1062 error from MySQL server: "Duplicate entry '6' for key 1"
2013/07/25 19:55:54 Received #1062 error from MySQL server: "Duplicate entry '7' for key 1"
2013/07/25 19:55:55 Received #1062 error from MySQL server: "Duplicate entry '8' for key 1"
2013/07/25 19:55:56 Received #1062 error from MySQL server: "Duplicate entry '9' for key 1"
2013/07/25 19:55:57 Received #1062 error from MySQL server: "Duplicate entry '10' for key 1"
2013/07/25 19:55:58 td: &{1 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{9 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{8 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{7 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{6 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{5 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{4 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{3 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{2 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 td: &{10 2013-07-25 20:00:09 +0400 MSK}
2013/07/25 19:55:58 ============================================================
2013/07/25 19:55:58 td: &{1 2013-07-25 20:00:58.524079 +0400 MSK}
2013/07/25 19:55:58 td: &{2 2013-07-25 20:00:58.549049 +0400 MSK}
2013/07/25 19:55:58 td: &{3 2013-07-25 20:00:58.561546 +0400 MSK}
2013/07/25 19:55:58 td: &{4 2013-07-25 20:00:58.569919 +0400 MSK}
2013/07/25 19:55:58 td: &{5 2013-07-25 20:00:58.57815 +0400 MSK}
2013/07/25 19:55:58 td: &{6 2013-07-25 20:00:58.586558 +0400 MSK}
2013/07/25 19:55:58 td: &{7 2013-07-25 20:00:58.59485 +0400 MSK}
2013/07/25 19:55:58 td: &{8 2013-07-25 20:00:58.603146 +0400 MSK}
2013/07/25 19:55:58 td: &{9 2013-07-25 20:00:58.611498 +0400 MSK}
2013/07/25 19:55:58 td: &{10 2013-07-25 20:00:58.61984 +0400 MSK}
2013/07/25 19:55:58 ============================================================
2013/07/25 19:55:58 td: &{1 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{9 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{8 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{7 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{6 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{5 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{4 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{3 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{2 0001-01-01 00:00:00 +0000 UTC}
2013/07/25 19:55:58 td: &{10 0001-01-01 00:00:00 +0000 UTC}
ziutek commented 11 years ago

I run your test code in my system: go version go1.1.1 linux/amd64 mymysql v1.5

2013/07/27 00:23:22 td: &{1 2013-07-27 00:23:12 +0200 CEST} 2013/07/27 00:23:22 td: &{2 2013-07-27 00:23:13 +0200 CEST} 2013/07/27 00:23:22 td: &{3 2013-07-27 00:23:14 +0200 CEST} 2013/07/27 00:23:22 td: &{4 2013-07-27 00:23:15 +0200 CEST} 2013/07/27 00:23:22 td: &{5 2013-07-27 00:23:16 +0200 CEST} 2013/07/27 00:23:22 td: &{6 2013-07-27 00:23:17 +0200 CEST} 2013/07/27 00:23:22 td: &{7 2013-07-27 00:23:18 +0200 CEST} 2013/07/27 00:23:22 td: &{8 2013-07-27 00:23:19 +0200 CEST} 2013/07/27 00:23:22 td: &{9 2013-07-27 00:23:20 +0200 CEST} 2013/07/27 00:23:22 td: &{10 2013-07-27 00:23:21 +0200 CEST} 2013/07/27 00:23:22 ============================================================ 2013/07/27 00:23:22 td: &{1 2013-07-27 00:28:22.938990204 +0200 CEST} 2013/07/27 00:23:22 td: &{2 2013-07-27 00:28:22.970377226 +0200 CEST} 2013/07/27 00:23:23 td: &{3 2013-07-27 00:28:23.003976401 +0200 CEST} 2013/07/27 00:23:23 td: &{4 2013-07-27 00:28:23.03738896 +0200 CEST} 2013/07/27 00:23:23 td: &{5 2013-07-27 00:28:23.070939805 +0200 CEST} 2013/07/27 00:23:23 td: &{6 2013-07-27 00:28:23.104431424 +0200 CEST} 2013/07/27 00:23:23 td: &{7 2013-07-27 00:28:23.137957615 +0200 CEST} 2013/07/27 00:23:23 td: &{8 2013-07-27 00:28:23.17142898 +0200 CEST} 2013/07/27 00:23:23 td: &{9 2013-07-27 00:28:23.204972212 +0200 CEST} 2013/07/27 00:23:23 td: &{10 2013-07-27 00:28:23.238491838 +0200 CEST} 2013/07/27 00:23:23 ============================================================ 2013/07/27 00:23:23 td: &{1 2013-07-27 00:28:22 +0200 CEST} 2013/07/27 00:23:23 td: &{2 2013-07-27 00:28:22 +0200 CEST} 2013/07/27 00:23:23 td: &{9 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{8 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{7 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{6 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{5 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{4 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{3 2013-07-27 00:28:23 +0200 CEST} 2013/07/27 00:23:23 td: &{10 2013-07-27 00:28:23 +0200 CEST}

Please update mymysql to v1.5 and try your test wit it.