本文通過(guò)一次mysql packet for query is too large問(wèn)題的排查經(jīng)歷，從go-mysql-driver源碼層面解析問(wèn)題出現(xiàn)的原因及解決方案仆邓，進(jìn)而引出一系列你所不知道的go-mysql-driver操作技巧鲜滩。

1、背景介紹

在某次執(zhí)行大批量的數(shù)據(jù)插入時(shí)节值，為了提高插入的效率徙硅，將逐條插入的sql語(yǔ)句進(jìn)行聚合，直接執(zhí)行聚合之后的sql語(yǔ)句察署，實(shí)現(xiàn)數(shù)據(jù)的批量插入闷游，但是當(dāng)數(shù)據(jù)量很大時(shí)，會(huì)返回如下錯(cuò)誤packet for query is too large. Try adjusting the 'max_allowed_packet' variable on the server贴汪。

通過(guò)查證脐往，在網(wǎng)上發(fā)現(xiàn)了如下解決方案：

//查詢(xún)mysql的max_allowed_packet參數(shù)
show VARIABLES like '%max_allowed_packet%';
//重置該參數(shù)值為100M
set global max_allowed_packet = 1024*1024*100;

重置之后再次執(zhí)行sql的執(zhí)行，依然返回packet for query is too large的錯(cuò)誤信息扳埂，而我提供的sql長(zhǎng)度為91M左右业簿，起初以為是set global max_allowed_packe設(shè)置的臨時(shí)參數(shù)，沒(méi)有生效阳懂，于是修改/usr/local/etc/my.cnf文件的配置信息如下：

# Default Homebrew MySQL service config
[mysqld]
#Only allow connections from localhost
bind-address = 127.0.0.1
max_allowed_packet = 100M
[mysqldump]
max_allowed_packet = 100M

之后重啟mysql服務(wù)梅尤。依然無(wú)法解決該問(wèn)題柜思。

為了確定不是mysql參數(shù)的問(wèn)題，直接執(zhí)行 mysql -uXXX -pXXX -DXXX < insert.sql巷燥，發(fā)現(xiàn)數(shù)據(jù)插入成功赡盘，說(shuō)明mysql確實(shí)沒(méi)有問(wèn)題，那么問(wèn)題只能存在于gorm或者go-mysql-driver中缰揪。

2陨享、問(wèn)題排查

在確定了問(wèn)題出在gorm中之后，使用debug模式查找具體問(wèn)題钝腺，發(fā)現(xiàn)問(wèn)題發(fā)生在go-mysql-driver的packets.go中抛姑。

func (mc *mysqlConn) writePacket(data []byte) error {
    pktLen := len(data) - 4

    if pktLen > mc.maxAllowedPacket {
        return ErrPktTooLarge
    }
    ....
    ....
}

說(shuō)明mysql的確驅(qū)動(dòng)對(duì)sql的長(zhǎng)度進(jìn)行了限制。繼續(xù)分析我們發(fā)現(xiàn)在go-mysql-driver/const.go中默認(rèn)將MaxAllowedPacket參數(shù)限制在了4M艳狐。

const (
    defaultAuthPlugin       = "mysql_native_password"
    defaultMaxAllowedPacket = 4 << 20 // 4 MiB
    minProtocolVersion      = 10
    maxPacketSize           = 1<<24 - 1
    timeFormat              = "2006-01-02 15:04:05.999999"
)

繼續(xù)分析可以發(fā)現(xiàn)在go-mysql-driver/connector.go中有如下邏輯：

func (c *connector) Connect(ctx context.Context) (driver.Conn, error) {
    var err error

    // New mysqlConn
    mc := &mysqlConn{
        maxAllowedPacket: maxPacketSize,
        maxWriteSize:     maxPacketSize - 1,
        closech:          make(chan struct{}),
        cfg:              c.cfg,
    }
    mc.parseTime = mc.cfg.ParseTime
    ....
    ....
    
    if mc.cfg.MaxAllowedPacket > 0 {
        mc.maxAllowedPacket = mc.cfg.MaxAllowedPacket
    } else {
        // Get max allowed packet size
        maxap, err := mc.getSystemVar("max_allowed_packet")
        if err != nil {
            mc.Close()
            return nil, err
        }
        mc.maxAllowedPacket = stringToInt(maxap) - 1
    }
    if mc.maxAllowedPacket < maxPacketSize {
        mc.maxWriteSize = mc.maxAllowedPacket
    }
    ...
    ...
}

