Golang MySQL Diagnostic tour (20 million open room data)

This is an article created on, where the information may have evolved or changed.
Recently, due to some vulnerabilities, 20 million open house data has been exposed. The data is in csv format, which is slow to open, so I want to import this 2000w open house data into mysql, and then use go to write a simple query tool.


The tragedy began:

The first step is to download the mysql module, go get github.com/go-sql-driver/mysql,

The second step, write a small example to test

package main

import (
"database / sql" // This package must be quoted
"encoding / json"
"fmt" // This was mentioned in the previous chapter
_ "github.com/go-sql-driver/mysql" // This is the package just downloaded
)

// Define a structure, which needs to start with a capital, and the field name also needs to start with a capital, otherwise the json module will not recognize it
// Structure members can only be accessed by the outside world
type User struct {
User string `json:" user "`
Password string `json:" password "`
Host string `json:" host "`
}

// Main method as always
func main () {
// The format is a bit strange, @tcp refers to the network protocol (does it support udp?), Then the domain name and port
db, e: = sql.Open ("mysql", "root: @tcp (192.168.7.15:3306) / mysql? charset = utf8")
if e! = nil {// If the connection is wrong, e will not be nil
print ("ERROR?")
return
}

defer db.Close ()

// As a reminder, running here does not mean that the database connection is completely OK, because the password will be verified only after the first SQL is sent. Khan ~!
rows, e: = db.Query ("select user, password, host from mysql.user")
if e! = nil {
fmt.Printf ("query error !!% v \ n", e)
return
}
if rows == nil {
print ("Rows is nil")
return
}
fmt.Println ("DB rows.Next")
for rows.Next () {// Like java ResultSet, need to read next
user: = new (User)
// rows seem to only support the Scan method. Continue to sweat ~! Of course, you can get the field order through GetColumns ()
row_err: = rows.Scan (& user.User, & user.Password, & user.Host)
if row_err! = nil {
print ("Row error !!")
return
}
b, _: = json.Marshal (user)
fmt.Println (string (b)) // There is no judgment error here, huh, huh, generally no mistake
}
fmt.Println ("Done")
}
The results have been wrong:

panic: runtime error: index out of range
The
goroutine 1 [running]:
github.com/go-sql-driver/mysql.readLengthEncodedInteger(0x10fb0037, 0x1, 0xfc9, 0x0, 0x0, ...)
E: /go/src/github.com/go-sql-driver/mysql/utils.go: 406 + 0x3e8
github.com/go-sql-driver/mysql.skipLengthEnodedString(0x10fb0037, 0x1, 0xfc9, 0x2, 0x0, ...)
E: /go/src/github.com/go-sql-driver/mysql/utils.go: 366 + 0x38
github.com/go-sql-driver/mysql.(*mysqlConn).readColumns(0x10f88230, 0x1, 0x10f86500, 0x1, 0x1, ...)
E: /go/src/github.com/go-sql-driver/mysql/packets.go: 482 + 0x389
github.com/go-sql-driver/mysql.(*mysqlConn).getSystemVar(0x10f88230, 0x530b88, 0x12, 0x0, 0x0, ...)
E: /go/src/github.com/go-sql-driver/mysql/connection.go: 228 + 0x118
github.com/go-sql-driver/mysql.(*mysqlDriver).Open(0x5f0bf4, 0x547aa8, 0x2f, 0x1, 0x10f9f900, ...)
E: /go/src/github.com/go-sql-driver/mysql/driver.go: 70 + 0x2de
database / sql. (* DB) .conn (0x10f85e40, 0x10f50228, 0xff014c, 0x5)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/database/sql/sql.go: 484 + 0x15e
database / sql. (* DB) .query (0x10f85e40, 0x527b68, 0x8, 0x0, 0x0, ...)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/database/sql/sql.go: 708 + 0x58
database / sql. (* DB) .Query (0x10f85e40, 0x527b68, 0x8, 0x0, 0x0, ...)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/database/sql/sql.go: 699 + 0x6b
main.main ()
E: /go/src/testmysql/testmysql.go: 54 + 0x89
The
goroutine 3 [syscall]:
syscall.Syscall6 (0x7c80a7bd, 0x5, 0xf70, 0x10f86420, 0x10f50280, ...)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/runtime/zsyscall_windows_windows_386.c: 97 + 0x49
syscall.GetQueuedCompletionStatus (0xf70, 0x10f86420, 0x10f50280, 0x10f50278, 0xffffffff, ...)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/syscall/zsyscall_windows_386.go: 507 + 0x7e
net. (* resultSrv) .Run (0x10f50260)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/net/fd_windows.go: 150 + 0x11a
created by net.startServer
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/net/fd_windows.go: 285 + 0xde
The
goroutine 4 [select]:
net. (* ioSrv) .ProcessRemoteIO (0x10f50268)
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/net/fd_windows.go: 183 + 0x171
created by net.startServer
C: /Users/ADMINI~1/AppData/Local/Temp/2/bindist465310315/go/src/pkg/net/fd_windows.go: 293 + 0x163
exit status 2

