mysql packet for query is too large问题排查

小嘴叭叭儿
218 阅读6分钟

本文通过一次mysql packet for query is too large问题的排查经历，从go-mysql-driver源码层面解析问题出现的原因及解决方案，进而引出一系列你所不知道的go-mysql-driver操作技巧。

1、背景介绍

在某次执行大批量的数据插入时，为了提高插入的效率，将逐条插入的sql语句进行聚合，直接执行聚合之后的sql语句，实现数据的批量插入，但是当数据量很大时，会返回如下错误packet for query is too large. Try adjusting the 'max_allowed_packet' variable on the server。

通过查证，在网上发现了如下解决方案：

//查询mysql的max_allowed_packet参数
show VARIABLES like '%max_allowed_packet%';
//重置该参数值为100M
set global max_allowed_packet = 1024*1024*100;

重置之后再次执行sql的执行，依然返回packet for query is too large的错误信息，而我提供的sql长度为91M左右，起初以为是set global max_allowed_packe设置的临时参数，没有生效，于是修改/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服务。依然无法解决该问题。

为了确定不是mysql参数的问题，直接执行 mysql -uXXX -pXXX -DXXX < insert.sql，发现数据插入成功，说明mysql确实没有问题，那么问题只能存在于gorm或者go-mysql-driver中。

2、问题排查

在确定了问题出在gorm中之后，使用debug模式查找具体问题，发现问题发生在go-mysql-driver的packets.go中。

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

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

说明mysql的确驱动对sql的长度进行了限制。继续分析我们发现在go-mysql-driver/const.go中默认将MaxAllowedPacket参数限制在了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"
)

继续分析可以发现在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
	}
	...
	...
}

发现，创建mysqlConn对象时会默认将MaxAllowedPacket参数设置为4M，如果传入了参数配置会更新为用户配置的参数，如果用户没有配置该参数则会利用getSystemVar方法获取mysql的配置参数进行设置。为了验证我们的推断，在创建数据库时我们指定了该参数：

    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
	}

再次尝试执行发现执行成功，说明go-mysql-driver中的参数maxAllowedPacket被成功设置为了100M。问题虽然圆满解决，但是我们却发现了另一个问题：

按照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参数没有手动配置，应该会通过方法getSystemVar获取mysql的参数配置进行填充才对，然而事实却是，当我们没有指定该参数是，以系统默认的defaultMaxAllowedPacket参数进行填充。

继续分析我们发现当我们不设置该参数时，由于mc.cfg.MaxAllowedPacket > 0上述逻辑执行到了mc.maxAllowedPacket = mc.cfg.MaxAllowedPacket。查看mc.cfg的结构如下：

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在创建时，参数MaxAllowedPacket被默认填充为defaultMaxAllowedPacket。此外有没有任何重置该参数的接口，因此理论上来说，getSystemVar是不可能被执行到的，那该方法存在的意义是什么呢？go-mysql-driver作为一个成熟使用的数据库驱动，应该不会出现这个明显的错误问题，我们再次分析go-mysql-driver/connector.go中的逻辑发现，如果希望驱动程序将MaxAllowedPacket设置为mysql的配置参数，需要满足条件mc.cfg.MaxAllowedPacket > 0，因此，只要我们在创建数据库连接时，将maxAllowedPacket参数配置为<= 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
	}

至此程序执行成功，问题完美解决，打印相应的调试信息发现：get mysql max_allowed_packet=104857599程序成功获取的mysql的参数配置，并将参数 max_allowed_packet设置为100M。

3、拓展

在发现该问题之后，在网上搜索了大量的信息，发现很多解决方案并不能解决自己的问题，一方面是因为搜索时不知道问题的原因，导致搜索时不能描述的特别具体，另一方面，一些小众的问题，很难找到标准的答案，需要自己去阅读源码解决。但是最重要的原因在于自己不清楚go-mysql-driver这个包的用法，如果直接上网查询gorm的使用方法或者入门资料，大多在创建连接时不会去认为配置mysql驱动的一些不常用参数，从而导致我们用了很多次第三方的库，但是根本不清楚这个库的全部用法。其实系统学习这个库的最好方法就是阅读源码，但同时这也是比较费时的方法。相对于查找入门资料和阅读源码的折中方法就是去看该包的测试用例，因为只有作者最清楚这个包提供的全部功能。我们以go-mysql-driver驱动为例，通过查看github.com/go-sql-driver/mysql/dsn_test.go可以发现一些创建连接的测试用例：

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},
},
}

通过这些测试用例，我们发现了很多平时使用不会注意的参数配置方式，当然我们不需要去逐个解释每个参数的作用，在需要的时候去查看指定部分的用法就可以啦。

以上就是本篇文章的全部内容。

avatar
文章
阅读
粉丝