max_allowed_packet
程序运行错误提示:
org.springframework.dao.TransientDataAccessResourceException:
### Error updating database. Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1482 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
### The error may involve com.rlglsys.mapper.IRlgl010106Mapper.updateUnit-Inline
### The error occurred while setting parameters
### SQL: UPDATE m_tb04_unit SET unit_own =?, earnings_nature =? , unit_manage_scale =? , unit_manage_scale_tow=?, unit_manage_scale_nm=?, unit_economic_type =?, unit_nature =?, unit_nature_tow =?, unit_level =? , industry_plan =?, agency_level =?, agency_level_tow =? , agency_level_three =? , unit_scale =?, license_no =?, establishment_num =?, organization_no =? , organization_code =? , unit_address =?, offical_scope =?, unit_pro =? , hospital_grade =? , hospital_grade_tow =? , unit_img =? , area_id =? , unit_lower =? , system_sort =? , industry_divide =? , system_sort_two =? , industry_divide_two =? , agency_plan =? , ex_key = ?, del_kbn = ?, expend_01 = ?, expend_02 = ?, expend_03 = ?, expend_04 = ?, expend_05 = ?, login_user_id = ?, login_date = ?, update_user_id = ?, update_date = ? WHERE unit_no = ?;
### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1482 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
; SQL []; Packet for query is too large (1482 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1482 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
解决方案:
在linux命令栏中:输入:whereis mysql
可以显示mysql的安装目录: /usr/share/mysql
1.找到 【my-default.cnf】文件:把max_allowed_packet = 20M 的值调大些即可。
2.在etc/my.cnf文件中,找到max_allowed_packet设置,默认是1MB,我们可以修改为10MB或者20MB,随意。然后重启网站环境之后再导入数据库命令执行,就可以完全的执行导入数据库。
重新启动mysql:service mysql restart
通过 ps -el | grep mysqld 查看mysql的启动情况。
通过此语句查看mysql中max_allowed_packet的大小:
show VARIABLES like '%max_allowed_packet%';
在linux下会发现上述文件可能都不存在。
1)先确定出使用的配置文件的路径(如果未启动,可先启动)
[root@localhost 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/mysql --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
找见mysqld或mysqld_safe的那一行,看下basedir=/path/file ,那个/path/file就是配置文件路径;
2)也可以直接创建 /etc/my.cnf, 或者从你安装的mysql的相关目录中(可能是/usr/include/mysql或/usr/share/mysql)找一个my.cnf 或 my-small.cnf 拷贝为/etc/my.cnf,mysql启动时会优先使用这个配置文件。
可以用如下命令在/etc目录下查找my.cnf类似的文件名:
mysql --help | grep my.cnf 或者
[root@localhost 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)有了配置文件,在配置文件中的[mysqld]下边加些常用的配置参数。重启mysql服务器后,该参数即可生效。
max_allowed_packet=32M