Http://www.2cto.com/database/201303/195830.html
MySQL restricts the size of packets accepted by the server according to the configuration file. Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, resulting in failure. View current configuration show VARIABLES like '%max_allowed_packet% '; the result is: +--------------------+---------+| variable_name | Value |+--------------------+---------+| Max_allowed_packet | 1048576 |+--------------------+---------+ above instructions The current configuration is: 1M Modify Method One, Method 1 can edit my.cnf to modify (under Windows My.ini), in [Mysqld] section or MySQL server configuration section for modifications. Max_allowed_packet = 20M If no my.cnf can be found via MySQL--help | grep my.cnf to find the my.cnf file.
[[email protected] usr]# MySQL--help | grep my.cnf
Order of preference, MY.CNF, $MYSQL _tcp_port,
/ETC/MY.CNF/ETC/MYSQL/MY.CNF/USR/ETC/MY.CNF ~/.my.cnf
The above files may be found to be nonexistent under Linux.
1) First determine the path of the configuration file used (if it is not started, it can be started first)
[[email protected] usr]# PS aux |grep mysql
root 14688 0.0 0.0 11336 1404 pts/0 S 19:07 0:00/bin/sh/usr/bin/mysqld_safe--datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain138.pid
MySQL 14791 0.0 15.4 1076700 451336 pts/0 Sl 19:07 0:00/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/my SQL--plugin-dir=/usr/lib64/mysql/plugin--user=mysql--log-error=/var/lib/mysql/localhost.localdomain138.err-- Pid-file=/var/lib/mysql/localhost.localdomain138.pid
root 14835 0.0 0.0 201584 2504 pts/0 s+ 19:09 0:00 mysql-u root-p
root 15143 0.0 0.0 103244 828 pts/1 s+ 19:40 0:00 grep mysql
Find mysqld or mysqld_safe that line, look at Basedir=/path/file, that/path/file is the configuration file path;
2) You can also create/etc/my.cnf directly or from the relevant directory of your installed MySQL (possibly/usr/include/mysql or/usr/share/mysql) to find a my.cnf or my-small.cnf copy for/ This configuration file is preferred when Etc/my.cnf,mysql is started.
You can use the following command to find my.cnf similar file names in the/etc directory:
[email protected] usr]# find-name "MY*.CNF"
./MY.CNF
./SHARE/MYSQL/MY-DEFAULT.CNF
./SHARE/DOC/MYSQL-SERVER-5.6.16/MY-DEFAULT.CNF
./MY-NEW.CNF
3) With the configuration file, add some common configuration parameters under [Mysqld] in the configuration file. This parameter will take effect when the MySQL server is restarted.
max_allowed_packet=32m
Ii. Method 2 (very compromised, very tangled approach) into MySQL serverrun in the MySQL command lineSet Global Max_allowed_packet = 2*1024*1024*10quit the MySQL command line, and then log back on. show VARIABLES like '%max_allowed_packet% ';See if the next Max_allowed_packet is edited successfully
Note: In method 2, if the MySQL service is restarted, the value of Max_allowed_packet is restored to the default initial value, and the value set on the command line does not take effect.
Other references:
Http://zhidao.baidu.com/link?url=nIy0O1xj1kJWuvdKi8Aeo1UcHRtCx6EtrFFbkCwIsduE1mwVNWXpLUKf_izyyhd3fu7Hknp5bG1lbCuiG8s-tK
MySQL max_allowed_packet Query and modification
