Mysql数据库报错：You can change this value on the server by setting the ‘max_allowed_packet‘ variable

今天出现一个mysql插入数据异常如下：

org.springframework.dao.TransientDataAccessResourceException: jOOQ; SQL [update `knowledge`.`document` set `knowledge`.`document`.`location` = ?, `knowledge`.`document`.`file_type` = ?, `knowledge`.`document`.`parent` = ?, `knowledge`.`document`.`size` = ?, `knowledge`.`document`.`word_count` = ?, `knowledge`.`document`.`words` = ?, `knowledge`.`document`.`append_time` = ?, `knowledge`.`document`.`append_user` = ?, `knowledge`.`document`.`content` = ?, `knowledge`.`document`.`other` = ? where `knowledge`.`document`.`document_name` = ?]; Packet for query is too large (9,015,984 > 999,424). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (9,015,984 > 999,424). You can change this value on the server by setting the 'max_allowed_packet' variable.
        at org.jooq_3.13.2.MYSQL.debug(Unknown Source)
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.translate(JooqExceptionTranslator.java:92)
        at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:81)
        at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:55)

原来mysql根据配置文件会限制server接受的数据包大小。如果一次插入数据库中的数据太大的话就会失败，用命令行查看max_allowed_packet设置的大小

show VARIABLES like '%max_allowed_packet%'

但实际情况：存入的长度9,015,984 > 999,424所以报错

解决方法

1、修改配置文件（推荐）
在mysql中的my.ini（或my.cnf）文件中修改max_allowed_packet的值500M

max_allowed_packet=524288000

然后重启mysql就可以，重启mysql方式如下：

#先关闭mysql服务
mysqladmin -uroot -p shutdown
#启动mysql服务
service mysqld start

2、命令行中设置

set global max_allowed_packet = 2*1024*1024*10 

然后退出命令行后再登录查看。但是这个方法没什么用，因为重启mysql服务后这个方法就失效了。