Hidden Max_allowd_packet parameter _ MySQL

The hidden Max_allowd_packet parameter bitsCN.com

Background:

Today I saw a bug: http://bugs.mysql.com/bug.php? Id = 67448

After global & session max_allowed_packet is configured, the client still reports an ERROR such as ERROR 2020 (HY000): Got packet bigger than 'max _ allowed_packet 'bytes.

I think it is different from my previous understanding. so I did a specific experiment and recorded it as this blog.

1. prepare the environment

Generate a 32 m file and import it to a field in the table.

#! /bin/shi=0;while [ $i -lt 33554432 ]doecho -ne 'a' >> /tmp/longblob.txtlet i=$i+1done

CREATE TABLE `longblobtest` ( `content` longblob ) ENGINE=InnoDB DEFAULT CHARSET=utf8;load data infile '/tmp/longblob.txt' into table longblobtest;

2. run the test.

First, make sure that the current max_allowed_packet is longer than the length of a single field.

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet, length(content) from longblobtest;+------------------------------+-----------------------------+-----------------+| @@session.max_allowed_packet | @@global.max_allowed_packet | length(content) |+------------------------------+-----------------------------+-----------------+|                     43553792 |                    43553792 |        33554432 |+------------------------------+-----------------------------+-----------------+1 row in set (0.04 sec)

Run SQL: the following result is displayed. an error is returned!

mysql> select content from longblobtest;ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

3. Analysis

From the above results, we can see that max_allowed_packet at the Session level can be inherited from global.

However, the value does not affect the result obtaining of CLI. That is, if the configuration of max_allowed_packet in global is large, mysql in CLI still reports an error.

According to the bug description, we know that,Each mysql-client has an internal max_allowed_packet variable.Is an important parameter that affects the result set acquisition.

This parameter is transparent to the outside, and the current value of this variable cannot be viewed from any place!

Fortunately, mysql-cli provides the entry for configuring this variable. That isWhen calling the mysql command line, add the -- max-allowed-packet parameter

4. verify again

Add parameters when using mysql command line

shell> mysql --max-allowed-packet=43553792 -uroot -p -S /usr/local/mysql3310/mysql.sock 

Execute SQL. pager is configured to make the result set easier to copy. Does not affect the size of the returned result set

mysql> pager wc -cPAGER set to 'wc -c'mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select content from longblobtest;1342187771 row in set (0.26 sec)

BitsCN.com