發(fā)現(xiàn)定硝，創(chuàng)建mysqlConn對(duì)象時(shí)會(huì)默認(rèn)將MaxAllowedPacket參數(shù)設(shè)置為4M，如果傳入了參數(shù)配置會(huì)更新為用戶(hù)配置的參數(shù)毫目，如果用戶(hù)沒(méi)有配置該參數(shù)則會(huì)利用getSystemVar方法獲取mysql的配置參數(shù)進(jìn)行設(shè)置蔬啡。為了驗(yàn)證我們的推斷，在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)我們指定了該參數(shù)：

    var err error
    var timeZone = "Asia%2FShanghai"
    var dsn string

    dsn = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=%s&maxAllowedPacket=%d","root", "12345678", "localhost", 3306, "test", timeZone,100<<20)
    
    db, err := gorm.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

再次嘗試執(zhí)行發(fā)現(xiàn)執(zhí)行成功蒜茴，說(shuō)明go-mysql-driver中的參數(shù)maxAllowedPacket被成功設(shè)置為了100M星爪。問(wèn)題雖然圓滿(mǎn)解決，但是我們卻發(fā)現(xiàn)了另一個(gè)問(wèn)題：

按照go-mysql-driver/connector.go中的邏輯：

if mc.cfg.MaxAllowedPacket > 0 {
        mc.maxAllowedPacket = mc.cfg.MaxAllowedPacket
    } else {
        // Get max allowed packet size
        maxap, err := mc.getSystemVar("max_allowed_packet")
        if err != nil {
            mc.Close()
            return nil, err
        }
        mc.maxAllowedPacket = stringToInt(maxap) - 1
    }
    ...
    ...
}

如果maxAllowedPacket參數(shù)沒(méi)有手動(dòng)配置粉私，應(yīng)該會(huì)通過(guò)方法getSystemVar獲取mysql的參數(shù)配置進(jìn)行填充才對(duì)，然而事實(shí)卻是近零，當(dāng)我們沒(méi)有指定該參數(shù)是诺核，以系統(tǒng)默認(rèn)的defaultMaxAllowedPacket參數(shù)進(jìn)行填充。

繼續(xù)分析我們發(fā)現(xiàn)當(dāng)我們不設(shè)置該參數(shù)時(shí)久信，由于mc.cfg.MaxAllowedPacket > 0上述邏輯執(zhí)行到了mc.maxAllowedPacket = mc.cfg.MaxAllowedPacket窖杀。查看mc.cfg的結(jié)構(gòu)如下：

type Config struct {
    User             string            // Username
    Passwd           string            // Password (requires User)
    Net              string            // Network type
    Addr             string            // Network address (requires Net)
    DBName           string            // Database name
    Params           map[string]string // Connection parameters
    Collation        string            // Connection collation
    Loc              *time.Location    // Location for time.Time values
    MaxAllowedPacket int               // Max packet size allowed
    ServerPubKey     string            // Server public key name
    pubKey           *rsa.PublicKey    // Server public key
    TLSConfig        string            // TLS configuration name
    tls              *tls.Config       // TLS configuration
    Timeout          time.Duration     // Dial timeout
    ReadTimeout      time.Duration     // I/O read timeout
    WriteTimeout     time.Duration     // I/O write timeout

    AllowAllFiles           bool // Allow all files to be used with LOAD DATA LOCAL INFILE
    AllowCleartextPasswords bool // Allows the cleartext client side plugin
    AllowNativePasswords    bool // Allows the native password authentication method
    AllowOldPasswords       bool // Allows the old insecure password method
    CheckConnLiveness       bool // Check connections for liveness before using them
    ClientFoundRows         bool // Return number of matching rows instead of rows changed
    ColumnsWithAlias        bool // Prepend table alias to column names
    InterpolateParams       bool // Interpolate placeholders into query string
    MultiStatements         bool // Allow multiple statements in one query
    ParseTime               bool // Parse time values to time.Time
    RejectReadOnly          bool // Reject read-only connections
}

// NewConfig creates a new Config and sets default values.
func NewConfig() *Config {
    return &Config{
        Collation:            defaultCollation,
        Loc:                  time.UTC,
        MaxAllowedPacket:     defaultMaxAllowedPacket,
        AllowNativePasswords: true,
        CheckConnLiveness:    true,
    }
}

