PacketTooBigException异常捕获及解决方法

一、背景

在连接数据库过程中，爆出如下错误。
原因是：提交的sql信息超过了设置max_allowed_packet参数太小。
举个例子：往数据库中插入text文本，数据库最大允许提交16M的sql,结果你提交了32M；
或者执行的sql条数太多；

org.springframework.dao.TransientDataAccessResourceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (6,250,262 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
### The error may involve com.example.demo.mapper.LoveMapper.insertSelective-Inline
### The error occurred while setting parameters
### SQL: insert into love          ( status_id,                                           like_user,                                           version )           values ( ?,                                           ?,                                           ? )
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (6,250,262 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (6,250,262 > 4,194,304). 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 (6,250,262 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
	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.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy79.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:279)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:56)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
	at com.sun.proxy.$Proxy80.insertSelective(Unknown Source)
	at com.example.demo.service.impl.LikeServiceImpl.insertSelective(LikeServiceImpl.java:99)
	at com.example.demo.pullThread.ReceiveThread.insertLike(ReceiveThread.java:127)
	at com.example.demo.pullThread.ReceiveThread.run(ReceiveThread.java:46)
	at java.lang.Thread.run(Thread.java:748)

二、捕获异常

如果你不想修改mysql的配置文件，只是想捕获这种异常，那该怎么办呢？

try{
// insert 
} catch(PacketTooBigException e){
//todo
}

结果报错，当前包根本引用不到PacketTooBigException，那又该怎么办呢？

1.捕获TransientDataAccessResourceException

捕获TransientDataAccessResourceException 代替PacketTooBigException

try{
// insert 
} catch(TransientDataAccessResourceException e){
//todo
}

2.捕获PacketTooBigException

打死也要捕获PacketTooBigException
想法来源

try{
// insert 
}catch (Exception e) {
    if(e.getCause().getClass().getName().equals("com.mysql.cj.jdbc.exceptions.PacketTooBigException")){
                log.info("老子终于捕获到PacketTooBigException了");
            }
    }

三、解决异常

参考：MySQL之PacketTooBigException解决

1.方法一：修改配置文件

正如报错提示那样，改my.ini配置，默认是1M，该大点咯。

2. 方法二：执行命令修改：

进入mysql server
在mysql 命令行中运行
set global max_allowed_packet = 16777216
然后关闭掉这此mysql server链接，再进入。
show VARIABLES like ‘%max_allowed_packet%’;
查看下max_allowed_packet是否编辑成功

注意：
这种情况虽然不需要重启MySQL服务，但是如果某一天MySQL需要重启，该设置就会恢复到原来的设置1MB