github.com/cellofellow/gopkg@v0.0.0-20140722061823-eec0544a62ad/database/mysql/driver_test.go (about) 1 // Go MySQL Driver - A MySQL-Driver for Go's database/sql package 2 // 3 // Copyright 2013 The Go-MySQL-Driver Authors. All rights reserved. 4 // 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/. 8 9 package mysql 10 11 import ( 12 "crypto/tls" 13 "database/sql" 14 "database/sql/driver" 15 "fmt" 16 "io" 17 "io/ioutil" 18 "net" 19 "net/url" 20 "os" 21 "strings" 22 "sync" 23 "sync/atomic" 24 "testing" 25 "time" 26 ) 27 28 var ( 29 user string 30 pass string 31 prot string 32 addr string 33 dbname string 34 dsn string 35 netAddr string 36 available bool 37 ) 38 39 var ( 40 tDate = time.Date(2012, 6, 14, 0, 0, 0, 0, time.UTC) 41 sDate = "2012-06-14" 42 tDateTime = time.Date(2011, 11, 20, 21, 27, 37, 0, time.UTC) 43 sDateTime = "2011-11-20 21:27:37" 44 tDate0 = time.Time{} 45 sDate0 = "0000-00-00" 46 sDateTime0 = "0000-00-00 00:00:00" 47 ) 48 49 // See https://github.com/go-sql-driver/mysql/wiki/Testing 50 func init() { 51 // get environment variables 52 env := func(key, defaultValue string) string { 53 if value := os.Getenv(key); value != "" { 54 return value 55 } 56 return defaultValue 57 } 58 user = env("MYSQL_TEST_USER", "root") 59 pass = env("MYSQL_TEST_PASS", "") 60 prot = env("MYSQL_TEST_PROT", "tcp") 61 addr = env("MYSQL_TEST_ADDR", "localhost:3306") 62 dbname = env("MYSQL_TEST_DBNAME", "gotest") 63 netAddr = fmt.Sprintf("%s(%s)", prot, addr) 64 dsn = fmt.Sprintf("%s:%s@%s/%s?timeout=30s&strict=true", user, pass, netAddr, dbname) 65 c, err := net.Dial(prot, addr) 66 if err == nil { 67 available = true 68 c.Close() 69 } 70 } 71 72 type DBTest struct { 73 *testing.T 74 db *sql.DB 75 } 76 77 func runTests(t *testing.T, dsn string, tests ...func(dbt *DBTest)) { 78 if !available { 79 t.Skipf("MySQL-Server not running on %s", netAddr) 80 } 81 82 db, err := sql.Open("mysql", dsn) 83 if err != nil { 84 t.Fatalf("Error connecting: %s", err.Error()) 85 } 86 defer db.Close() 87 88 db.Exec("DROP TABLE IF EXISTS test") 89 90 dbt := &DBTest{t, db} 91 for _, test := range tests { 92 test(dbt) 93 dbt.db.Exec("DROP TABLE IF EXISTS test") 94 } 95 } 96 97 func (dbt *DBTest) fail(method, query string, err error) { 98 if len(query) > 300 { 99 query = "[query too large to print]" 100 } 101 dbt.Fatalf("Error on %s %s: %s", method, query, err.Error()) 102 } 103 104 func (dbt *DBTest) mustExec(query string, args ...interface{}) (res sql.Result) { 105 res, err := dbt.db.Exec(query, args...) 106 if err != nil { 107 dbt.fail("Exec", query, err) 108 } 109 return res 110 } 111 112 func (dbt *DBTest) mustQuery(query string, args ...interface{}) (rows *sql.Rows) { 113 rows, err := dbt.db.Query(query, args...) 114 if err != nil { 115 dbt.fail("Query", query, err) 116 } 117 return rows 118 } 119 120 func TestCRUD(t *testing.T) { 121 runTests(t, dsn, func(dbt *DBTest) { 122 // Create Table 123 dbt.mustExec("CREATE TABLE test (value BOOL)") 124 125 // Test for unexpected data 126 var out bool 127 rows := dbt.mustQuery("SELECT * FROM test") 128 if rows.Next() { 129 dbt.Error("unexpected data in empty table") 130 } 131 132 // Create Data 133 res := dbt.mustExec("INSERT INTO test VALUES (1)") 134 count, err := res.RowsAffected() 135 if err != nil { 136 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 137 } 138 if count != 1 { 139 dbt.Fatalf("Expected 1 affected row, got %d", count) 140 } 141 142 id, err := res.LastInsertId() 143 if err != nil { 144 dbt.Fatalf("res.LastInsertId() returned error: %s", err.Error()) 145 } 146 if id != 0 { 147 dbt.Fatalf("Expected InsertID 0, got %d", id) 148 } 149 150 // Read 151 rows = dbt.mustQuery("SELECT value FROM test") 152 if rows.Next() { 153 rows.Scan(&out) 154 if true != out { 155 dbt.Errorf("true != %t", out) 156 } 157 158 if rows.Next() { 159 dbt.Error("unexpected data") 160 } 161 } else { 162 dbt.Error("no data") 163 } 164 165 // Update 166 res = dbt.mustExec("UPDATE test SET value = ? WHERE value = ?", false, true) 167 count, err = res.RowsAffected() 168 if err != nil { 169 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 170 } 171 if count != 1 { 172 dbt.Fatalf("Expected 1 affected row, got %d", count) 173 } 174 175 // Check Update 176 rows = dbt.mustQuery("SELECT value FROM test") 177 if rows.Next() { 178 rows.Scan(&out) 179 if false != out { 180 dbt.Errorf("false != %t", out) 181 } 182 183 if rows.Next() { 184 dbt.Error("unexpected data") 185 } 186 } else { 187 dbt.Error("no data") 188 } 189 190 // Delete 191 res = dbt.mustExec("DELETE FROM test WHERE value = ?", false) 192 count, err = res.RowsAffected() 193 if err != nil { 194 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 195 } 196 if count != 1 { 197 dbt.Fatalf("Expected 1 affected row, got %d", count) 198 } 199 200 // Check for unexpected rows 201 res = dbt.mustExec("DELETE FROM test") 202 count, err = res.RowsAffected() 203 if err != nil { 204 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 205 } 206 if count != 0 { 207 dbt.Fatalf("Expected 0 affected row, got %d", count) 208 } 209 }) 210 } 211 212 func TestInt(t *testing.T) { 213 runTests(t, dsn, func(dbt *DBTest) { 214 types := [5]string{"TINYINT", "SMALLINT", "MEDIUMINT", "INT", "BIGINT"} 215 in := int64(42) 216 var out int64 217 var rows *sql.Rows 218 219 // SIGNED 220 for _, v := range types { 221 dbt.mustExec("CREATE TABLE test (value " + v + ")") 222 223 dbt.mustExec("INSERT INTO test VALUES (?)", in) 224 225 rows = dbt.mustQuery("SELECT value FROM test") 226 if rows.Next() { 227 rows.Scan(&out) 228 if in != out { 229 dbt.Errorf("%s: %d != %d", v, in, out) 230 } 231 } else { 232 dbt.Errorf("%s: no data", v) 233 } 234 235 dbt.mustExec("DROP TABLE IF EXISTS test") 236 } 237 238 // UNSIGNED ZEROFILL 239 for _, v := range types { 240 dbt.mustExec("CREATE TABLE test (value " + v + " ZEROFILL)") 241 242 dbt.mustExec("INSERT INTO test VALUES (?)", in) 243 244 rows = dbt.mustQuery("SELECT value FROM test") 245 if rows.Next() { 246 rows.Scan(&out) 247 if in != out { 248 dbt.Errorf("%s ZEROFILL: %d != %d", v, in, out) 249 } 250 } else { 251 dbt.Errorf("%s ZEROFILL: no data", v) 252 } 253 254 dbt.mustExec("DROP TABLE IF EXISTS test") 255 } 256 }) 257 } 258 259 func TestFloat(t *testing.T) { 260 runTests(t, dsn, func(dbt *DBTest) { 261 types := [2]string{"FLOAT", "DOUBLE"} 262 in := float32(42.23) 263 var out float32 264 var rows *sql.Rows 265 for _, v := range types { 266 dbt.mustExec("CREATE TABLE test (value " + v + ")") 267 dbt.mustExec("INSERT INTO test VALUES (?)", in) 268 rows = dbt.mustQuery("SELECT value FROM test") 269 if rows.Next() { 270 rows.Scan(&out) 271 if in != out { 272 dbt.Errorf("%s: %g != %g", v, in, out) 273 } 274 } else { 275 dbt.Errorf("%s: no data", v) 276 } 277 dbt.mustExec("DROP TABLE IF EXISTS test") 278 } 279 }) 280 } 281 282 func TestString(t *testing.T) { 283 runTests(t, dsn, func(dbt *DBTest) { 284 types := [6]string{"CHAR(255)", "VARCHAR(255)", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT"} 285 in := "κόσμε üöäßñóùéàâÿœ'îë Árvíztűrő いろはにほへとちりぬるを イロハニホヘト דג סקרן чащах น่าฟังเอย" 286 var out string 287 var rows *sql.Rows 288 289 for _, v := range types { 290 dbt.mustExec("CREATE TABLE test (value " + v + ") CHARACTER SET utf8") 291 292 dbt.mustExec("INSERT INTO test VALUES (?)", in) 293 294 rows = dbt.mustQuery("SELECT value FROM test") 295 if rows.Next() { 296 rows.Scan(&out) 297 if in != out { 298 dbt.Errorf("%s: %s != %s", v, in, out) 299 } 300 } else { 301 dbt.Errorf("%s: no data", v) 302 } 303 304 dbt.mustExec("DROP TABLE IF EXISTS test") 305 } 306 307 // BLOB 308 dbt.mustExec("CREATE TABLE test (id int, value BLOB) CHARACTER SET utf8") 309 310 id := 2 311 in = "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, " + 312 "sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, " + 313 "sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. " + 314 "Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. " + 315 "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, " + 316 "sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, " + 317 "sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. " + 318 "Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet." 319 dbt.mustExec("INSERT INTO test VALUES (?, ?)", id, in) 320 321 err := dbt.db.QueryRow("SELECT value FROM test WHERE id = ?", id).Scan(&out) 322 if err != nil { 323 dbt.Fatalf("Error on BLOB-Query: %s", err.Error()) 324 } else if out != in { 325 dbt.Errorf("BLOB: %s != %s", in, out) 326 } 327 }) 328 } 329 330 func TestDateTime(t *testing.T) { 331 type testmode struct { 332 selectSuffix string 333 args []interface{} 334 } 335 type timetest struct { 336 in interface{} 337 sOut string 338 tOut time.Time 339 tIsZero bool 340 } 341 type tester func(dbt *DBTest, rows *sql.Rows, 342 test *timetest, sqltype, resulttype, mode string) 343 type setup struct { 344 vartype string 345 dsnSuffix string 346 test tester 347 } 348 var ( 349 modes = map[string]*testmode{ 350 "text": &testmode{}, 351 "binary": &testmode{" WHERE 1 = ?", []interface{}{1}}, 352 } 353 timetests = map[string][]*timetest{ 354 "DATE": { 355 {sDate, sDate, tDate, false}, 356 {sDate0, sDate0, tDate0, true}, 357 {tDate, sDate, tDate, false}, 358 {tDate0, sDate0, tDate0, true}, 359 }, 360 "DATETIME": { 361 {sDateTime, sDateTime, tDateTime, false}, 362 {sDateTime0, sDateTime0, tDate0, true}, 363 {tDateTime, sDateTime, tDateTime, false}, 364 {tDate0, sDateTime0, tDate0, true}, 365 }, 366 } 367 setups = []*setup{ 368 {"string", "&parseTime=false", func( 369 dbt *DBTest, rows *sql.Rows, test *timetest, sqltype, resulttype, mode string) { 370 var sOut string 371 if err := rows.Scan(&sOut); err != nil { 372 dbt.Errorf("%s (%s %s): %s", sqltype, resulttype, mode, err.Error()) 373 } else if test.sOut != sOut { 374 dbt.Errorf("%s (%s %s): %s != %s", sqltype, resulttype, mode, test.sOut, sOut) 375 } 376 }}, 377 {"time.Time", "&parseTime=true", func( 378 dbt *DBTest, rows *sql.Rows, test *timetest, sqltype, resulttype, mode string) { 379 var tOut time.Time 380 if err := rows.Scan(&tOut); err != nil { 381 dbt.Errorf("%s (%s %s): %s", sqltype, resulttype, mode, err.Error()) 382 } else if test.tOut != tOut || test.tIsZero != tOut.IsZero() { 383 dbt.Errorf("%s (%s %s): %s [%t] != %s [%t]", sqltype, resulttype, mode, test.tOut, test.tIsZero, tOut, tOut.IsZero()) 384 } 385 }}, 386 } 387 ) 388 389 var s *setup 390 testTime := func(dbt *DBTest) { 391 var rows *sql.Rows 392 for sqltype, tests := range timetests { 393 dbt.mustExec("CREATE TABLE test (value " + sqltype + ")") 394 for _, test := range tests { 395 for mode, q := range modes { 396 dbt.mustExec("TRUNCATE test") 397 dbt.mustExec("INSERT INTO test VALUES (?)", test.in) 398 rows = dbt.mustQuery("SELECT value FROM test"+q.selectSuffix, q.args...) 399 if rows.Next() { 400 s.test(dbt, rows, test, sqltype, s.vartype, mode) 401 } else { 402 if err := rows.Err(); err != nil { 403 dbt.Errorf("%s (%s %s): %s", 404 sqltype, s.vartype, mode, err.Error()) 405 } else { 406 dbt.Errorf("%s (%s %s): no data", 407 sqltype, s.vartype, mode) 408 } 409 } 410 } 411 } 412 dbt.mustExec("DROP TABLE IF EXISTS test") 413 } 414 } 415 416 timeDsn := dsn + "&sql_mode=ALLOW_INVALID_DATES" 417 for _, v := range setups { 418 s = v 419 runTests(t, timeDsn+s.dsnSuffix, testTime) 420 } 421 } 422 423 func TestNULL(t *testing.T) { 424 runTests(t, dsn, func(dbt *DBTest) { 425 nullStmt, err := dbt.db.Prepare("SELECT NULL") 426 if err != nil { 427 dbt.Fatal(err) 428 } 429 defer nullStmt.Close() 430 431 nonNullStmt, err := dbt.db.Prepare("SELECT 1") 432 if err != nil { 433 dbt.Fatal(err) 434 } 435 defer nonNullStmt.Close() 436 437 // NullBool 438 var nb sql.NullBool 439 // Invalid 440 if err = nullStmt.QueryRow().Scan(&nb); err != nil { 441 dbt.Fatal(err) 442 } 443 if nb.Valid { 444 dbt.Error("Valid NullBool which should be invalid") 445 } 446 // Valid 447 if err = nonNullStmt.QueryRow().Scan(&nb); err != nil { 448 dbt.Fatal(err) 449 } 450 if !nb.Valid { 451 dbt.Error("Invalid NullBool which should be valid") 452 } else if nb.Bool != true { 453 dbt.Errorf("Unexpected NullBool value: %t (should be true)", nb.Bool) 454 } 455 456 // NullFloat64 457 var nf sql.NullFloat64 458 // Invalid 459 if err = nullStmt.QueryRow().Scan(&nf); err != nil { 460 dbt.Fatal(err) 461 } 462 if nf.Valid { 463 dbt.Error("Valid NullFloat64 which should be invalid") 464 } 465 // Valid 466 if err = nonNullStmt.QueryRow().Scan(&nf); err != nil { 467 dbt.Fatal(err) 468 } 469 if !nf.Valid { 470 dbt.Error("Invalid NullFloat64 which should be valid") 471 } else if nf.Float64 != float64(1) { 472 dbt.Errorf("Unexpected NullFloat64 value: %f (should be 1.0)", nf.Float64) 473 } 474 475 // NullInt64 476 var ni sql.NullInt64 477 // Invalid 478 if err = nullStmt.QueryRow().Scan(&ni); err != nil { 479 dbt.Fatal(err) 480 } 481 if ni.Valid { 482 dbt.Error("Valid NullInt64 which should be invalid") 483 } 484 // Valid 485 if err = nonNullStmt.QueryRow().Scan(&ni); err != nil { 486 dbt.Fatal(err) 487 } 488 if !ni.Valid { 489 dbt.Error("Invalid NullInt64 which should be valid") 490 } else if ni.Int64 != int64(1) { 491 dbt.Errorf("Unexpected NullInt64 value: %d (should be 1)", ni.Int64) 492 } 493 494 // NullString 495 var ns sql.NullString 496 // Invalid 497 if err = nullStmt.QueryRow().Scan(&ns); err != nil { 498 dbt.Fatal(err) 499 } 500 if ns.Valid { 501 dbt.Error("Valid NullString which should be invalid") 502 } 503 // Valid 504 if err = nonNullStmt.QueryRow().Scan(&ns); err != nil { 505 dbt.Fatal(err) 506 } 507 if !ns.Valid { 508 dbt.Error("Invalid NullString which should be valid") 509 } else if ns.String != `1` { 510 dbt.Error("Unexpected NullString value:" + ns.String + " (should be `1`)") 511 } 512 513 // nil-bytes 514 var b []byte 515 // Read nil 516 if err = nullStmt.QueryRow().Scan(&b); err != nil { 517 dbt.Fatal(err) 518 } 519 if b != nil { 520 dbt.Error("Non-nil []byte wich should be nil") 521 } 522 // Read non-nil 523 if err = nonNullStmt.QueryRow().Scan(&b); err != nil { 524 dbt.Fatal(err) 525 } 526 if b == nil { 527 dbt.Error("Nil []byte wich should be non-nil") 528 } 529 // Insert nil 530 b = nil 531 success := false 532 if err = dbt.db.QueryRow("SELECT ? IS NULL", b).Scan(&success); err != nil { 533 dbt.Fatal(err) 534 } 535 if !success { 536 dbt.Error("Inserting []byte(nil) as NULL failed") 537 } 538 // Check input==output with input==nil 539 b = nil 540 if err = dbt.db.QueryRow("SELECT ?", b).Scan(&b); err != nil { 541 dbt.Fatal(err) 542 } 543 if b != nil { 544 dbt.Error("Non-nil echo from nil input") 545 } 546 // Check input==output with input!=nil 547 b = []byte("") 548 if err = dbt.db.QueryRow("SELECT ?", b).Scan(&b); err != nil { 549 dbt.Fatal(err) 550 } 551 if b == nil { 552 dbt.Error("nil echo from non-nil input") 553 } 554 555 // Insert NULL 556 dbt.mustExec("CREATE TABLE test (dummmy1 int, value int, dummy2 int)") 557 558 dbt.mustExec("INSERT INTO test VALUES (?, ?, ?)", 1, nil, 2) 559 560 var out interface{} 561 rows := dbt.mustQuery("SELECT * FROM test") 562 if rows.Next() { 563 rows.Scan(&out) 564 if out != nil { 565 dbt.Errorf("%v != nil", out) 566 } 567 } else { 568 dbt.Error("no data") 569 } 570 }) 571 } 572 573 func TestLongData(t *testing.T) { 574 runTests(t, dsn, func(dbt *DBTest) { 575 var maxAllowedPacketSize int 576 err := dbt.db.QueryRow("select @@max_allowed_packet").Scan(&maxAllowedPacketSize) 577 if err != nil { 578 dbt.Fatal(err) 579 } 580 maxAllowedPacketSize-- 581 582 // don't get too ambitious 583 if maxAllowedPacketSize > 1<<25 { 584 maxAllowedPacketSize = 1 << 25 585 } 586 587 dbt.mustExec("CREATE TABLE test (value LONGBLOB)") 588 589 in := strings.Repeat(`a`, maxAllowedPacketSize+1) 590 var out string 591 var rows *sql.Rows 592 593 // Long text data 594 const nonDataQueryLen = 28 // length query w/o value 595 inS := in[:maxAllowedPacketSize-nonDataQueryLen] 596 dbt.mustExec("INSERT INTO test VALUES('" + inS + "')") 597 rows = dbt.mustQuery("SELECT value FROM test") 598 if rows.Next() { 599 rows.Scan(&out) 600 if inS != out { 601 dbt.Fatalf("LONGBLOB: length in: %d, length out: %d", len(inS), len(out)) 602 } 603 if rows.Next() { 604 dbt.Error("LONGBLOB: unexpexted row") 605 } 606 } else { 607 dbt.Fatalf("LONGBLOB: no data") 608 } 609 610 // Empty table 611 dbt.mustExec("TRUNCATE TABLE test") 612 613 // Long binary data 614 dbt.mustExec("INSERT INTO test VALUES(?)", in) 615 rows = dbt.mustQuery("SELECT value FROM test WHERE 1=?", 1) 616 if rows.Next() { 617 rows.Scan(&out) 618 if in != out { 619 dbt.Fatalf("LONGBLOB: length in: %d, length out: %d", len(in), len(out)) 620 } 621 if rows.Next() { 622 dbt.Error("LONGBLOB: unexpexted row") 623 } 624 } else { 625 if err = rows.Err(); err != nil { 626 dbt.Fatalf("LONGBLOB: no data (err: %s)", err.Error()) 627 } else { 628 dbt.Fatal("LONGBLOB: no data (err: <nil>)") 629 } 630 } 631 }) 632 } 633 634 func TestLoadData(t *testing.T) { 635 runTests(t, dsn, func(dbt *DBTest) { 636 verifyLoadDataResult := func() { 637 rows, err := dbt.db.Query("SELECT * FROM test") 638 if err != nil { 639 dbt.Fatal(err.Error()) 640 } 641 642 i := 0 643 values := [4]string{ 644 "a string", 645 "a string containing a \t", 646 "a string containing a \n", 647 "a string containing both \t\n", 648 } 649 650 var id int 651 var value string 652 653 for rows.Next() { 654 i++ 655 err = rows.Scan(&id, &value) 656 if err != nil { 657 dbt.Fatal(err.Error()) 658 } 659 if i != id { 660 dbt.Fatalf("%d != %d", i, id) 661 } 662 if values[i-1] != value { 663 dbt.Fatalf("%s != %s", values[i-1], value) 664 } 665 } 666 err = rows.Err() 667 if err != nil { 668 dbt.Fatal(err.Error()) 669 } 670 671 if i != 4 { 672 dbt.Fatalf("Rows count mismatch. Got %d, want 4", i) 673 } 674 } 675 file, err := ioutil.TempFile("", "gotest") 676 defer os.Remove(file.Name()) 677 if err != nil { 678 dbt.Fatal(err) 679 } 680 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") 681 file.Close() 682 683 dbt.db.Exec("DROP TABLE IF EXISTS test") 684 dbt.mustExec("CREATE TABLE test (id INT NOT NULL PRIMARY KEY, value TEXT NOT NULL) CHARACTER SET utf8") 685 686 // Local File 687 RegisterLocalFile(file.Name()) 688 dbt.mustExec(fmt.Sprintf("LOAD DATA LOCAL INFILE '%q' INTO TABLE test", file.Name())) 689 verifyLoadDataResult() 690 // negative test 691 _, err = dbt.db.Exec("LOAD DATA LOCAL INFILE 'doesnotexist' INTO TABLE test") 692 if err == nil { 693 dbt.Fatal("Load non-existent file didn't fail") 694 } else if err.Error() != "Local File 'doesnotexist' is not registered. Use the DSN parameter 'allowAllFiles=true' to allow all files" { 695 dbt.Fatal(err.Error()) 696 } 697 698 // Empty table 699 dbt.mustExec("TRUNCATE TABLE test") 700 701 // Reader 702 RegisterReaderHandler("test", func() io.Reader { 703 file, err = os.Open(file.Name()) 704 if err != nil { 705 dbt.Fatal(err) 706 } 707 return file 708 }) 709 dbt.mustExec("LOAD DATA LOCAL INFILE 'Reader::test' INTO TABLE test") 710 verifyLoadDataResult() 711 // negative test 712 _, err = dbt.db.Exec("LOAD DATA LOCAL INFILE 'Reader::doesnotexist' INTO TABLE test") 713 if err == nil { 714 dbt.Fatal("Load non-existent Reader didn't fail") 715 } else if err.Error() != "Reader 'doesnotexist' is not registered" { 716 dbt.Fatal(err.Error()) 717 } 718 }) 719 } 720 721 func TestFoundRows(t *testing.T) { 722 runTests(t, dsn, func(dbt *DBTest) { 723 dbt.mustExec("CREATE TABLE test (id INT NOT NULL ,data INT NOT NULL)") 724 dbt.mustExec("INSERT INTO test (id, data) VALUES (0, 0),(0, 0),(1, 0),(1, 0),(1, 1)") 725 726 res := dbt.mustExec("UPDATE test SET data = 1 WHERE id = 0") 727 count, err := res.RowsAffected() 728 if err != nil { 729 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 730 } 731 if count != 2 { 732 dbt.Fatalf("Expected 2 affected rows, got %d", count) 733 } 734 res = dbt.mustExec("UPDATE test SET data = 1 WHERE id = 1") 735 count, err = res.RowsAffected() 736 if err != nil { 737 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 738 } 739 if count != 2 { 740 dbt.Fatalf("Expected 2 affected rows, got %d", count) 741 } 742 }) 743 runTests(t, dsn+"&clientFoundRows=true", func(dbt *DBTest) { 744 dbt.mustExec("CREATE TABLE test (id INT NOT NULL ,data INT NOT NULL)") 745 dbt.mustExec("INSERT INTO test (id, data) VALUES (0, 0),(0, 0),(1, 0),(1, 0),(1, 1)") 746 747 res := dbt.mustExec("UPDATE test SET data = 1 WHERE id = 0") 748 count, err := res.RowsAffected() 749 if err != nil { 750 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 751 } 752 if count != 2 { 753 dbt.Fatalf("Expected 2 matched rows, got %d", count) 754 } 755 res = dbt.mustExec("UPDATE test SET data = 1 WHERE id = 1") 756 count, err = res.RowsAffected() 757 if err != nil { 758 dbt.Fatalf("res.RowsAffected() returned error: %s", err.Error()) 759 } 760 if count != 3 { 761 dbt.Fatalf("Expected 3 matched rows, got %d", count) 762 } 763 }) 764 } 765 766 func TestStrict(t *testing.T) { 767 // ALLOW_INVALID_DATES to get rid of stricter modes - we want to test for warnings, not errors 768 relaxedDsn := dsn + "&sql_mode=ALLOW_INVALID_DATES" 769 runTests(t, relaxedDsn, func(dbt *DBTest) { 770 dbt.mustExec("CREATE TABLE test (a TINYINT NOT NULL, b CHAR(4))") 771 772 var queries = [...]struct { 773 in string 774 codes []string 775 }{ 776 {"DROP TABLE IF EXISTS no_such_table", []string{"1051"}}, 777 {"INSERT INTO test VALUES(10,'mysql'),(NULL,'test'),(300,'Open Source')", []string{"1265", "1048", "1264", "1265"}}, 778 } 779 var err error 780 781 var checkWarnings = func(err error, mode string, idx int) { 782 if err == nil { 783 dbt.Errorf("Expected STRICT error on query [%s] %s", mode, queries[idx].in) 784 } 785 786 if warnings, ok := err.(MySQLWarnings); ok { 787 var codes = make([]string, len(warnings)) 788 for i := range warnings { 789 codes[i] = warnings[i].Code 790 } 791 if len(codes) != len(queries[idx].codes) { 792 dbt.Errorf("Unexpected STRICT error count on query [%s] %s: Wanted %v, Got %v", mode, queries[idx].in, queries[idx].codes, codes) 793 } 794 795 for i := range warnings { 796 if codes[i] != queries[idx].codes[i] { 797 dbt.Errorf("Unexpected STRICT error codes on query [%s] %s: Wanted %v, Got %v", mode, queries[idx].in, queries[idx].codes, codes) 798 return 799 } 800 } 801 802 } else { 803 dbt.Errorf("Unexpected error on query [%s] %s: %s", mode, queries[idx].in, err.Error()) 804 } 805 } 806 807 // text protocol 808 for i := range queries { 809 _, err = dbt.db.Exec(queries[i].in) 810 checkWarnings(err, "text", i) 811 } 812 813 var stmt *sql.Stmt 814 815 // binary protocol 816 for i := range queries { 817 stmt, err = dbt.db.Prepare(queries[i].in) 818 if err != nil { 819 dbt.Errorf("Error on preparing query %s: %s", queries[i].in, err.Error()) 820 } 821 822 _, err = stmt.Exec() 823 checkWarnings(err, "binary", i) 824 825 err = stmt.Close() 826 if err != nil { 827 dbt.Errorf("Error on closing stmt for query %s: %s", queries[i].in, err.Error()) 828 } 829 } 830 }) 831 } 832 833 func TestTLS(t *testing.T) { 834 tlsTest := func(dbt *DBTest) { 835 if err := dbt.db.Ping(); err != nil { 836 if err == ErrNoTLS { 837 dbt.Skip("Server does not support TLS") 838 } else { 839 dbt.Fatalf("Error on Ping: %s", err.Error()) 840 } 841 } 842 843 rows := dbt.mustQuery("SHOW STATUS LIKE 'Ssl_cipher'") 844 845 var variable, value *sql.RawBytes 846 for rows.Next() { 847 if err := rows.Scan(&variable, &value); err != nil { 848 dbt.Fatal(err.Error()) 849 } 850 851 if value == nil { 852 dbt.Fatal("No Cipher") 853 } 854 } 855 } 856 857 runTests(t, dsn+"&tls=skip-verify", tlsTest) 858 859 // Verify that registering / using a custom cfg works 860 RegisterTLSConfig("custom-skip-verify", &tls.Config{ 861 InsecureSkipVerify: true, 862 }) 863 runTests(t, dsn+"&tls=custom-skip-verify", tlsTest) 864 } 865 866 func TestReuseClosedConnection(t *testing.T) { 867 // this test does not use sql.database, it uses the driver directly 868 if !available { 869 t.Skipf("MySQL-Server not running on %s", netAddr) 870 } 871 872 md := &MySQLDriver{} 873 conn, err := md.Open(dsn) 874 if err != nil { 875 t.Fatalf("Error connecting: %s", err.Error()) 876 } 877 stmt, err := conn.Prepare("DO 1") 878 if err != nil { 879 t.Fatalf("Error preparing statement: %s", err.Error()) 880 } 881 _, err = stmt.Exec(nil) 882 if err != nil { 883 t.Fatalf("Error executing statement: %s", err.Error()) 884 } 885 err = conn.Close() 886 if err != nil { 887 t.Fatalf("Error closing connection: %s", err.Error()) 888 } 889 890 defer func() { 891 if err := recover(); err != nil { 892 t.Errorf("Panic after reusing a closed connection: %v", err) 893 } 894 }() 895 _, err = stmt.Exec(nil) 896 if err != nil && err != driver.ErrBadConn { 897 t.Errorf("Unexpected error '%s', expected '%s'", 898 err.Error(), driver.ErrBadConn.Error()) 899 } 900 } 901 902 func TestCharset(t *testing.T) { 903 if !available { 904 t.Skipf("MySQL-Server not running on %s", netAddr) 905 } 906 907 mustSetCharset := func(charsetParam, expected string) { 908 runTests(t, dsn+"&"+charsetParam, func(dbt *DBTest) { 909 rows := dbt.mustQuery("SELECT @@character_set_connection") 910 defer rows.Close() 911 912 if !rows.Next() { 913 dbt.Fatalf("Error getting connection charset: %s", rows.Err()) 914 } 915 916 var got string 917 rows.Scan(&got) 918 919 if got != expected { 920 dbt.Fatalf("Expected connection charset %s but got %s", expected, got) 921 } 922 }) 923 } 924 925 // non utf8 test 926 mustSetCharset("charset=ascii", "ascii") 927 928 // when the first charset is invalid, use the second 929 mustSetCharset("charset=none,utf8", "utf8") 930 931 // when the first charset is valid, use it 932 mustSetCharset("charset=ascii,utf8", "ascii") 933 mustSetCharset("charset=utf8,ascii", "utf8") 934 } 935 936 func TestFailingCharset(t *testing.T) { 937 runTests(t, dsn+"&charset=none", func(dbt *DBTest) { 938 // run query to really establish connection... 939 _, err := dbt.db.Exec("SELECT 1") 940 if err == nil { 941 dbt.db.Close() 942 t.Fatalf("Connection must not succeed without a valid charset") 943 } 944 }) 945 } 946 947 func TestCollation(t *testing.T) { 948 if !available { 949 t.Skipf("MySQL-Server not running on %s", netAddr) 950 } 951 952 defaultCollation := "utf8_general_ci" 953 testCollations := []string{ 954 "", // do not set 955 defaultCollation, // driver default 956 "latin1_general_ci", 957 "binary", 958 "utf8_unicode_ci", 959 "utf8mb4_general_ci", 960 } 961 962 for _, collation := range testCollations { 963 var expected, tdsn string 964 if collation != "" { 965 tdsn = dsn + "&collation=" + collation 966 expected = collation 967 } else { 968 tdsn = dsn 969 expected = defaultCollation 970 } 971 972 runTests(t, tdsn, func(dbt *DBTest) { 973 var got string 974 if err := dbt.db.QueryRow("SELECT @@collation_connection").Scan(&got); err != nil { 975 dbt.Fatal(err) 976 } 977 978 if got != expected { 979 dbt.Fatalf("Expected connection collation %s but got %s", expected, got) 980 } 981 }) 982 } 983 } 984 985 func TestRawBytesResultExceedsBuffer(t *testing.T) { 986 runTests(t, dsn, func(dbt *DBTest) { 987 // defaultBufSize from buffer.go 988 expected := strings.Repeat("abc", defaultBufSize) 989 990 rows := dbt.mustQuery("SELECT '" + expected + "'") 991 defer rows.Close() 992 if !rows.Next() { 993 dbt.Error("expected result, got none") 994 } 995 var result sql.RawBytes 996 rows.Scan(&result) 997 if expected != string(result) { 998 dbt.Error("result did not match expected value") 999 } 1000 }) 1001 } 1002 1003 func TestTimezoneConversion(t *testing.T) { 1004 zones := []string{"UTC", "US/Central", "US/Pacific", "Local"} 1005 1006 // Regression test for timezone handling 1007 tzTest := func(dbt *DBTest) { 1008 1009 // Create table 1010 dbt.mustExec("CREATE TABLE test (ts TIMESTAMP)") 1011 1012 // Insert local time into database (should be converted) 1013 usCentral, _ := time.LoadLocation("US/Central") 1014 now := time.Now().In(usCentral) 1015 dbt.mustExec("INSERT INTO test VALUE (?)", now) 1016 1017 // Retrieve time from DB 1018 rows := dbt.mustQuery("SELECT ts FROM test") 1019 if !rows.Next() { 1020 dbt.Fatal("Didn't get any rows out") 1021 } 1022 1023 var nowDB time.Time 1024 err := rows.Scan(&nowDB) 1025 if err != nil { 1026 dbt.Fatal("Err", err) 1027 } 1028 1029 // Check that dates match 1030 if now.Unix() != nowDB.Unix() { 1031 dbt.Errorf("Times don't match.\n") 1032 dbt.Errorf(" Now(%v)=%v\n", usCentral, now) 1033 dbt.Errorf(" Now(UTC)=%v\n", nowDB) 1034 } 1035 } 1036 1037 for _, tz := range zones { 1038 runTests(t, dsn+"&parseTime=true&loc="+url.QueryEscape(tz), tzTest) 1039 } 1040 } 1041 1042 // This tests for https://github.com/go-sql-driver/mysql/pull/139 1043 // 1044 // An extra (invisible) nil byte was being added to the beginning of positive 1045 // time strings. 1046 func TestTimeSign(t *testing.T) { 1047 runTests(t, dsn, func(dbt *DBTest) { 1048 var sTimes = []struct { 1049 value string 1050 fieldType string 1051 }{ 1052 {"12:34:56", "TIME"}, 1053 {"-12:34:56", "TIME"}, 1054 // As described in http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html 1055 // they *should* work, but only in 5.6+. 1056 // { "12:34:56.789", "TIME(3)" }, 1057 // { "-12:34:56.789", "TIME(3)" }, 1058 } 1059 1060 for _, sTime := range sTimes { 1061 dbt.db.Exec("DROP TABLE IF EXISTS test") 1062 dbt.mustExec("CREATE TABLE test (id INT, time_field " + sTime.fieldType + ")") 1063 dbt.mustExec("INSERT INTO test (id, time_field) VALUES(1, '" + sTime.value + "')") 1064 rows := dbt.mustQuery("SELECT time_field FROM test WHERE id = ?", 1) 1065 if rows.Next() { 1066 var oTime string 1067 rows.Scan(&oTime) 1068 if oTime != sTime.value { 1069 dbt.Errorf(`time values differ: got %q, expected %q.`, oTime, sTime.value) 1070 } 1071 } else { 1072 dbt.Error("expecting at least one row.") 1073 } 1074 } 1075 }) 1076 } 1077 1078 // Special cases 1079 1080 func TestRowsClose(t *testing.T) { 1081 runTests(t, dsn, func(dbt *DBTest) { 1082 rows, err := dbt.db.Query("SELECT 1") 1083 if err != nil { 1084 dbt.Fatal(err) 1085 } 1086 1087 err = rows.Close() 1088 if err != nil { 1089 dbt.Fatal(err) 1090 } 1091 1092 if rows.Next() { 1093 dbt.Fatal("Unexpected row after rows.Close()") 1094 } 1095 1096 err = rows.Err() 1097 if err != nil { 1098 dbt.Fatal(err) 1099 } 1100 }) 1101 } 1102 1103 // dangling statements 1104 // http://code.google.com/p/go/issues/detail?id=3865 1105 func TestCloseStmtBeforeRows(t *testing.T) { 1106 runTests(t, dsn, func(dbt *DBTest) { 1107 stmt, err := dbt.db.Prepare("SELECT 1") 1108 if err != nil { 1109 dbt.Fatal(err) 1110 } 1111 1112 rows, err := stmt.Query() 1113 if err != nil { 1114 stmt.Close() 1115 dbt.Fatal(err) 1116 } 1117 defer rows.Close() 1118 1119 err = stmt.Close() 1120 if err != nil { 1121 dbt.Fatal(err) 1122 } 1123 1124 if !rows.Next() { 1125 dbt.Fatal("Getting row failed") 1126 } else { 1127 err = rows.Err() 1128 if err != nil { 1129 dbt.Fatal(err) 1130 } 1131 1132 var out bool 1133 err = rows.Scan(&out) 1134 if err != nil { 1135 dbt.Fatalf("Error on rows.Scan(): %s", err.Error()) 1136 } 1137 if out != true { 1138 dbt.Errorf("true != %t", out) 1139 } 1140 } 1141 }) 1142 } 1143 1144 // It is valid to have multiple Rows for the same Stmt 1145 // http://code.google.com/p/go/issues/detail?id=3734 1146 func TestStmtMultiRows(t *testing.T) { 1147 runTests(t, dsn, func(dbt *DBTest) { 1148 stmt, err := dbt.db.Prepare("SELECT 1 UNION SELECT 0") 1149 if err != nil { 1150 dbt.Fatal(err) 1151 } 1152 1153 rows1, err := stmt.Query() 1154 if err != nil { 1155 stmt.Close() 1156 dbt.Fatal(err) 1157 } 1158 defer rows1.Close() 1159 1160 rows2, err := stmt.Query() 1161 if err != nil { 1162 stmt.Close() 1163 dbt.Fatal(err) 1164 } 1165 defer rows2.Close() 1166 1167 var out bool 1168 1169 // 1 1170 if !rows1.Next() { 1171 dbt.Fatal("1st rows1.Next failed") 1172 } else { 1173 err = rows1.Err() 1174 if err != nil { 1175 dbt.Fatal(err) 1176 } 1177 1178 err = rows1.Scan(&out) 1179 if err != nil { 1180 dbt.Fatalf("Error on rows.Scan(): %s", err.Error()) 1181 } 1182 if out != true { 1183 dbt.Errorf("true != %t", out) 1184 } 1185 } 1186 1187 if !rows2.Next() { 1188 dbt.Fatal("1st rows2.Next failed") 1189 } else { 1190 err = rows2.Err() 1191 if err != nil { 1192 dbt.Fatal(err) 1193 } 1194 1195 err = rows2.Scan(&out) 1196 if err != nil { 1197 dbt.Fatalf("Error on rows.Scan(): %s", err.Error()) 1198 } 1199 if out != true { 1200 dbt.Errorf("true != %t", out) 1201 } 1202 } 1203 1204 // 2 1205 if !rows1.Next() { 1206 dbt.Fatal("2nd rows1.Next failed") 1207 } else { 1208 err = rows1.Err() 1209 if err != nil { 1210 dbt.Fatal(err) 1211 } 1212 1213 err = rows1.Scan(&out) 1214 if err != nil { 1215 dbt.Fatalf("Error on rows.Scan(): %s", err.Error()) 1216 } 1217 if out != false { 1218 dbt.Errorf("false != %t", out) 1219 } 1220 1221 if rows1.Next() { 1222 dbt.Fatal("Unexpected row on rows1") 1223 } 1224 err = rows1.Close() 1225 if err != nil { 1226 dbt.Fatal(err) 1227 } 1228 } 1229 1230 if !rows2.Next() { 1231 dbt.Fatal("2nd rows2.Next failed") 1232 } else { 1233 err = rows2.Err() 1234 if err != nil { 1235 dbt.Fatal(err) 1236 } 1237 1238 err = rows2.Scan(&out) 1239 if err != nil { 1240 dbt.Fatalf("Error on rows.Scan(): %s", err.Error()) 1241 } 1242 if out != false { 1243 dbt.Errorf("false != %t", out) 1244 } 1245 1246 if rows2.Next() { 1247 dbt.Fatal("Unexpected row on rows2") 1248 } 1249 err = rows2.Close() 1250 if err != nil { 1251 dbt.Fatal(err) 1252 } 1253 } 1254 }) 1255 } 1256 1257 // Regression test for 1258 // * more than 32 NULL parameters (issue 209) 1259 // * more parameters than fit into the buffer (issue 201) 1260 func TestPreparedManyCols(t *testing.T) { 1261 const numParams = defaultBufSize 1262 runTests(t, dsn, func(dbt *DBTest) { 1263 query := "SELECT ?" + strings.Repeat(",?", numParams-1) 1264 stmt, err := dbt.db.Prepare(query) 1265 if err != nil { 1266 dbt.Fatal(err) 1267 } 1268 defer stmt.Close() 1269 // create more parameters than fit into the buffer 1270 // which will take nil-values 1271 params := make([]interface{}, numParams) 1272 rows, err := stmt.Query(params...) 1273 if err != nil { 1274 stmt.Close() 1275 dbt.Fatal(err) 1276 } 1277 defer rows.Close() 1278 }) 1279 } 1280 1281 func TestConcurrent(t *testing.T) { 1282 if enabled, _ := readBool(os.Getenv("MYSQL_TEST_CONCURRENT")); !enabled { 1283 t.Skip("MYSQL_TEST_CONCURRENT env var not set") 1284 } 1285 1286 runTests(t, dsn, func(dbt *DBTest) { 1287 var max int 1288 err := dbt.db.QueryRow("SELECT @@max_connections").Scan(&max) 1289 if err != nil { 1290 dbt.Fatalf("%s", err.Error()) 1291 } 1292 dbt.Logf("Testing up to %d concurrent connections \r\n", max) 1293 1294 var remaining, succeeded int32 = int32(max), 0 1295 1296 var wg sync.WaitGroup 1297 wg.Add(max) 1298 1299 var fatalError string 1300 var once sync.Once 1301 fatalf := func(s string, vals ...interface{}) { 1302 once.Do(func() { 1303 fatalError = fmt.Sprintf(s, vals...) 1304 }) 1305 } 1306 1307 for i := 0; i < max; i++ { 1308 go func(id int) { 1309 defer wg.Done() 1310 1311 tx, err := dbt.db.Begin() 1312 atomic.AddInt32(&remaining, -1) 1313 1314 if err != nil { 1315 if err.Error() != "Error 1040: Too many connections" { 1316 fatalf("Error on Conn %d: %s", id, err.Error()) 1317 } 1318 return 1319 } 1320 1321 // keep the connection busy until all connections are open 1322 for remaining > 0 { 1323 if _, err = tx.Exec("DO 1"); err != nil { 1324 fatalf("Error on Conn %d: %s", id, err.Error()) 1325 return 1326 } 1327 } 1328 1329 if err = tx.Commit(); err != nil { 1330 fatalf("Error on Conn %d: %s", id, err.Error()) 1331 return 1332 } 1333 1334 // everything went fine with this connection 1335 atomic.AddInt32(&succeeded, 1) 1336 }(i) 1337 } 1338 1339 // wait until all conections are open 1340 wg.Wait() 1341 1342 if fatalError != "" { 1343 dbt.Fatal(fatalError) 1344 } 1345 1346 dbt.Logf("Reached %d concurrent connections\r\n", succeeded) 1347 }) 1348 } 1349 1350 // Tests custom dial functions 1351 func TestCustomDial(t *testing.T) { 1352 if !available { 1353 t.Skipf("MySQL-Server not running on %s", netAddr) 1354 } 1355 1356 // our custom dial function which justs wraps net.Dial here 1357 RegisterDial("mydial", func(addr string) (net.Conn, error) { 1358 return net.Dial(prot, addr) 1359 }) 1360 1361 db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@mydial(%s)/%s?timeout=30s&strict=true", user, pass, addr, dbname)) 1362 if err != nil { 1363 t.Fatalf("Error connecting: %s", err.Error()) 1364 } 1365 defer db.Close() 1366 1367 if _, err = db.Exec("DO 1"); err != nil { 1368 t.Fatalf("Connection failed: %s", err.Error()) 1369 } 1370 }