mysqlConn.config在創(chuàng)建時(shí)，參數(shù)MaxAllowedPacket被默認(rèn)填充為defaultMaxAllowedPacket裙士。此外有沒(méi)有任何重置該參數(shù)的接口入客，因此理論上來(lái)說(shuō)，getSystemVar是不可能被執(zhí)行到的腿椎，那該方法存在的意義是什么呢桌硫？go-mysql-driver作為一個(gè)成熟使用的數(shù)據(jù)庫(kù)驅(qū)動(dòng)，應(yīng)該不會(huì)出現(xiàn)這個(gè)明顯的錯(cuò)誤問(wèn)題啃炸，我們?cè)俅畏治?code>go-mysql-driver/connector.go中的邏輯發(fā)現(xiàn)铆隘，如果希望驅(qū)動(dòng)程序?qū)axAllowedPacket設(shè)置為mysql的配置參數(shù)，需要滿(mǎn)足條件mc.cfg.MaxAllowedPacket > 0南用，因此膀钠，只要我們?cè)趧?chuàng)建數(shù)據(jù)庫(kù)連接時(shí)掏湾，將maxAllowedPacket參數(shù)配置為<= 0的情況即可。

    var err error
    var timeZone = "Asia%2FShanghai"
    var dsn string

    dsn = fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=%s&maxAllowedPacket=%d","root", "12345678", "localhost", 3306, "test", timeZone,0)
    
    db, err := gorm.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }

至此程序執(zhí)行成功肿嘲，問(wèn)題完美解決融击，打印相應(yīng)的調(diào)試信息發(fā)現(xiàn)：get mysql max_allowed_packet=104857599程序成功獲取的mysql的參數(shù)配置，并將參數(shù) max_allowed_packet設(shè)置為100M雳窟。

3尊浪、拓展

在發(fā)現(xiàn)該問(wèn)題之后，在網(wǎng)上搜索了大量的信息涩拙，發(fā)現(xiàn)很多解決方案并不能解決自己的問(wèn)題际长，一方面是因?yàn)樗阉鲿r(shí)不知道問(wèn)題的原因，導(dǎo)致搜索時(shí)不能描述的特別具體兴泥，另一方面工育，一些小眾的問(wèn)題，很難找到標(biāo)準(zhǔn)的答案搓彻，需要自己去閱讀源碼解決如绸。但是最重要的原因在于自己不清楚go-mysql-driver這個(gè)包的用法，如果直接上網(wǎng)查詢(xún)gorm的使用方法或者入門(mén)資料旭贬，大多在創(chuàng)建連接時(shí)不會(huì)去認(rèn)為配置mysql驅(qū)動(dòng)的一些不常用參數(shù)怔接，從而導(dǎo)致我們用了很多次第三方的庫(kù)，但是根本不清楚這個(gè)庫(kù)的全部用法稀轨。其實(shí)系統(tǒng)學(xué)習(xí)這個(gè)庫(kù)的最好方法就是閱讀源碼扼脐，但同時(shí)這也是比較費(fèi)時(shí)的方法。相對(duì)于查找入門(mén)資料和閱讀源碼的折中方法就是去看該包的測(cè)試用例奋刽，因?yàn)橹挥凶髡咦钋宄@個(gè)包提供的全部功能瓦侮。我們以go-mysql-driver驅(qū)動(dòng)為例，通過(guò)查看github.com/go-sql-driver/mysql/dsn_test.go可以發(fā)現(xiàn)一些創(chuàng)建連接的測(cè)試用例：

