1 // Go MySQL Driver - A MySQL-Driver for Go's database/sql package
3 // Copyright 2013 The Go-MySQL-Driver Authors. All rights reserved.
5 // This Source Code Form is subject to the terms of the Mozilla Public
6 // License, v. 2.0. If a copy of the MPL was not distributed with this file,
7 // You can obtain one at http://mozilla.org/MPL/2.0/.
33 // Ensure that all the driver interfaces are implemented
35 _ driver.Rows = &binaryRows{}
36 _ driver.Rows = &textRows{}
51 tDate = time.Date(2012, 6, 14, 0, 0, 0, 0, time.UTC)
53 tDateTime = time.Date(2011, 11, 20, 21, 27, 37, 0, time.UTC)
54 sDateTime = "2011-11-20 21:27:37"
57 sDateTime0 = "0000-00-00 00:00:00"
60 // See https://github.com/go-sql-driver/mysql/wiki/Testing
62 // get environment variables
63 env := func(key, defaultValue string) string {
64 if value := os.Getenv(key); value != "" {
69 user = env("MYSQL_TEST_USER", "root")
70 pass = env("MYSQL_TEST_PASS", "")
71 prot = env("MYSQL_TEST_PROT", "tcp")
72 addr = env("MYSQL_TEST_ADDR", "localhost:3306")
73 dbname = env("MYSQL_TEST_DBNAME", "gotest")
74 netAddr = fmt.Sprintf("%s(%s)", prot, addr)
75 dsn = fmt.Sprintf("%s:%s@%s/%s?timeout=30s", user, pass, netAddr, dbname)
76 c, err := net.Dial(prot, addr)
88 func runTestsWithMultiStatement(t *testing.T, dsn string, tests ...func(dbt *DBTest)) {
90 t.Skipf("MySQL server not running on %s", netAddr)
93 dsn += "&multiStatements=true"
95 if _, err := ParseDSN(dsn); err != errInvalidDSNUnsafeCollation {
96 db, err = sql.Open("mysql", dsn)
98 t.Fatalf("error connecting: %s", err.Error())
103 dbt := &DBTest{t, db}
104 for _, test := range tests {
106 dbt.db.Exec("DROP TABLE IF EXISTS test")
110 func runTests(t *testing.T, dsn string, tests ...func(dbt *DBTest)) {
112 t.Skipf("MySQL server not running on %s", netAddr)
115 db, err := sql.Open("mysql", dsn)
117 t.Fatalf("error connecting: %s", err.Error())
121 db.Exec("DROP TABLE IF EXISTS test")
123 dsn2 := dsn + "&interpolateParams=true"
125 if _, err := ParseDSN(dsn2); err != errInvalidDSNUnsafeCollation {
126 db2, err = sql.Open("mysql", dsn2)
128 t.Fatalf("error connecting: %s", err.Error())
133 dsn3 := dsn + "&multiStatements=true"
135 if _, err := ParseDSN(dsn3); err != errInvalidDSNUnsafeCollation {
136 db3, err = sql.Open("mysql", dsn3)
138 t.Fatalf("error connecting: %s", err.Error())
143 dbt := &DBTest{t, db}
144 dbt2 := &DBTest{t, db2}
145 dbt3 := &DBTest{t, db3}
146 for _, test := range tests {
148 dbt.db.Exec("DROP TABLE IF EXISTS test")
151 dbt2.db.Exec("DROP TABLE IF EXISTS test")
155 dbt3.db.Exec("DROP TABLE IF EXISTS test")
160 func (dbt *DBTest) fail(method, query string, err error) {
161 if len(query) > 300 {
162 query = "[query too large to print]"
164 dbt.Fatalf("error on %s %s: %s", method, query, err.Error())
167 func (dbt *DBTest) mustExec(query string, args ...interface{}) (res sql.Result) {
168 res, err := dbt.db.Exec(query, args...)
170 dbt.fail("exec", query, err)
175 func (dbt *DBTest) mustQuery(query string, args ...interface{}) (rows *sql.Rows) {
176 rows, err := dbt.db.Query(query, args...)
178 dbt.fail("query", query, err)
183 func maybeSkip(t *testing.T, err error, skipErrno uint16) {
184 mySQLErr, ok := err.(*MySQLError)
189 if mySQLErr.Number == skipErrno {
190 t.Skipf("skipping test for error: %v", err)
194 func TestEmptyQuery(t *testing.T) {
195 runTests(t, dsn, func(dbt *DBTest) {
196 // just a comment, no query
197 rows := dbt.mustQuery("--")
198 // will hang before #255
200 dbt.Errorf("next on rows must be false")
205 func TestCRUD(t *testing.T) {
206 runTests(t, dsn, func(dbt *DBTest) {
208 dbt.mustExec("CREATE TABLE test (value BOOL)")
210 // Test for unexpected data
212 rows := dbt.mustQuery("SELECT * FROM test")
214 dbt.Error("unexpected data in empty table")
218 res := dbt.mustExec("INSERT INTO test VALUES (1)")
219 count, err := res.RowsAffected()
221 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
224 dbt.Fatalf("expected 1 affected row, got %d", count)
227 id, err := res.LastInsertId()
229 dbt.Fatalf("res.LastInsertId() returned error: %s", err.Error())
232 dbt.Fatalf("expected InsertId 0, got %d", id)
236 rows = dbt.mustQuery("SELECT value FROM test")
240 dbt.Errorf("true != %t", out)
244 dbt.Error("unexpected data")
251 res = dbt.mustExec("UPDATE test SET value = ? WHERE value = ?", false, true)
252 count, err = res.RowsAffected()
254 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
257 dbt.Fatalf("expected 1 affected row, got %d", count)
261 rows = dbt.mustQuery("SELECT value FROM test")
265 dbt.Errorf("false != %t", out)
269 dbt.Error("unexpected data")
276 res = dbt.mustExec("DELETE FROM test WHERE value = ?", false)
277 count, err = res.RowsAffected()
279 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
282 dbt.Fatalf("expected 1 affected row, got %d", count)
285 // Check for unexpected rows
286 res = dbt.mustExec("DELETE FROM test")
287 count, err = res.RowsAffected()
289 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
292 dbt.Fatalf("expected 0 affected row, got %d", count)
297 func TestMultiQuery(t *testing.T) {
298 runTestsWithMultiStatement(t, dsn, func(dbt *DBTest) {
300 dbt.mustExec("CREATE TABLE `test` (`id` int(11) NOT NULL, `value` int(11) NOT NULL) ")
303 res := dbt.mustExec("INSERT INTO test VALUES (1, 1)")
304 count, err := res.RowsAffected()
306 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
309 dbt.Fatalf("expected 1 affected row, got %d", count)
313 res = dbt.mustExec("UPDATE test SET value = 3 WHERE id = 1; UPDATE test SET value = 4 WHERE id = 1; UPDATE test SET value = 5 WHERE id = 1;")
314 count, err = res.RowsAffected()
316 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
319 dbt.Fatalf("expected 1 affected row, got %d", count)
324 rows := dbt.mustQuery("SELECT value FROM test WHERE id=1;")
328 dbt.Errorf("5 != %d", out)
332 dbt.Error("unexpected data")
341 func TestInt(t *testing.T) {
342 runTests(t, dsn, func(dbt *DBTest) {
343 types := [5]string{"TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT"}
349 for _, v := range types {
350 dbt.mustExec("CREATE TABLE test (value " + v + ")")
352 dbt.mustExec("INSERT INTO test VALUES (?)", in)
354 rows = dbt.mustQuery("SELECT value FROM test")
358 dbt.Errorf("%s: %d != %d", v, in, out)
361 dbt.Errorf("%s: no data", v)
364 dbt.mustExec("DROP TABLE IF EXISTS test")
368 for _, v := range types {
369 dbt.mustExec("CREATE TABLE test (value " + v + " ZEROFILL)")
371 dbt.mustExec("INSERT INTO test VALUES (?)", in)
373 rows = dbt.mustQuery("SELECT value FROM test")
377 dbt.Errorf("%s ZEROFILL: %d != %d", v, in, out)
380 dbt.Errorf("%s ZEROFILL: no data", v)
383 dbt.mustExec("DROP TABLE IF EXISTS test")
388 func TestFloat32(t *testing.T) {
389 runTests(t, dsn, func(dbt *DBTest) {
390 types := [2]string{"FLOAT", "DOUBLE"}
394 for _, v := range types {
395 dbt.mustExec("CREATE TABLE test (value " + v + ")")
396 dbt.mustExec("INSERT INTO test VALUES (?)", in)
397 rows = dbt.mustQuery("SELECT value FROM test")
401 dbt.Errorf("%s: %g != %g", v, in, out)
404 dbt.Errorf("%s: no data", v)
406 dbt.mustExec("DROP TABLE IF EXISTS test")
411 func TestFloat64(t *testing.T) {
412 runTests(t, dsn, func(dbt *DBTest) {
413 types := [2]string{"FLOAT", "DOUBLE"}
414 var expected float64 = 42.23
417 for _, v := range types {
418 dbt.mustExec("CREATE TABLE test (value " + v + ")")
419 dbt.mustExec("INSERT INTO test VALUES (42.23)")
420 rows = dbt.mustQuery("SELECT value FROM test")
424 dbt.Errorf("%s: %g != %g", v, expected, out)
427 dbt.Errorf("%s: no data", v)
429 dbt.mustExec("DROP TABLE IF EXISTS test")
434 func TestFloat64Placeholder(t *testing.T) {
435 runTests(t, dsn, func(dbt *DBTest) {
436 types := [2]string{"FLOAT", "DOUBLE"}
437 var expected float64 = 42.23
440 for _, v := range types {
441 dbt.mustExec("CREATE TABLE test (id int, value " + v + ")")
442 dbt.mustExec("INSERT INTO test VALUES (1, 42.23)")
443 rows = dbt.mustQuery("SELECT value FROM test WHERE id = ?", 1)
447 dbt.Errorf("%s: %g != %g", v, expected, out)
450 dbt.Errorf("%s: no data", v)
452 dbt.mustExec("DROP TABLE IF EXISTS test")
457 func TestString(t *testing.T) {
458 runTests(t, dsn, func(dbt *DBTest) {
459 types := [6]string{"CHAR(255)", "VARCHAR(255)", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT"}
460 in := "κόσμε üöäßñóùéàâÿœ'îë Árvíztűrő いろはにほへとちりぬるを イロハニホヘト דג סקרן чащах น่าฟังเอย"
464 for _, v := range types {
465 dbt.mustExec("CREATE TABLE test (value " + v + ") CHARACTER SET utf8")
467 dbt.mustExec("INSERT INTO test VALUES (?)", in)
469 rows = dbt.mustQuery("SELECT value FROM test")
473 dbt.Errorf("%s: %s != %s", v, in, out)
476 dbt.Errorf("%s: no data", v)
479 dbt.mustExec("DROP TABLE IF EXISTS test")
483 dbt.mustExec("CREATE TABLE test (id int, value BLOB) CHARACTER SET utf8")
486 in = "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, " +
487 "sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, " +
488 "sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. " +
489 "Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. " +
490 "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, " +
491 "sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, " +
492 "sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. " +
493 "Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet."
494 dbt.mustExec("INSERT INTO test VALUES (?, ?)", id, in)
496 err := dbt.db.QueryRow("SELECT value FROM test WHERE id = ?", id).Scan(&out)
498 dbt.Fatalf("Error on BLOB-Query: %s", err.Error())
499 } else if out != in {
500 dbt.Errorf("BLOB: %s != %s", in, out)
505 func TestRawBytes(t *testing.T) {
506 runTests(t, dsn, func(dbt *DBTest) {
509 rows := dbt.mustQuery("SELECT ?, ?", v1, v2)
511 var o1, o2 sql.RawBytes
512 if err := rows.Scan(&o1, &o2); err != nil {
513 dbt.Errorf("Got error: %v", err)
515 if !bytes.Equal(v1, o1) {
516 dbt.Errorf("expected %v, got %v", v1, o1)
518 if !bytes.Equal(v2, o2) {
519 dbt.Errorf("expected %v, got %v", v2, o2)
521 // https://github.com/go-sql-driver/mysql/issues/765
522 // Appending to RawBytes shouldn't overwrite next RawBytes.
523 o1 = append(o1, "xyzzy"...)
524 if !bytes.Equal(v2, o2) {
525 dbt.Errorf("expected %v, got %v", v2, o2)
528 dbt.Errorf("no data")
533 type testValuer struct {
537 func (tv testValuer) Value() (driver.Value, error) {
541 func TestValuer(t *testing.T) {
542 runTests(t, dsn, func(dbt *DBTest) {
543 in := testValuer{"a_value"}
547 dbt.mustExec("CREATE TABLE test (value VARCHAR(255)) CHARACTER SET utf8")
548 dbt.mustExec("INSERT INTO test VALUES (?)", in)
549 rows = dbt.mustQuery("SELECT value FROM test")
553 dbt.Errorf("Valuer: %v != %s", in, out)
556 dbt.Errorf("Valuer: no data")
559 dbt.mustExec("DROP TABLE IF EXISTS test")
563 type testValuerWithValidation struct {
567 func (tv testValuerWithValidation) Value() (driver.Value, error) {
568 if len(tv.value) == 0 {
569 return nil, fmt.Errorf("Invalid string valuer. Value must not be empty")
575 func TestValuerWithValidation(t *testing.T) {
576 runTests(t, dsn, func(dbt *DBTest) {
577 in := testValuerWithValidation{"a_value"}
581 dbt.mustExec("CREATE TABLE testValuer (value VARCHAR(255)) CHARACTER SET utf8")
582 dbt.mustExec("INSERT INTO testValuer VALUES (?)", in)
584 rows = dbt.mustQuery("SELECT value FROM testValuer")
590 dbt.Errorf("Valuer: %v != %s", in, out)
593 dbt.Errorf("Valuer: no data")
596 if _, err := dbt.db.Exec("INSERT INTO testValuer VALUES (?)", testValuerWithValidation{""}); err == nil {
597 dbt.Errorf("Failed to check valuer error")
600 if _, err := dbt.db.Exec("INSERT INTO testValuer VALUES (?)", nil); err != nil {
601 dbt.Errorf("Failed to check nil")
604 if _, err := dbt.db.Exec("INSERT INTO testValuer VALUES (?)", map[string]bool{}); err == nil {
605 dbt.Errorf("Failed to check not valuer")
608 dbt.mustExec("DROP TABLE IF EXISTS testValuer")
612 type timeTests struct {
618 type timeTest struct {
619 s string // leading "!": do not use t as value in queries
625 func (t timeMode) String() string {
628 return "binary:string"
630 return "binary:time.Time"
634 panic("unsupported timeMode")
637 func (t timeMode) Binary() bool {
639 case binaryString, binaryTime:
646 binaryString timeMode = iota
651 func (t timeTest) genQuery(dbtype string, mode timeMode) string {
658 return `SELECT cast(` + inner + ` as ` + dbtype + `)`
661 func (t timeTest) run(dbt *DBTest, dbtype, tlayout string, mode timeMode) {
663 query := t.genQuery(dbtype, mode)
666 rows = dbt.mustQuery(query, t.s)
668 rows = dbt.mustQuery(query, t.t)
670 query = fmt.Sprintf(query, t.s)
671 rows = dbt.mustQuery(query)
673 panic("unsupported mode")
680 err = fmt.Errorf("no data")
682 dbt.Errorf("%s [%s]: %s", dbtype, mode, err)
686 err = rows.Scan(&dst)
688 dbt.Errorf("%s [%s]: %s", dbtype, mode, err)
691 switch val := dst.(type) {
697 if mode.Binary() && dbtype == "DATETIME" && len(str) == 26 && str[:19] == t.s {
698 // a fix mainly for TravisCI:
699 // accept full microsecond resolution in result for DATETIME columns
700 // where the binary protocol was used
703 dbt.Errorf("%s [%s] to string: expected %q, got %q",
711 dbt.Errorf("%s [%s] to string: expected %q, got %q",
713 t.s, val.Format(tlayout),
716 fmt.Printf("%#v\n", []interface{}{dbtype, tlayout, mode, t.s, t.t})
717 dbt.Errorf("%s [%s]: unhandled type %T (is '%v')",
724 func TestDateTime(t *testing.T) {
725 afterTime := func(t time.Time, d string) time.Time {
726 dur, err := time.ParseDuration(d)
732 // NOTE: MySQL rounds DATETIME(x) up - but that's not included in the tests
733 format := "2006-01-02 15:04:05.999999"
735 tstr0 := "0000-00-00 00:00:00.000000"
736 testcases := []timeTests{
737 {"DATE", format[:10], []timeTest{
738 {t: time.Date(2011, 11, 20, 0, 0, 0, 0, time.UTC)},
739 {t: t0, s: tstr0[:10]},
741 {"DATETIME", format[:19], []timeTest{
742 {t: time.Date(2011, 11, 20, 21, 27, 37, 0, time.UTC)},
743 {t: t0, s: tstr0[:19]},
745 {"DATETIME(0)", format[:21], []timeTest{
746 {t: time.Date(2011, 11, 20, 21, 27, 37, 0, time.UTC)},
747 {t: t0, s: tstr0[:19]},
749 {"DATETIME(1)", format[:21], []timeTest{
750 {t: time.Date(2011, 11, 20, 21, 27, 37, 100000000, time.UTC)},
751 {t: t0, s: tstr0[:21]},
753 {"DATETIME(6)", format, []timeTest{
754 {t: time.Date(2011, 11, 20, 21, 27, 37, 123456000, time.UTC)},
757 {"TIME", format[11:19], []timeTest{
758 {t: afterTime(t0, "12345s")},
762 {t: t0, s: tstr0[11:19]},
764 {"TIME(0)", format[11:19], []timeTest{
765 {t: afterTime(t0, "12345s")},
769 {t: t0, s: tstr0[11:19]},
771 {"TIME(1)", format[11:21], []timeTest{
772 {t: afterTime(t0, "12345600ms")},
774 {s: "!-838:59:58.9"},
776 {t: t0, s: tstr0[11:21]},
778 {"TIME(6)", format[11:], []timeTest{
779 {t: afterTime(t0, "1234567890123000ns")},
780 {s: "!-12:34:56.789012"},
781 {s: "!-838:59:58.999999"},
782 {s: "!838:59:58.999999"},
783 {t: t0, s: tstr0[11:]},
787 dsn + "&parseTime=true",
788 dsn + "&parseTime=false",
790 for _, testdsn := range dsns {
791 runTests(t, testdsn, func(dbt *DBTest) {
792 microsecsSupported := false
793 zeroDateSupported := false
796 rows, err = dbt.db.Query(`SELECT cast("00:00:00.1" as TIME(1)) = "00:00:00.1"`)
798 rows.Scan(µsecsSupported)
801 rows, err = dbt.db.Query(`SELECT cast("0000-00-00" as DATE) = "0000-00-00"`)
803 rows.Scan(&zeroDateSupported)
806 for _, setups := range testcases {
807 if t := setups.dbtype; !microsecsSupported && t[len(t)-1:] == ")" {
808 // skip fractional second tests if unsupported by server
811 for _, setup := range setups.tests {
814 // fill time string wherever Go can reliable produce it
815 setup.s = setup.t.Format(setups.tlayout)
816 } else if setup.s[0] == '!' {
817 // skip tests using setup.t as source in queries
819 // fix setup.s - remove the "!"
820 setup.s = setup.s[1:]
822 if !zeroDateSupported && setup.s == tstr0[:len(setup.s)] {
823 // skip disallowed 0000-00-00 date
826 setup.run(dbt, setups.dbtype, setups.tlayout, textString)
827 setup.run(dbt, setups.dbtype, setups.tlayout, binaryString)
829 setup.run(dbt, setups.dbtype, setups.tlayout, binaryTime)
837 func TestTimestampMicros(t *testing.T) {
838 format := "2006-01-02 15:04:05.999999"
842 runTests(t, dsn, func(dbt *DBTest) {
843 // check if microseconds are supported.
844 // Do not use timestamp(x) for that check - before 5.5.6, x would mean display width
845 // and not precision.
846 // Se last paragraph at http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
847 microsecsSupported := false
848 if rows, err := dbt.db.Query(`SELECT cast("00:00:00.1" as TIME(1)) = "00:00:00.1"`); err == nil {
849 rows.Scan(µsecsSupported)
852 if !microsecsSupported {
856 _, err := dbt.db.Exec(`
858 value0 TIMESTAMP NOT NULL DEFAULT '` + f0 + `',
859 value1 TIMESTAMP(1) NOT NULL DEFAULT '` + f1 + `',
860 value6 TIMESTAMP(6) NOT NULL DEFAULT '` + f6 + `'
866 defer dbt.mustExec("DROP TABLE IF EXISTS test")
867 dbt.mustExec("INSERT INTO test SET value0=?, value1=?, value6=?", f0, f1, f6)
868 var res0, res1, res6 string
869 rows := dbt.mustQuery("SELECT * FROM test")
871 dbt.Errorf("test contained no selectable values")
873 err = rows.Scan(&res0, &res1, &res6)
878 dbt.Errorf("expected %q, got %q", f0, res0)
881 dbt.Errorf("expected %q, got %q", f1, res1)
884 dbt.Errorf("expected %q, got %q", f6, res6)
889 func TestNULL(t *testing.T) {
890 runTests(t, dsn, func(dbt *DBTest) {
891 nullStmt, err := dbt.db.Prepare("SELECT NULL")
895 defer nullStmt.Close()
897 nonNullStmt, err := dbt.db.Prepare("SELECT 1")
901 defer nonNullStmt.Close()
906 if err = nullStmt.QueryRow().Scan(&nb); err != nil {
910 dbt.Error("valid NullBool which should be invalid")
913 if err = nonNullStmt.QueryRow().Scan(&nb); err != nil {
917 dbt.Error("invalid NullBool which should be valid")
918 } else if nb.Bool != true {
919 dbt.Errorf("Unexpected NullBool value: %t (should be true)", nb.Bool)
923 var nf sql.NullFloat64
925 if err = nullStmt.QueryRow().Scan(&nf); err != nil {
929 dbt.Error("valid NullFloat64 which should be invalid")
932 if err = nonNullStmt.QueryRow().Scan(&nf); err != nil {
936 dbt.Error("invalid NullFloat64 which should be valid")
937 } else if nf.Float64 != float64(1) {
938 dbt.Errorf("unexpected NullFloat64 value: %f (should be 1.0)", nf.Float64)
944 if err = nullStmt.QueryRow().Scan(&ni); err != nil {
948 dbt.Error("valid NullInt64 which should be invalid")
951 if err = nonNullStmt.QueryRow().Scan(&ni); err != nil {
955 dbt.Error("invalid NullInt64 which should be valid")
956 } else if ni.Int64 != int64(1) {
957 dbt.Errorf("unexpected NullInt64 value: %d (should be 1)", ni.Int64)
961 var ns sql.NullString
963 if err = nullStmt.QueryRow().Scan(&ns); err != nil {
967 dbt.Error("valid NullString which should be invalid")
970 if err = nonNullStmt.QueryRow().Scan(&ns); err != nil {
974 dbt.Error("invalid NullString which should be valid")
975 } else if ns.String != `1` {
976 dbt.Error("unexpected NullString value:" + ns.String + " (should be `1`)")
982 if err = nullStmt.QueryRow().Scan(&b); err != nil {
986 dbt.Error("non-nil []byte which should be nil")
989 if err = nonNullStmt.QueryRow().Scan(&b); err != nil {
993 dbt.Error("nil []byte which should be non-nil")
998 if err = dbt.db.QueryRow("SELECT ? IS NULL", b).Scan(&success); err != nil {
1002 dbt.Error("inserting []byte(nil) as NULL failed")
1004 // Check input==output with input==nil
1006 if err = dbt.db.QueryRow("SELECT ?", b).Scan(&b); err != nil {
1010 dbt.Error("non-nil echo from nil input")
1012 // Check input==output with input!=nil
1014 if err = dbt.db.QueryRow("SELECT ?", b).Scan(&b); err != nil {
1018 dbt.Error("nil echo from non-nil input")
1022 dbt.mustExec("CREATE TABLE test (dummmy1 int, value int, dummy2 int)")
1024 dbt.mustExec("INSERT INTO test VALUES (?, ?, ?)", 1, nil, 2)
1027 rows := dbt.mustQuery("SELECT * FROM test")
1031 dbt.Errorf("%v != nil", out)
1034 dbt.Error("no data")
1039 func TestUint64(t *testing.T) {
1047 shigh = int64(uhigh)
1050 runTests(t, dsn, func(dbt *DBTest) {
1051 stmt, err := dbt.db.Prepare(`SELECT ?, ?, ? ,?, ?, ?, ?, ?`)
1056 row := stmt.QueryRow(
1057 u0, uhigh, utop, uall,
1058 s0, shigh, stop, sall,
1061 var ua, ub, uc, ud uint64
1062 var sa, sb, sc, sd int64
1064 err = row.Scan(&ua, &ub, &uc, &ud, &sa, &sb, &sc, &sd)
1077 dbt.Fatal("unexpected result value")
1082 func TestLongData(t *testing.T) {
1083 runTests(t, dsn+"&maxAllowedPacket=0", func(dbt *DBTest) {
1084 var maxAllowedPacketSize int
1085 err := dbt.db.QueryRow("select @@max_allowed_packet").Scan(&maxAllowedPacketSize)
1089 maxAllowedPacketSize--
1091 // don't get too ambitious
1092 if maxAllowedPacketSize > 1<<25 {
1093 maxAllowedPacketSize = 1 << 25
1096 dbt.mustExec("CREATE TABLE test (value LONGBLOB)")
1098 in := strings.Repeat(`a`, maxAllowedPacketSize+1)
1103 const nonDataQueryLen = 28 // length query w/o value
1104 inS := in[:maxAllowedPacketSize-nonDataQueryLen]
1105 dbt.mustExec("INSERT INTO test VALUES('" + inS + "')")
1106 rows = dbt.mustQuery("SELECT value FROM test")
1110 dbt.Fatalf("LONGBLOB: length in: %d, length out: %d", len(inS), len(out))
1113 dbt.Error("LONGBLOB: unexpexted row")
1116 dbt.Fatalf("LONGBLOB: no data")
1120 dbt.mustExec("TRUNCATE TABLE test")
1123 dbt.mustExec("INSERT INTO test VALUES(?)", in)
1124 rows = dbt.mustQuery("SELECT value FROM test WHERE 1=?", 1)
1128 dbt.Fatalf("LONGBLOB: length in: %d, length out: %d", len(in), len(out))
1131 dbt.Error("LONGBLOB: unexpexted row")
1134 if err = rows.Err(); err != nil {
1135 dbt.Fatalf("LONGBLOB: no data (err: %s)", err.Error())
1137 dbt.Fatal("LONGBLOB: no data (err: <nil>)")
1143 func TestLoadData(t *testing.T) {
1144 runTests(t, dsn, func(dbt *DBTest) {
1145 verifyLoadDataResult := func() {
1146 rows, err := dbt.db.Query("SELECT * FROM test")
1148 dbt.Fatal(err.Error())
1152 values := [4]string{
1154 "a string containing a \t",
1155 "a string containing a \n",
1156 "a string containing both \t\n",
1164 err = rows.Scan(&id, &value)
1166 dbt.Fatal(err.Error())
1169 dbt.Fatalf("%d != %d", i, id)
1171 if values[i-1] != value {
1172 dbt.Fatalf("%q != %q", values[i-1], value)
1177 dbt.Fatal(err.Error())
1181 dbt.Fatalf("rows count mismatch. Got %d, want 4", i)
1185 dbt.db.Exec("DROP TABLE IF EXISTS test")
1186 dbt.mustExec("CREATE TABLE test (id INT NOT NULL PRIMARY KEY, value TEXT NOT NULL) CHARACTER SET utf8")
1189 file, err := ioutil.TempFile("", "gotest")
1190 defer os.Remove(file.Name())
1194 RegisterLocalFile(file.Name())
1196 // Try first with empty file
1197 dbt.mustExec(fmt.Sprintf("LOAD DATA LOCAL INFILE %q INTO TABLE test", file.Name()))
1199 err = dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&count)
1201 dbt.Fatal(err.Error())
1204 dbt.Fatalf("unexpected row count: got %d, want 0", count)
1207 // Then fille File with data and try to load it
1208 file.WriteString("1\ta string\n2\ta string containing a \\t\n3\ta string containing a \\n\n4\ta string containing both \\t\\n\n")
1210 dbt.mustExec(fmt.Sprintf("LOAD DATA LOCAL INFILE %q INTO TABLE test", file.Name()))
1211 verifyLoadDataResult()
1213 // Try with non-existing file
1214 _, err = dbt.db.Exec("LOAD DATA LOCAL INFILE 'doesnotexist' INTO TABLE test")
1216 dbt.Fatal("load non-existent file didn't fail")
1217 } else if err.Error() != "local file 'doesnotexist' is not registered" {
1218 dbt.Fatal(err.Error())
1222 dbt.mustExec("TRUNCATE TABLE test")
1225 RegisterReaderHandler("test", func() io.Reader {
1226 file, err = os.Open(file.Name())
1232 dbt.mustExec("LOAD DATA LOCAL INFILE 'Reader::test' INTO TABLE test")
1233 verifyLoadDataResult()
1235 _, err = dbt.db.Exec("LOAD DATA LOCAL INFILE 'Reader::doesnotexist' INTO TABLE test")
1237 dbt.Fatal("load non-existent Reader didn't fail")
1238 } else if err.Error() != "Reader 'doesnotexist' is not registered" {
1239 dbt.Fatal(err.Error())
1244 func TestFoundRows(t *testing.T) {
1245 runTests(t, dsn, func(dbt *DBTest) {
1246 dbt.mustExec("CREATE TABLE test (id INT NOT NULL ,data INT NOT NULL)")
1247 dbt.mustExec("INSERT INTO test (id, data) VALUES (0, 0),(0, 0),(1, 0),(1, 0),(1, 1)")
1249 res := dbt.mustExec("UPDATE test SET data = 1 WHERE id = 0")
1250 count, err := res.RowsAffected()
1252 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
1255 dbt.Fatalf("Expected 2 affected rows, got %d", count)
1257 res = dbt.mustExec("UPDATE test SET data = 1 WHERE id = 1")
1258 count, err = res.RowsAffected()
1260 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
1263 dbt.Fatalf("Expected 2 affected rows, got %d", count)
1266 runTests(t, dsn+"&clientFoundRows=true", func(dbt *DBTest) {
1267 dbt.mustExec("CREATE TABLE test (id INT NOT NULL ,data INT NOT NULL)")
1268 dbt.mustExec("INSERT INTO test (id, data) VALUES (0, 0),(0, 0),(1, 0),(1, 0),(1, 1)")
1270 res := dbt.mustExec("UPDATE test SET data = 1 WHERE id = 0")
1271 count, err := res.RowsAffected()
1273 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
1276 dbt.Fatalf("Expected 2 matched rows, got %d", count)
1278 res = dbt.mustExec("UPDATE test SET data = 1 WHERE id = 1")
1279 count, err = res.RowsAffected()
1281 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error())
1284 dbt.Fatalf("Expected 3 matched rows, got %d", count)
1289 func TestTLS(t *testing.T) {
1290 tlsTest := func(dbt *DBTest) {
1291 if err := dbt.db.Ping(); err != nil {
1292 if err == ErrNoTLS {
1293 dbt.Skip("server does not support TLS")
1295 dbt.Fatalf("error on Ping: %s", err.Error())
1299 rows := dbt.mustQuery("SHOW STATUS LIKE 'Ssl_cipher'")
1301 var variable, value *sql.RawBytes
1303 if err := rows.Scan(&variable, &value); err != nil {
1304 dbt.Fatal(err.Error())
1308 dbt.Fatal("no Cipher")
1313 runTests(t, dsn+"&tls=skip-verify", tlsTest)
1315 // Verify that registering / using a custom cfg works
1316 RegisterTLSConfig("custom-skip-verify", &tls.Config{
1317 InsecureSkipVerify: true,
1319 runTests(t, dsn+"&tls=custom-skip-verify", tlsTest)
1322 func TestReuseClosedConnection(t *testing.T) {
1323 // this test does not use sql.database, it uses the driver directly
1325 t.Skipf("MySQL server not running on %s", netAddr)
1328 md := &MySQLDriver{}
1329 conn, err := md.Open(dsn)
1331 t.Fatalf("error connecting: %s", err.Error())
1333 stmt, err := conn.Prepare("DO 1")
1335 t.Fatalf("error preparing statement: %s", err.Error())
1337 _, err = stmt.Exec(nil)
1339 t.Fatalf("error executing statement: %s", err.Error())
1343 t.Fatalf("error closing connection: %s", err.Error())
1347 if err := recover(); err != nil {
1348 t.Errorf("panic after reusing a closed connection: %v", err)
1351 _, err = stmt.Exec(nil)
1352 if err != nil && err != driver.ErrBadConn {
1353 t.Errorf("unexpected error '%s', expected '%s'",
1354 err.Error(), driver.ErrBadConn.Error())
1358 func TestCharset(t *testing.T) {
1360 t.Skipf("MySQL server not running on %s", netAddr)
1363 mustSetCharset := func(charsetParam, expected string) {
1364 runTests(t, dsn+"&"+charsetParam, func(dbt *DBTest) {
1365 rows := dbt.mustQuery("SELECT @@character_set_connection")
1369 dbt.Fatalf("error getting connection charset: %s", rows.Err())
1375 if got != expected {
1376 dbt.Fatalf("expected connection charset %s but got %s", expected, got)
1382 mustSetCharset("charset=ascii", "ascii")
1384 // when the first charset is invalid, use the second
1385 mustSetCharset("charset=none,utf8", "utf8")
1387 // when the first charset is valid, use it
1388 mustSetCharset("charset=ascii,utf8", "ascii")
1389 mustSetCharset("charset=utf8,ascii", "utf8")
1392 func TestFailingCharset(t *testing.T) {
1393 runTests(t, dsn+"&charset=none", func(dbt *DBTest) {
1394 // run query to really establish connection...
1395 _, err := dbt.db.Exec("SELECT 1")
1398 t.Fatalf("connection must not succeed without a valid charset")
1403 func TestCollation(t *testing.T) {
1405 t.Skipf("MySQL server not running on %s", netAddr)
1408 defaultCollation := "utf8_general_ci"
1409 testCollations := []string{
1411 defaultCollation, // driver default
1412 "latin1_general_ci",
1418 for _, collation := range testCollations {
1419 var expected, tdsn string
1420 if collation != "" {
1421 tdsn = dsn + "&collation=" + collation
1422 expected = collation
1425 expected = defaultCollation
1428 runTests(t, tdsn, func(dbt *DBTest) {
1430 if err := dbt.db.QueryRow("SELECT @@collation_connection").Scan(&got); err != nil {
1434 if got != expected {
1435 dbt.Fatalf("expected connection collation %s but got %s", expected, got)
1441 func TestColumnsWithAlias(t *testing.T) {
1442 runTests(t, dsn+"&columnsWithAlias=true", func(dbt *DBTest) {
1443 rows := dbt.mustQuery("SELECT 1 AS A")
1445 cols, _ := rows.Columns()
1447 t.Fatalf("expected 1 column, got %d", len(cols))
1450 t.Fatalf("expected column name \"A\", got \"%s\"", cols[0])
1454 rows = dbt.mustQuery("SELECT * FROM (SELECT 1 AS one) AS A")
1455 cols, _ = rows.Columns()
1457 t.Fatalf("expected 1 column, got %d", len(cols))
1459 if cols[0] != "A.one" {
1460 t.Fatalf("expected column name \"A.one\", got \"%s\"", cols[0])
1465 func TestRawBytesResultExceedsBuffer(t *testing.T) {
1466 runTests(t, dsn, func(dbt *DBTest) {
1467 // defaultBufSize from buffer.go
1468 expected := strings.Repeat("abc", defaultBufSize)
1470 rows := dbt.mustQuery("SELECT '" + expected + "'")
1473 dbt.Error("expected result, got none")
1475 var result sql.RawBytes
1477 if expected != string(result) {
1478 dbt.Error("result did not match expected value")
1483 func TestTimezoneConversion(t *testing.T) {
1484 zones := []string{"UTC", "US/Central", "US/Pacific", "Local"}
1486 // Regression test for timezone handling
1487 tzTest := func(dbt *DBTest) {
1489 dbt.mustExec("CREATE TABLE test (ts TIMESTAMP)")
1491 // Insert local time into database (should be converted)
1492 usCentral, _ := time.LoadLocation("US/Central")
1493 reftime := time.Date(2014, 05, 30, 18, 03, 17, 0, time.UTC).In(usCentral)
1494 dbt.mustExec("INSERT INTO test VALUE (?)", reftime)
1496 // Retrieve time from DB
1497 rows := dbt.mustQuery("SELECT ts FROM test")
1499 dbt.Fatal("did not get any rows out")
1502 var dbTime time.Time
1503 err := rows.Scan(&dbTime)
1505 dbt.Fatal("Err", err)
1508 // Check that dates match
1509 if reftime.Unix() != dbTime.Unix() {
1510 dbt.Errorf("times do not match.\n")
1511 dbt.Errorf(" Now(%v)=%v\n", usCentral, reftime)
1512 dbt.Errorf(" Now(UTC)=%v\n", dbTime)
1516 for _, tz := range zones {
1517 runTests(t, dsn+"&parseTime=true&loc="+url.QueryEscape(tz), tzTest)
1523 func TestRowsClose(t *testing.T) {
1524 runTests(t, dsn, func(dbt *DBTest) {
1525 rows, err := dbt.db.Query("SELECT 1")
1536 dbt.Fatal("unexpected row after rows.Close()")
1546 // dangling statements
1547 // http://code.google.com/p/go/issues/detail?id=3865
1548 func TestCloseStmtBeforeRows(t *testing.T) {
1549 runTests(t, dsn, func(dbt *DBTest) {
1550 stmt, err := dbt.db.Prepare("SELECT 1")
1555 rows, err := stmt.Query()
1568 dbt.Fatal("getting row failed")
1576 err = rows.Scan(&out)
1578 dbt.Fatalf("error on rows.Scan(): %s", err.Error())
1581 dbt.Errorf("true != %t", out)
1587 // It is valid to have multiple Rows for the same Stmt
1588 // http://code.google.com/p/go/issues/detail?id=3734
1589 func TestStmtMultiRows(t *testing.T) {
1590 runTests(t, dsn, func(dbt *DBTest) {
1591 stmt, err := dbt.db.Prepare("SELECT 1 UNION SELECT 0")
1596 rows1, err := stmt.Query()
1603 rows2, err := stmt.Query()
1614 dbt.Fatal("first rows1.Next failed")
1621 err = rows1.Scan(&out)
1623 dbt.Fatalf("error on rows.Scan(): %s", err.Error())
1626 dbt.Errorf("true != %t", out)
1631 dbt.Fatal("first rows2.Next failed")
1638 err = rows2.Scan(&out)
1640 dbt.Fatalf("error on rows.Scan(): %s", err.Error())
1643 dbt.Errorf("true != %t", out)
1649 dbt.Fatal("second rows1.Next failed")
1656 err = rows1.Scan(&out)
1658 dbt.Fatalf("error on rows.Scan(): %s", err.Error())
1661 dbt.Errorf("false != %t", out)
1665 dbt.Fatal("unexpected row on rows1")
1674 dbt.Fatal("second rows2.Next failed")
1681 err = rows2.Scan(&out)
1683 dbt.Fatalf("error on rows.Scan(): %s", err.Error())
1686 dbt.Errorf("false != %t", out)
1690 dbt.Fatal("unexpected row on rows2")
1700 // Regression test for
1701 // * more than 32 NULL parameters (issue 209)
1702 // * more parameters than fit into the buffer (issue 201)
1703 // * parameters * 64 > max_allowed_packet (issue 734)
1704 func TestPreparedManyCols(t *testing.T) {
1706 runTests(t, dsn, func(dbt *DBTest) {
1707 query := "SELECT ?" + strings.Repeat(",?", numParams-1)
1708 stmt, err := dbt.db.Prepare(query)
1714 // create more parameters than fit into the buffer
1715 // which will take nil-values
1716 params := make([]interface{}, numParams)
1717 rows, err := stmt.Query(params...)
1723 // Create 0byte string which we can't send via STMT_LONG_DATA.
1724 for i := 0; i < numParams; i++ {
1727 rows, err = stmt.Query(params...)
1735 func TestConcurrent(t *testing.T) {
1736 if enabled, _ := readBool(os.Getenv("MYSQL_TEST_CONCURRENT")); !enabled {
1737 t.Skip("MYSQL_TEST_CONCURRENT env var not set")
1740 runTests(t, dsn, func(dbt *DBTest) {
1742 err := dbt.db.QueryRow("SELECT @@max_connections").Scan(&max)
1744 dbt.Fatalf("%s", err.Error())
1746 dbt.Logf("testing up to %d concurrent connections \r\n", max)
1748 var remaining, succeeded int32 = int32(max), 0
1750 var wg sync.WaitGroup
1753 var fatalError string
1755 fatalf := func(s string, vals ...interface{}) {
1757 fatalError = fmt.Sprintf(s, vals...)
1761 for i := 0; i < max; i++ {
1765 tx, err := dbt.db.Begin()
1766 atomic.AddInt32(&remaining, -1)
1769 if err.Error() != "Error 1040: Too many connections" {
1770 fatalf("error on conn %d: %s", id, err.Error())
1775 // keep the connection busy until all connections are open
1777 if _, err = tx.Exec("DO 1"); err != nil {
1778 fatalf("error on conn %d: %s", id, err.Error())
1783 if err = tx.Commit(); err != nil {
1784 fatalf("error on conn %d: %s", id, err.Error())
1788 // everything went fine with this connection
1789 atomic.AddInt32(&succeeded, 1)
1793 // wait until all conections are open
1796 if fatalError != "" {
1797 dbt.Fatal(fatalError)
1800 dbt.Logf("reached %d concurrent connections\r\n", succeeded)
1804 // Tests custom dial functions
1805 func TestCustomDial(t *testing.T) {
1807 t.Skipf("MySQL server not running on %s", netAddr)
1810 // our custom dial function which justs wraps net.Dial here
1811 RegisterDial("mydial", func(addr string) (net.Conn, error) {
1812 return net.Dial(prot, addr)
1815 db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@mydial(%s)/%s?timeout=30s", user, pass, addr, dbname))
1817 t.Fatalf("error connecting: %s", err.Error())
1821 if _, err = db.Exec("DO 1"); err != nil {
1822 t.Fatalf("connection failed: %s", err.Error())
1826 func TestSQLInjection(t *testing.T) {
1827 createTest := func(arg string) func(dbt *DBTest) {
1828 return func(dbt *DBTest) {
1829 dbt.mustExec("CREATE TABLE test (v INTEGER)")
1830 dbt.mustExec("INSERT INTO test VALUES (?)", 1)
1833 // NULL can't be equal to anything, the idea here is to inject query so it returns row
1834 // This test verifies that escapeQuotes and escapeBackslash are working properly
1835 err := dbt.db.QueryRow("SELECT v FROM test WHERE NULL = ?", arg).Scan(&v)
1836 if err == sql.ErrNoRows {
1837 return // success, sql injection failed
1838 } else if err == nil {
1839 dbt.Errorf("sql injection successful with arg: %s", arg)
1841 dbt.Errorf("error running query with arg: %s; err: %s", arg, err.Error())
1848 dsn + "&sql_mode='NO_BACKSLASH_ESCAPES'",
1850 for _, testdsn := range dsns {
1851 runTests(t, testdsn, createTest("1 OR 1=1"))
1852 runTests(t, testdsn, createTest("' OR '1'='1"))
1856 // Test if inserted data is correctly retrieved after being escaped
1857 func TestInsertRetrieveEscapedData(t *testing.T) {
1858 testData := func(dbt *DBTest) {
1859 dbt.mustExec("CREATE TABLE test (v VARCHAR(255))")
1861 // All sequences that are escaped by escapeQuotes and escapeBackslash
1862 v := "foo \x00\n\r\x1a\"'\\"
1863 dbt.mustExec("INSERT INTO test VALUES (?)", v)
1866 err := dbt.db.QueryRow("SELECT v FROM test").Scan(&out)
1868 dbt.Fatalf("%s", err.Error())
1872 dbt.Errorf("%q != %q", out, v)
1878 dsn + "&sql_mode='NO_BACKSLASH_ESCAPES'",
1880 for _, testdsn := range dsns {
1881 runTests(t, testdsn, testData)
1885 func TestUnixSocketAuthFail(t *testing.T) {
1886 runTests(t, dsn, func(dbt *DBTest) {
1887 // Save the current logger so we can restore it.
1890 // Set a new logger so we can capture its output.
1891 buffer := bytes.NewBuffer(make([]byte, 0, 64))
1892 newLogger := log.New(buffer, "prefix: ", 0)
1893 SetLogger(newLogger)
1895 // Restore the logger.
1896 defer SetLogger(oldLogger)
1898 // Make a new DSN that uses the MySQL socket file and a bad password, which
1899 // we can make by simply appending any character to the real password.
1900 badPass := pass + "x"
1905 // Get socket file from MySQL.
1906 err := dbt.db.QueryRow("SELECT @@socket").Scan(&socket)
1908 t.Fatalf("error on SELECT @@socket: %s", err.Error())
1911 t.Logf("socket: %s", socket)
1912 badDSN := fmt.Sprintf("%s:%s@unix(%s)/%s?timeout=30s", user, badPass, socket, dbname)
1913 db, err := sql.Open("mysql", badDSN)
1915 t.Fatalf("error connecting: %s", err.Error())
1919 // Connect to MySQL for real. This will cause an auth failure.
1922 t.Error("expected Ping() to return an error")
1925 // The driver should not log anything.
1926 if actual := buffer.String(); actual != "" {
1927 t.Errorf("expected no output, got %q", actual)
1933 func TestInterruptBySignal(t *testing.T) {
1934 runTestsWithMultiStatement(t, dsn, func(dbt *DBTest) {
1936 DROP PROCEDURE IF EXISTS test_signal;
1937 CREATE PROCEDURE test_signal(ret INT)
1943 MESSAGE_TEXT = "an error",
1944 MYSQL_ERRNO = 45001;
1947 defer dbt.mustExec("DROP PROCEDURE test_signal")
1952 rows, err := dbt.db.Query("CALL test_signal(42)")
1954 dbt.Fatalf("error on text query: %s", err.Error())
1957 if err := rows.Scan(&val); err != nil {
1959 } else if val != 42 {
1960 dbt.Errorf("expected val to be 42")
1965 rows, err = dbt.db.Query("CALL test_signal(?)", 42)
1967 dbt.Fatalf("error on binary query: %s", err.Error())
1970 if err := rows.Scan(&val); err != nil {
1972 } else if val != 42 {
1973 dbt.Errorf("expected val to be 42")
1979 func TestColumnsReusesSlice(t *testing.T) {
1982 columns: []mysqlField{
1995 allocs := testing.AllocsPerRun(1, func() {
1996 cols := rows.Columns()
1999 t.Fatalf("expected 2 columns, got %d", len(cols))
2004 t.Fatalf("expected 0 allocations, got %d", int(allocs))
2007 if rows.rs.columnNames == nil {
2008 t.Fatalf("expected columnNames to be set, got nil")
2012 func TestRejectReadOnly(t *testing.T) {
2013 runTests(t, dsn, func(dbt *DBTest) {
2015 dbt.mustExec("CREATE TABLE test (value BOOL)")
2016 // Set the session to read-only. We didn't set the `rejectReadOnly`
2017 // option, so any writes after this should fail.
2018 _, err := dbt.db.Exec("SET SESSION TRANSACTION READ ONLY")
2019 // Error 1193: Unknown system variable 'TRANSACTION' => skip test,
2020 // MySQL server version is too old
2021 maybeSkip(t, err, 1193)
2022 if _, err := dbt.db.Exec("DROP TABLE test"); err == nil {
2023 t.Fatalf("writing to DB in read-only session without " +
2024 "rejectReadOnly did not error")
2026 // Set the session back to read-write so runTests() can properly clean
2027 // up the table `test`.
2028 dbt.mustExec("SET SESSION TRANSACTION READ WRITE")
2031 // Enable the `rejectReadOnly` option.
2032 runTests(t, dsn+"&rejectReadOnly=true", func(dbt *DBTest) {
2034 dbt.mustExec("CREATE TABLE test (value BOOL)")
2035 // Set the session to read only. Any writes after this should error on
2036 // a driver.ErrBadConn, and cause `database/sql` to initiate a new
2038 dbt.mustExec("SET SESSION TRANSACTION READ ONLY")
2039 // This would error, but `database/sql` should automatically retry on a
2040 // new connection which is not read-only, and eventually succeed.
2041 dbt.mustExec("DROP TABLE test")
2045 func TestPing(t *testing.T) {
2046 runTests(t, dsn, func(dbt *DBTest) {
2047 if err := dbt.db.Ping(); err != nil {
2048 dbt.fail("Ping", "Ping", err)
2054 func TestEmptyPassword(t *testing.T) {
2056 t.Skipf("MySQL server not running on %s", netAddr)
2059 dsn := fmt.Sprintf("%s:%s@%s/%s?timeout=30s", user, "", netAddr, dbname)
2060 db, err := sql.Open("mysql", dsn)
2068 t.Fatal(err.Error())
2072 t.Fatal("expected authentication error")
2074 if !strings.HasPrefix(err.Error(), "Error 1045") {
2075 t.Fatal(err.Error())
2080 // static interface implementation checks of mysqlConn
2082 _ driver.ConnBeginTx = &mysqlConn{}
2083 _ driver.ConnPrepareContext = &mysqlConn{}
2084 _ driver.ExecerContext = &mysqlConn{}
2085 _ driver.Pinger = &mysqlConn{}
2086 _ driver.QueryerContext = &mysqlConn{}
2089 // static interface implementation checks of mysqlStmt
2091 _ driver.StmtExecContext = &mysqlStmt{}
2092 _ driver.StmtQueryContext = &mysqlStmt{}
2095 // Ensure that all the driver interfaces are implemented
2097 // _ driver.RowsColumnTypeLength = &binaryRows{}
2098 // _ driver.RowsColumnTypeLength = &textRows{}
2099 _ driver.RowsColumnTypeDatabaseTypeName = &binaryRows{}
2100 _ driver.RowsColumnTypeDatabaseTypeName = &textRows{}
2101 _ driver.RowsColumnTypeNullable = &binaryRows{}
2102 _ driver.RowsColumnTypeNullable = &textRows{}
2103 _ driver.RowsColumnTypePrecisionScale = &binaryRows{}
2104 _ driver.RowsColumnTypePrecisionScale = &textRows{}
2105 _ driver.RowsColumnTypeScanType = &binaryRows{}
2106 _ driver.RowsColumnTypeScanType = &textRows{}
2107 _ driver.RowsNextResultSet = &binaryRows{}
2108 _ driver.RowsNextResultSet = &textRows{}
2111 func TestMultiResultSet(t *testing.T) {
2112 type result struct {
2117 // checkRows is a helper test function to validate rows containing 3 result
2118 // sets with specific values and columns. The basic query would look like this:
2120 // SELECT 1 AS col1, 2 AS col2 UNION SELECT 3, 4;
2121 // SELECT 0 UNION SELECT 1;
2122 // SELECT 1 AS col1, 2 AS col2, 3 AS col3 UNION SELECT 4, 5, 6;
2124 // to distinguish test cases the first string argument is put in front of
2125 // every error or fatal message.
2126 checkRows := func(desc string, rows *sql.Rows, dbt *DBTest) {
2127 expected := []result{
2129 values: [][]int{{1, 2}, {3, 4}},
2130 columns: []string{"col1", "col2"},
2133 values: [][]int{{1, 2, 3}, {4, 5, 6}},
2134 columns: []string{"col1", "col2", "col3"},
2141 if err := rows.Scan(&res[0], &res[1]); err != nil {
2144 res1.values = append(res1.values, res[:])
2147 cols, err := rows.Columns()
2149 dbt.Fatal(desc, err)
2153 if !reflect.DeepEqual(expected[0], res1) {
2154 dbt.Error(desc, "want =", expected[0], "got =", res1)
2157 if !rows.NextResultSet() {
2158 dbt.Fatal(desc, "expected next result set")
2161 // ignoring one result set
2163 if !rows.NextResultSet() {
2164 dbt.Fatal(desc, "expected next result set")
2168 cols, err = rows.Columns()
2170 dbt.Fatal(desc, err)
2176 if err := rows.Scan(&res[0], &res[1], &res[2]); err != nil {
2177 dbt.Fatal(desc, err)
2179 res2.values = append(res2.values, res[:])
2182 if !reflect.DeepEqual(expected[1], res2) {
2183 dbt.Error(desc, "want =", expected[1], "got =", res2)
2186 if rows.NextResultSet() {
2187 dbt.Error(desc, "unexpected next result set")
2190 if err := rows.Err(); err != nil {
2191 dbt.Error(desc, err)
2195 runTestsWithMultiStatement(t, dsn, func(dbt *DBTest) {
2196 rows := dbt.mustQuery(`DO 1;
2197 SELECT 1 AS col1, 2 AS col2 UNION SELECT 3, 4;
2199 SELECT 0 UNION SELECT 1;
2200 SELECT 1 AS col1, 2 AS col2, 3 AS col3 UNION SELECT 4, 5, 6;`)
2202 checkRows("query: ", rows, dbt)
2205 runTestsWithMultiStatement(t, dsn, func(dbt *DBTest) {
2206 queries := []string{
2208 DROP PROCEDURE IF EXISTS test_mrss;
2209 CREATE PROCEDURE test_mrss()
2212 SELECT 1 AS col1, 2 AS col2 UNION SELECT 3, 4;
2214 SELECT 0 UNION SELECT 1;
2215 SELECT 1 AS col1, 2 AS col2, 3 AS col3 UNION SELECT 4, 5, 6;
2219 DROP PROCEDURE IF EXISTS test_mrss;
2220 CREATE PROCEDURE test_mrss()
2222 SELECT 1 AS col1, 2 AS col2 UNION SELECT 3, 4;
2223 SELECT 0 UNION SELECT 1;
2224 SELECT 1 AS col1, 2 AS col2, 3 AS col3 UNION SELECT 4, 5, 6;
2229 defer dbt.mustExec("DROP PROCEDURE IF EXISTS test_mrss")
2231 for i, query := range queries {
2234 stmt, err := dbt.db.Prepare("CALL test_mrss()")
2236 dbt.Fatalf("%v (i=%d)", err, i)
2240 for j := 0; j < 2; j++ {
2241 rows, err := stmt.Query()
2243 dbt.Fatalf("%v (i=%d) (j=%d)", err, i, j)
2245 checkRows(fmt.Sprintf("prepared stmt query (i=%d) (j=%d): ", i, j), rows, dbt)
2251 func TestMultiResultSetNoSelect(t *testing.T) {
2252 runTestsWithMultiStatement(t, dsn, func(dbt *DBTest) {
2253 rows := dbt.mustQuery("DO 1; DO 2;")
2257 dbt.Error("unexpected row")
2260 if rows.NextResultSet() {
2261 dbt.Error("unexpected next result set")
2264 if err := rows.Err(); err != nil {
2265 dbt.Error("expected nil; got ", err)
2270 // tests if rows are set in a proper state if some results were ignored before
2271 // calling rows.NextResultSet.
2272 func TestSkipResults(t *testing.T) {
2273 runTests(t, dsn, func(dbt *DBTest) {
2274 rows := dbt.mustQuery("SELECT 1, 2")
2278 dbt.Error("expected row")
2281 if rows.NextResultSet() {
2282 dbt.Error("unexpected next result set")
2285 if err := rows.Err(); err != nil {
2286 dbt.Error("expected nil; got ", err)
2291 func TestPingContext(t *testing.T) {
2292 runTests(t, dsn, func(dbt *DBTest) {
2293 ctx, cancel := context.WithCancel(context.Background())
2295 if err := dbt.db.PingContext(ctx); err != context.Canceled {
2296 dbt.Errorf("expected context.Canceled, got %v", err)
2301 func TestContextCancelExec(t *testing.T) {
2302 runTests(t, dsn, func(dbt *DBTest) {
2303 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2304 ctx, cancel := context.WithCancel(context.Background())
2306 // Delay execution for just a bit until db.ExecContext has begun.
2307 defer time.AfterFunc(250*time.Millisecond, cancel).Stop()
2309 // This query will be canceled.
2310 startTime := time.Now()
2311 if _, err := dbt.db.ExecContext(ctx, "INSERT INTO test VALUES (SLEEP(1))"); err != context.Canceled {
2312 dbt.Errorf("expected context.Canceled, got %v", err)
2314 if d := time.Since(startTime); d > 500*time.Millisecond {
2315 dbt.Errorf("too long execution time: %s", d)
2318 // Wait for the INSERT query to be done.
2319 time.Sleep(time.Second)
2321 // Check how many times the query is executed.
2323 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2324 dbt.Fatalf("%s", err.Error())
2326 if v != 1 { // TODO: need to kill the query, and v should be 0.
2327 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2330 // Context is already canceled, so error should come before execution.
2331 if _, err := dbt.db.ExecContext(ctx, "INSERT INTO test VALUES (1)"); err == nil {
2332 dbt.Error("expected error")
2333 } else if err.Error() != "context canceled" {
2334 dbt.Fatalf("unexpected error: %s", err)
2337 // The second insert query will fail, so the table has no changes.
2338 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2339 dbt.Fatalf("%s", err.Error())
2342 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2347 func TestContextCancelQuery(t *testing.T) {
2348 runTests(t, dsn, func(dbt *DBTest) {
2349 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2350 ctx, cancel := context.WithCancel(context.Background())
2352 // Delay execution for just a bit until db.ExecContext has begun.
2353 defer time.AfterFunc(250*time.Millisecond, cancel).Stop()
2355 // This query will be canceled.
2356 startTime := time.Now()
2357 if _, err := dbt.db.QueryContext(ctx, "INSERT INTO test VALUES (SLEEP(1))"); err != context.Canceled {
2358 dbt.Errorf("expected context.Canceled, got %v", err)
2360 if d := time.Since(startTime); d > 500*time.Millisecond {
2361 dbt.Errorf("too long execution time: %s", d)
2364 // Wait for the INSERT query to be done.
2365 time.Sleep(time.Second)
2367 // Check how many times the query is executed.
2369 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2370 dbt.Fatalf("%s", err.Error())
2372 if v != 1 { // TODO: need to kill the query, and v should be 0.
2373 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2376 // Context is already canceled, so error should come before execution.
2377 if _, err := dbt.db.QueryContext(ctx, "INSERT INTO test VALUES (1)"); err != context.Canceled {
2378 dbt.Errorf("expected context.Canceled, got %v", err)
2381 // The second insert query will fail, so the table has no changes.
2382 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2383 dbt.Fatalf("%s", err.Error())
2386 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2391 func TestContextCancelQueryRow(t *testing.T) {
2392 runTests(t, dsn, func(dbt *DBTest) {
2393 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2394 dbt.mustExec("INSERT INTO test VALUES (1), (2), (3)")
2395 ctx, cancel := context.WithCancel(context.Background())
2397 rows, err := dbt.db.QueryContext(ctx, "SELECT v FROM test")
2399 dbt.Fatalf("%s", err.Error())
2402 // the first row will be succeed.
2405 dbt.Fatalf("unexpected end")
2407 if err := rows.Scan(&v); err != nil {
2408 dbt.Fatalf("%s", err.Error())
2412 // make sure the driver receives the cancel request.
2413 time.Sleep(100 * time.Millisecond)
2416 dbt.Errorf("expected end, but not")
2418 if err := rows.Err(); err != context.Canceled {
2419 dbt.Errorf("expected context.Canceled, got %v", err)
2424 func TestContextCancelPrepare(t *testing.T) {
2425 runTests(t, dsn, func(dbt *DBTest) {
2426 ctx, cancel := context.WithCancel(context.Background())
2428 if _, err := dbt.db.PrepareContext(ctx, "SELECT 1"); err != context.Canceled {
2429 dbt.Errorf("expected context.Canceled, got %v", err)
2434 func TestContextCancelStmtExec(t *testing.T) {
2435 runTests(t, dsn, func(dbt *DBTest) {
2436 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2437 ctx, cancel := context.WithCancel(context.Background())
2438 stmt, err := dbt.db.PrepareContext(ctx, "INSERT INTO test VALUES (SLEEP(1))")
2440 dbt.Fatalf("unexpected error: %v", err)
2443 // Delay execution for just a bit until db.ExecContext has begun.
2444 defer time.AfterFunc(250*time.Millisecond, cancel).Stop()
2446 // This query will be canceled.
2447 startTime := time.Now()
2448 if _, err := stmt.ExecContext(ctx); err != context.Canceled {
2449 dbt.Errorf("expected context.Canceled, got %v", err)
2451 if d := time.Since(startTime); d > 500*time.Millisecond {
2452 dbt.Errorf("too long execution time: %s", d)
2455 // Wait for the INSERT query to be done.
2456 time.Sleep(time.Second)
2458 // Check how many times the query is executed.
2460 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2461 dbt.Fatalf("%s", err.Error())
2463 if v != 1 { // TODO: need to kill the query, and v should be 0.
2464 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2469 func TestContextCancelStmtQuery(t *testing.T) {
2470 runTests(t, dsn, func(dbt *DBTest) {
2471 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2472 ctx, cancel := context.WithCancel(context.Background())
2473 stmt, err := dbt.db.PrepareContext(ctx, "INSERT INTO test VALUES (SLEEP(1))")
2475 dbt.Fatalf("unexpected error: %v", err)
2478 // Delay execution for just a bit until db.ExecContext has begun.
2479 defer time.AfterFunc(250*time.Millisecond, cancel).Stop()
2481 // This query will be canceled.
2482 startTime := time.Now()
2483 if _, err := stmt.QueryContext(ctx); err != context.Canceled {
2484 dbt.Errorf("expected context.Canceled, got %v", err)
2486 if d := time.Since(startTime); d > 500*time.Millisecond {
2487 dbt.Errorf("too long execution time: %s", d)
2490 // Wait for the INSERT query has done.
2491 time.Sleep(time.Second)
2493 // Check how many times the query is executed.
2495 if err := dbt.db.QueryRow("SELECT COUNT(*) FROM test").Scan(&v); err != nil {
2496 dbt.Fatalf("%s", err.Error())
2498 if v != 1 { // TODO: need to kill the query, and v should be 0.
2499 dbt.Skipf("[WARN] expected val to be 1, got %d", v)
2504 func TestContextCancelBegin(t *testing.T) {
2505 runTests(t, dsn, func(dbt *DBTest) {
2506 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2507 ctx, cancel := context.WithCancel(context.Background())
2508 tx, err := dbt.db.BeginTx(ctx, nil)
2513 // Delay execution for just a bit until db.ExecContext has begun.
2514 defer time.AfterFunc(100*time.Millisecond, cancel).Stop()
2516 // This query will be canceled.
2517 startTime := time.Now()
2518 if _, err := tx.ExecContext(ctx, "INSERT INTO test VALUES (SLEEP(1))"); err != context.Canceled {
2519 dbt.Errorf("expected context.Canceled, got %v", err)
2521 if d := time.Since(startTime); d > 500*time.Millisecond {
2522 dbt.Errorf("too long execution time: %s", d)
2525 // Transaction is canceled, so expect an error.
2526 switch err := tx.Commit(); err {
2528 // because the transaction has already been rollbacked.
2529 // the database/sql package watches ctx
2530 // and rollbacks when ctx is canceled.
2531 case context.Canceled:
2532 // the database/sql package rollbacks on another goroutine,
2533 // so the transaction may not be rollbacked depending on goroutine scheduling.
2535 dbt.Errorf("expected sql.ErrTxDone or context.Canceled, got %v", err)
2538 // Context is canceled, so cannot begin a transaction.
2539 if _, err := dbt.db.BeginTx(ctx, nil); err != context.Canceled {
2540 dbt.Errorf("expected context.Canceled, got %v", err)
2545 func TestContextBeginIsolationLevel(t *testing.T) {
2546 runTests(t, dsn, func(dbt *DBTest) {
2547 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2548 ctx, cancel := context.WithCancel(context.Background())
2551 tx1, err := dbt.db.BeginTx(ctx, &sql.TxOptions{
2552 Isolation: sql.LevelRepeatableRead,
2558 tx2, err := dbt.db.BeginTx(ctx, &sql.TxOptions{
2559 Isolation: sql.LevelReadCommitted,
2565 _, err = tx1.ExecContext(ctx, "INSERT INTO test VALUES (1)")
2571 row := tx2.QueryRowContext(ctx, "SELECT COUNT(*) FROM test")
2572 if err := row.Scan(&v); err != nil {
2575 // Because writer transaction wasn't commited yet, it should be available
2577 dbt.Errorf("expected val to be 0, got %d", v)
2585 row = tx2.QueryRowContext(ctx, "SELECT COUNT(*) FROM test")
2586 if err := row.Scan(&v); err != nil {
2589 // Data written by writer transaction is already commited, it should be selectable
2591 dbt.Errorf("expected val to be 1, got %d", v)
2597 func TestContextBeginReadOnly(t *testing.T) {
2598 runTests(t, dsn, func(dbt *DBTest) {
2599 dbt.mustExec("CREATE TABLE test (v INTEGER)")
2600 ctx, cancel := context.WithCancel(context.Background())
2603 tx, err := dbt.db.BeginTx(ctx, &sql.TxOptions{
2606 if _, ok := err.(*MySQLError); ok {
2607 dbt.Skip("It seems that your MySQL does not support READ ONLY transactions")
2609 } else if err != nil {
2613 // INSERT queries fail in a READ ONLY transaction.
2614 _, err = tx.ExecContext(ctx, "INSERT INTO test VALUES (1)")
2615 if _, ok := err.(*MySQLError); !ok {
2616 dbt.Errorf("expected MySQLError, got %v", err)
2619 // SELECT queries can be executed.
2621 row := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM test")
2622 if err := row.Scan(&v); err != nil {
2626 dbt.Errorf("expected val to be 0, got %d", v)
2629 if err := tx.Commit(); err != nil {
2635 func TestRowsColumnTypes(t *testing.T) {
2636 niNULL := sql.NullInt64{Int64: 0, Valid: false}
2637 ni0 := sql.NullInt64{Int64: 0, Valid: true}
2638 ni1 := sql.NullInt64{Int64: 1, Valid: true}
2639 ni42 := sql.NullInt64{Int64: 42, Valid: true}
2640 nfNULL := sql.NullFloat64{Float64: 0.0, Valid: false}
2641 nf0 := sql.NullFloat64{Float64: 0.0, Valid: true}
2642 nf1337 := sql.NullFloat64{Float64: 13.37, Valid: true}
2643 nt0 := NullTime{Time: time.Date(2006, 01, 02, 15, 04, 05, 0, time.UTC), Valid: true}
2644 nt1 := NullTime{Time: time.Date(2006, 01, 02, 15, 04, 05, 100000000, time.UTC), Valid: true}
2645 nt2 := NullTime{Time: time.Date(2006, 01, 02, 15, 04, 05, 110000000, time.UTC), Valid: true}
2646 nt6 := NullTime{Time: time.Date(2006, 01, 02, 15, 04, 05, 111111000, time.UTC), Valid: true}
2647 nd1 := NullTime{Time: time.Date(2006, 01, 02, 0, 0, 0, 0, time.UTC), Valid: true}
2648 nd2 := NullTime{Time: time.Date(2006, 03, 04, 0, 0, 0, 0, time.UTC), Valid: true}
2649 ndNULL := NullTime{Time: time.Time{}, Valid: false}
2650 rbNULL := sql.RawBytes(nil)
2651 rb0 := sql.RawBytes("0")
2652 rb42 := sql.RawBytes("42")
2653 rbTest := sql.RawBytes("Test")
2654 rb0pad4 := sql.RawBytes("0\x00\x00\x00") // BINARY right-pads values with 0x00
2655 rbx0 := sql.RawBytes("\x00")
2656 rbx42 := sql.RawBytes("\x42")
2658 var columns = []struct {
2660 fieldType string // type used when creating table schema
2661 databaseTypeName string // actual type used by MySQL
2662 scanType reflect.Type
2664 precision int64 // 0 if not ok
2667 valuesOut [3]interface{}
2669 {"bit8null", "BIT(8)", "BIT", scanTypeRawBytes, true, 0, 0, [3]string{"0x0", "NULL", "0x42"}, [3]interface{}{rbx0, rbNULL, rbx42}},
2670 {"boolnull", "BOOL", "TINYINT", scanTypeNullInt, true, 0, 0, [3]string{"NULL", "true", "0"}, [3]interface{}{niNULL, ni1, ni0}},
2671 {"bool", "BOOL NOT NULL", "TINYINT", scanTypeInt8, false, 0, 0, [3]string{"1", "0", "FALSE"}, [3]interface{}{int8(1), int8(0), int8(0)}},
2672 {"intnull", "INTEGER", "INT", scanTypeNullInt, true, 0, 0, [3]string{"0", "NULL", "42"}, [3]interface{}{ni0, niNULL, ni42}},
2673 {"smallint", "SMALLINT NOT NULL", "SMALLINT", scanTypeInt16, false, 0, 0, [3]string{"0", "-32768", "32767"}, [3]interface{}{int16(0), int16(-32768), int16(32767)}},
2674 {"smallintnull", "SMALLINT", "SMALLINT", scanTypeNullInt, true, 0, 0, [3]string{"0", "NULL", "42"}, [3]interface{}{ni0, niNULL, ni42}},
2675 {"int3null", "INT(3)", "INT", scanTypeNullInt, true, 0, 0, [3]string{"0", "NULL", "42"}, [3]interface{}{ni0, niNULL, ni42}},
2676 {"int7", "INT(7) NOT NULL", "INT", scanTypeInt32, false, 0, 0, [3]string{"0", "-1337", "42"}, [3]interface{}{int32(0), int32(-1337), int32(42)}},
2677 {"mediumintnull", "MEDIUMINT", "MEDIUMINT", scanTypeNullInt, true, 0, 0, [3]string{"0", "42", "NULL"}, [3]interface{}{ni0, ni42, niNULL}},
2678 {"bigint", "BIGINT NOT NULL", "BIGINT", scanTypeInt64, false, 0, 0, [3]string{"0", "65535", "-42"}, [3]interface{}{int64(0), int64(65535), int64(-42)}},
2679 {"bigintnull", "BIGINT", "BIGINT", scanTypeNullInt, true, 0, 0, [3]string{"NULL", "1", "42"}, [3]interface{}{niNULL, ni1, ni42}},
2680 {"tinyuint", "TINYINT UNSIGNED NOT NULL", "TINYINT", scanTypeUint8, false, 0, 0, [3]string{"0", "255", "42"}, [3]interface{}{uint8(0), uint8(255), uint8(42)}},
2681 {"smalluint", "SMALLINT UNSIGNED NOT NULL", "SMALLINT", scanTypeUint16, false, 0, 0, [3]string{"0", "65535", "42"}, [3]interface{}{uint16(0), uint16(65535), uint16(42)}},
2682 {"biguint", "BIGINT UNSIGNED NOT NULL", "BIGINT", scanTypeUint64, false, 0, 0, [3]string{"0", "65535", "42"}, [3]interface{}{uint64(0), uint64(65535), uint64(42)}},
2683 {"uint13", "INT(13) UNSIGNED NOT NULL", "INT", scanTypeUint32, false, 0, 0, [3]string{"0", "1337", "42"}, [3]interface{}{uint32(0), uint32(1337), uint32(42)}},
2684 {"float", "FLOAT NOT NULL", "FLOAT", scanTypeFloat32, false, math.MaxInt64, math.MaxInt64, [3]string{"0", "42", "13.37"}, [3]interface{}{float32(0), float32(42), float32(13.37)}},
2685 {"floatnull", "FLOAT", "FLOAT", scanTypeNullFloat, true, math.MaxInt64, math.MaxInt64, [3]string{"0", "NULL", "13.37"}, [3]interface{}{nf0, nfNULL, nf1337}},
2686 {"float74null", "FLOAT(7,4)", "FLOAT", scanTypeNullFloat, true, math.MaxInt64, 4, [3]string{"0", "NULL", "13.37"}, [3]interface{}{nf0, nfNULL, nf1337}},
2687 {"double", "DOUBLE NOT NULL", "DOUBLE", scanTypeFloat64, false, math.MaxInt64, math.MaxInt64, [3]string{"0", "42", "13.37"}, [3]interface{}{float64(0), float64(42), float64(13.37)}},
2688 {"doublenull", "DOUBLE", "DOUBLE", scanTypeNullFloat, true, math.MaxInt64, math.MaxInt64, [3]string{"0", "NULL", "13.37"}, [3]interface{}{nf0, nfNULL, nf1337}},
2689 {"decimal1", "DECIMAL(10,6) NOT NULL", "DECIMAL", scanTypeRawBytes, false, 10, 6, [3]string{"0", "13.37", "1234.123456"}, [3]interface{}{sql.RawBytes("0.000000"), sql.RawBytes("13.370000"), sql.RawBytes("1234.123456")}},
2690 {"decimal1null", "DECIMAL(10,6)", "DECIMAL", scanTypeRawBytes, true, 10, 6, [3]string{"0", "NULL", "1234.123456"}, [3]interface{}{sql.RawBytes("0.000000"), rbNULL, sql.RawBytes("1234.123456")}},
2691 {"decimal2", "DECIMAL(8,4) NOT NULL", "DECIMAL", scanTypeRawBytes, false, 8, 4, [3]string{"0", "13.37", "1234.123456"}, [3]interface{}{sql.RawBytes("0.0000"), sql.RawBytes("13.3700"), sql.RawBytes("1234.1235")}},
2692 {"decimal2null", "DECIMAL(8,4)", "DECIMAL", scanTypeRawBytes, true, 8, 4, [3]string{"0", "NULL", "1234.123456"}, [3]interface{}{sql.RawBytes("0.0000"), rbNULL, sql.RawBytes("1234.1235")}},
2693 {"decimal3", "DECIMAL(5,0) NOT NULL", "DECIMAL", scanTypeRawBytes, false, 5, 0, [3]string{"0", "13.37", "-12345.123456"}, [3]interface{}{rb0, sql.RawBytes("13"), sql.RawBytes("-12345")}},
2694 {"decimal3null", "DECIMAL(5,0)", "DECIMAL", scanTypeRawBytes, true, 5, 0, [3]string{"0", "NULL", "-12345.123456"}, [3]interface{}{rb0, rbNULL, sql.RawBytes("-12345")}},
2695 {"char25null", "CHAR(25)", "CHAR", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0, rbNULL, rbTest}},
2696 {"varchar42", "VARCHAR(42) NOT NULL", "VARCHAR", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2697 {"binary4null", "BINARY(4)", "BINARY", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0pad4, rbNULL, rbTest}},
2698 {"varbinary42", "VARBINARY(42) NOT NULL", "VARBINARY", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2699 {"tinyblobnull", "TINYBLOB", "BLOB", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0, rbNULL, rbTest}},
2700 {"tinytextnull", "TINYTEXT", "TEXT", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0, rbNULL, rbTest}},
2701 {"blobnull", "BLOB", "BLOB", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0, rbNULL, rbTest}},
2702 {"textnull", "TEXT", "TEXT", scanTypeRawBytes, true, 0, 0, [3]string{"0", "NULL", "'Test'"}, [3]interface{}{rb0, rbNULL, rbTest}},
2703 {"mediumblob", "MEDIUMBLOB NOT NULL", "BLOB", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2704 {"mediumtext", "MEDIUMTEXT NOT NULL", "TEXT", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2705 {"longblob", "LONGBLOB NOT NULL", "BLOB", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2706 {"longtext", "LONGTEXT NOT NULL", "TEXT", scanTypeRawBytes, false, 0, 0, [3]string{"0", "'Test'", "42"}, [3]interface{}{rb0, rbTest, rb42}},
2707 {"datetime", "DATETIME", "DATETIME", scanTypeNullTime, true, 0, 0, [3]string{"'2006-01-02 15:04:05'", "'2006-01-02 15:04:05.1'", "'2006-01-02 15:04:05.111111'"}, [3]interface{}{nt0, nt0, nt0}},
2708 {"datetime2", "DATETIME(2)", "DATETIME", scanTypeNullTime, true, 2, 2, [3]string{"'2006-01-02 15:04:05'", "'2006-01-02 15:04:05.1'", "'2006-01-02 15:04:05.111111'"}, [3]interface{}{nt0, nt1, nt2}},
2709 {"datetime6", "DATETIME(6)", "DATETIME", scanTypeNullTime, true, 6, 6, [3]string{"'2006-01-02 15:04:05'", "'2006-01-02 15:04:05.1'", "'2006-01-02 15:04:05.111111'"}, [3]interface{}{nt0, nt1, nt6}},
2710 {"date", "DATE", "DATE", scanTypeNullTime, true, 0, 0, [3]string{"'2006-01-02'", "NULL", "'2006-03-04'"}, [3]interface{}{nd1, ndNULL, nd2}},
2711 {"year", "YEAR NOT NULL", "YEAR", scanTypeUint16, false, 0, 0, [3]string{"2006", "2000", "1994"}, [3]interface{}{uint16(2006), uint16(2000), uint16(1994)}},
2718 for _, column := range columns {
2719 schema += fmt.Sprintf("`%s` %s, ", column.name, column.fieldType)
2720 values1 += column.valuesIn[0] + ", "
2721 values2 += column.valuesIn[1] + ", "
2722 values3 += column.valuesIn[2] + ", "
2724 schema = schema[:len(schema)-2]
2725 values1 = values1[:len(values1)-2]
2726 values2 = values2[:len(values2)-2]
2727 values3 = values3[:len(values3)-2]
2730 dsn + "&parseTime=true",
2731 dsn + "&parseTime=false",
2733 for _, testdsn := range dsns {
2734 runTests(t, testdsn, func(dbt *DBTest) {
2735 dbt.mustExec("CREATE TABLE test (" + schema + ")")
2736 dbt.mustExec("INSERT INTO test VALUES (" + values1 + "), (" + values2 + "), (" + values3 + ")")
2738 rows, err := dbt.db.Query("SELECT * FROM test")
2740 t.Fatalf("Query: %v", err)
2743 tt, err := rows.ColumnTypes()
2745 t.Fatalf("ColumnTypes: %v", err)
2748 if len(tt) != len(columns) {
2749 t.Fatalf("unexpected number of columns: expected %d, got %d", len(columns), len(tt))
2752 types := make([]reflect.Type, len(tt))
2753 for i, tp := range tt {
2754 column := columns[i]
2758 if name != column.name {
2759 t.Errorf("column name mismatch %s != %s", name, column.name)
2764 databaseTypeName := tp.DatabaseTypeName()
2765 if databaseTypeName != column.databaseTypeName {
2766 t.Errorf("databasetypename name mismatch for column %q: %s != %s", name, databaseTypeName, column.databaseTypeName)
2771 scanType := tp.ScanType()
2772 if scanType != column.scanType {
2773 if scanType == nil {
2774 t.Errorf("scantype is null for column %q", name)
2776 t.Errorf("scantype mismatch for column %q: %s != %s", name, scanType.Name(), column.scanType.Name())
2783 nullable, ok := tp.Nullable()
2785 t.Errorf("nullable not ok %q", name)
2788 if nullable != column.nullable {
2789 t.Errorf("nullable mismatch for column %q: %t != %t", name, nullable, column.nullable)
2793 // length, ok := tp.Length()
2794 // if length != column.length {
2796 // t.Errorf("length not ok for column %q", name)
2798 // t.Errorf("length mismatch for column %q: %d != %d", name, length, column.length)
2803 // Precision and Scale
2804 precision, scale, ok := tp.DecimalSize()
2805 if precision != column.precision {
2807 t.Errorf("precision not ok for column %q", name)
2809 t.Errorf("precision mismatch for column %q: %d != %d", name, precision, column.precision)
2813 if scale != column.scale {
2815 t.Errorf("scale not ok for column %q", name)
2817 t.Errorf("scale mismatch for column %q: %d != %d", name, scale, column.scale)
2823 values := make([]interface{}, len(tt))
2824 for i := range values {
2825 values[i] = reflect.New(types[i]).Interface()
2829 err = rows.Scan(values...)
2831 t.Fatalf("failed to scan values in %v", err)
2833 for j := range values {
2834 value := reflect.ValueOf(values[j]).Elem().Interface()
2835 if !reflect.DeepEqual(value, columns[j].valuesOut[i]) {
2836 if columns[j].scanType == scanTypeRawBytes {
2837 t.Errorf("row %d, column %d: %v != %v", i, j, string(value.(sql.RawBytes)), string(columns[j].valuesOut[i].(sql.RawBytes)))
2839 t.Errorf("row %d, column %d: %v != %v", i, j, value, columns[j].valuesOut[i])
2846 t.Errorf("expected 3 rows, got %d", i)
2849 if err := rows.Close(); err != nil {
2850 t.Errorf("error closing rows: %s", err)
2856 func TestValuerWithValueReceiverGivenNilValue(t *testing.T) {
2857 runTests(t, dsn, func(dbt *DBTest) {
2858 dbt.mustExec("CREATE TABLE test (value VARCHAR(255))")
2859 dbt.db.Exec("INSERT INTO test VALUES (?)", (*testValuer)(nil))
2860 // This test will panic on the INSERT if ConvertValue() does not check for typed nil before calling Value()