At first I suspected that Windows did not install the mysql driver, but I was interested to analyze the stack information and just learned something new.
Log Dafa, track readLengthEncodedInteger, and add the following debugging code, print the memory information of b [] byte, and find that b [0] = 0xfe, but there is no data behind, so the index of the array b overflows. The complete code is as follows:

func readLengthEncodedInteger (b [] byte) (num uint64, isNull bool, n int) {
fmt.Println (b)
fmt.Printf ("0x% 02x \ n", b [0])
switch b [0] {

// 251: NULL
case 0xfb:
n = 1
isNull = true
return

// 252: value of following 2
case 0xfc:
num = uint64 (b [1]) | uint64 (b [2]) << 8
n = 3
return

// 253: value of following 3
case 0xfd:
num = uint64 (b [1]) | uint64 (b [2]) << 8 | uint64 (b [3]) << 16
n = 4
return

// 254: value of following 8
case 0xfe:
num = uint64 (b [1]) | uint64 (b [2]) << 8 | uint64 (b [3]) << 16 |
uint64 (b [4]) << 24 | uint64 (b [5]) << 32 | uint64 (b [6]) << 40 |
uint64 (b [7]) << 48 | uint64 (b [8]) << 54
n = 9
return
}

// 0-250: value of first byte
num = uint64 (b [0])
n = 1
return
}

Continue to track and find: there is a very strange exit in the readColumns of package.go, but there is no processing of this 0xfe, and the strange thing is that the for loop can only return normally here. . .
func (mc * mysqlConn) readColumns (count int) (columns [] mysqlField, err error) {
var data [] byte
var i, pos, n int
var name [] byte

columns = make ([] mysqlField, count)
fmt.Println ("count:")
fmt.Println (count)
for {
data, err= mc.readPacket ()
if err! = nil {
return
}

// EOF Packet
if data [0] == iEOF && len (data) == 5 {
if i! = count {
err = fmt.Errorf ("ColumnsCount mismatch n:% d len:% d", count, len (columns))
}
return
}

Not too contented, Google reads the protocol of mysql http://dev.mysql.com/doc/internals/en/com-field-list-response.html
It is important to find that there is an EOF_Packet behind the Column package, which is 0xfe above, so I suspect this is a bug of the go mysql driver

15.6.5.1. COM_FIELD_LIST response
The response to a COM_FIELD_LIST can either be a

a ERR_Packet or

one or more Column Definition packets and a closing EOF_Packet

So go to the official website of go-sql-driver https://github.com/go-sql-driver/mysql/blob/master/packets.go, I want to pull-> submit the patch of this bug, the result. . .

I wipe! ! This issue has been revised on github as follows:

           // EOF Packet
                if data [0] == iEOF && (len (data) == 5 || len (data) == 1) {
                        if i == count {
                                return columns, nil
                        }
                        return nil, fmt.Errorf ("ColumnsCount mismatch n:% d len:% d", count, len (columns))
                }

Suddenly crash, guess the code that go get is an old version, see README.md
** Current tagged Release: ** June 03, 2013 (Version 1.0.1)

Look again on github

** Current tagged Release: ** May 14, 2013 (Version 1.0)
The question is coming, although the release version on the github library is still old, but the new version from go get has this problem! ! ! ? ? ?


Solution: The only way is to take a github version git clone https://github.com/go-sql-driver/mysql.git, do not use go get version.


Finally, which great god explained why go get and git cloned versions are different? ?


supplement:

1. @ ASTA 谢: Because you have installed github.com/go-sql-driver/mysql before, so you will not update when you go get, you must use go get -u to update, and git clone is the latest version of the update (2013-10-24 16:23:57), this go get -u is an update, but it is not the problem, @jimmykuu: The default is to get the code whose tag is go1, as it is said by jimmykuu: I check the tag as go1 The code: https://github.com/go-sql-driver/mysql/tree/go1 is the same as that obtained by go get, and there are instructions on golang. . . "When checking out or updating a package, get looks for a branch or tag that matches the locally installed version of Go. The most important rule is that if the local installation is running version" go1 ", get searches for a branch or tag named "go1". If no such version exists it retrieves the most recent version of the package. "
So my conclusion is that the go1 tag in go-sql-driver is full of bugs and irresponsible!

2. Just looked at it, the reason why other people did not encounter this problem is because I just use the ancient version mysql4.1, the patch is here fix crash when connect to mysql4.1: https://github.com/go -sql-driver / mysql / commit / 4a178617b97609ebd4d4a0ae5791225540c1bb26 # diff-2357b8494bbd2f27c09e61fc8ef5f092


     }
 The
 // EOF Packet
 -if data [0] == iEOF && len (data) == 5 {
 + if data [0] == iEOF && (len (data) == 5 || len (data) == 1) {
 if i! = count {
 err = fmt.Errorf ("ColumnsCount mismatch n:% d len:% d", count, len (columns))
 }
3. After replacing with the new version of git clone, remember to delete the mysql.a file generated under pkg! !