var testDSNs = []struct {
    in  string
    out *Config
}{{
    "username:password@protocol(address)/dbname?param=value",
    &Config{User: "username", Passwd: "password", Net: "protocol", Addr: "address", DBName: "dbname", Params: map[string]string{"param": "value"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "username:password@protocol(address)/dbname?param=value&columnsWithAlias=true",
    &Config{User: "username", Passwd: "password", Net: "protocol", Addr: "address", DBName: "dbname", Params: map[string]string{"param": "value"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true, ColumnsWithAlias: true},
}, {
    "username:password@protocol(address)/dbname?param=value&columnsWithAlias=true&multiStatements=true",
    &Config{User: "username", Passwd: "password", Net: "protocol", Addr: "address", DBName: "dbname", Params: map[string]string{"param": "value"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true, ColumnsWithAlias: true, MultiStatements: true},
}, {
    "user@unix(/path/to/socket)/dbname?charset=utf8",
    &Config{User: "user", Net: "unix", Addr: "/path/to/socket", DBName: "dbname", Params: map[string]string{"charset": "utf8"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "user:password@tcp(localhost:5555)/dbname?charset=utf8&tls=true",
    &Config{User: "user", Passwd: "password", Net: "tcp", Addr: "localhost:5555", DBName: "dbname", Params: map[string]string{"charset": "utf8"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true, TLSConfig: "true"},
}, {
    "user:password@tcp(localhost:5555)/dbname?charset=utf8mb4,utf8&tls=skip-verify",
    &Config{User: "user", Passwd: "password", Net: "tcp", Addr: "localhost:5555", DBName: "dbname", Params: map[string]string{"charset": "utf8mb4,utf8"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true, TLSConfig: "skip-verify"},
}, {
    "user:password@/dbname?loc=UTC&timeout=30s&readTimeout=1s&writeTimeout=1s&allowAllFiles=1&clientFoundRows=true&allowOldPasswords=TRUE&collation=utf8mb4_unicode_ci&maxAllowedPacket=16777216&tls=false&allowCleartextPasswords=true&parseTime=true&rejectReadOnly=true",
    &Config{User: "user", Passwd: "password", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "dbname", Collation: "utf8mb4_unicode_ci", Loc: time.UTC, TLSConfig: "false", AllowCleartextPasswords: true, AllowNativePasswords: true, Timeout: 30 * time.Second, ReadTimeout: time.Second, WriteTimeout: time.Second, AllowAllFiles: true, AllowOldPasswords: true, CheckConnLiveness: true, ClientFoundRows: true, MaxAllowedPacket: 16777216, ParseTime: true, RejectReadOnly: true},
}, {
    "user:password@/dbname?allowNativePasswords=false&checkConnLiveness=false&maxAllowedPacket=0",
    &Config{User: "user", Passwd: "password", Net: "tcp", Addr: "127.0.0.1:3306", DBName: "dbname", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: 0, AllowNativePasswords: false, CheckConnLiveness: false},
}, {
    "user:p@ss(word)@tcp([de:ad:be:ef::ca:fe]:80)/dbname?loc=Local",
    &Config{User: "user", Passwd: "p@ss(word)", Net: "tcp", Addr: "[de:ad:be:ef::ca:fe]:80", DBName: "dbname", Collation: "utf8mb4_general_ci", Loc: time.Local, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "/dbname",
    &Config{Net: "tcp", Addr: "127.0.0.1:3306", DBName: "dbname", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "@/",
    &Config{Net: "tcp", Addr: "127.0.0.1:3306", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "/",
    &Config{Net: "tcp", Addr: "127.0.0.1:3306", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "",
    &Config{Net: "tcp", Addr: "127.0.0.1:3306", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "user:p@/ssword@/",
    &Config{User: "user", Passwd: "p@/ssword", Net: "tcp", Addr: "127.0.0.1:3306", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "unix/?arg=%2Fsome%2Fpath.ext",
    &Config{Net: "unix", Addr: "/tmp/mysql.sock", Params: map[string]string{"arg": "/some/path.ext"}, Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "tcp(127.0.0.1)/dbname",
    &Config{Net: "tcp", Addr: "127.0.0.1:3306", DBName: "dbname", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
}, {
    "tcp(de:ad:be:ef::ca:fe)/dbname",
    &Config{Net: "tcp", Addr: "[de:ad:be:ef::ca:fe]:3306", DBName: "dbname", Collation: "utf8mb4_general_ci", Loc: time.UTC, MaxAllowedPacket: defaultMaxAllowedPacket, AllowNativePasswords: true, CheckConnLiveness: true},
},
}

通過(guò)這些測(cè)試用例佣谐，我們發(fā)現(xiàn)了很多平時(shí)使用不會(huì)注意的參數(shù)配置方式肚吏，當(dāng)然我們不需要去逐個(gè)解釋每個(gè)參數(shù)的作用，在需要的時(shí)候去查看指定部分的用法就可以啦狭魂。

以上就是本偏文章的全部?jī)?nèi)容罚攀。