MySQL and max_allowed_packet variable values

Question

I have MySQL Server version 5.1.54 installed on Windows 7 Enterprise and I'm having inconsistent values of variable max_allowed_packet. I have configured the variable in my.ini:

max_allowed_packet=1024M

When I run mysql --help, there is a list of variables and their values at the end of its output. The value of max_allowed_packet is listed as:

max_allowed_packet                16777216

When I run mysqldump --help instead, it lists the value of max_allowed_packet as:

max_allowed_packet                25165824

However when I log in with the mysql executable I get this:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)

So the same server lists three different values for this variable. Both the mysql --help and mysqldump --help list the order of reading their configuration and they are equal.

C:\mysql --help
...
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.1\my.ini C:\Program Files\MySQL\MySQL Server 5.1\my.cnf

C:\mysqldump --help
...
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.1\my.ini C:\Program Files\MySQL\MySQL Server 5.1\my.cnf

Only C:\Program Files\MySQL\MySQL Server 5.1\my.ini exists of the listed configuration files and it indeed contains the 1024M setting.

Any ideas what would explain this behavior?

Answer 1

Please look around C:\Program Files\MySQL\MySQL Server 5.1\my.ini for a group called [mysqldump] and look under it for max_allowed_packet. The setting should be there. If not, create that group and add the max_allowed_packet to the size you want.