全表删除失败导致数据量过大，链接超时后全部占用后台崩溃

目录

问题排查  

问题定位 

问题解决

另一种解决方案（未验证）

---

问题排查  

前端登录失败，查看后台报错：某个后台接口查询报错，获取不到数据库链接，但是数据库可以正常连接上

### The error may exist in URL [jar:file:/app.jar!/BOOT-INF/lib/test-1.0.0.jar!/mapper/business/testMapper.xml]

 ### The error may involve com.tunnel.business.mapper.test.insertData

 ### The error occurred while executing an update

 ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 20, maxActive 20, creating 0, runningSqlCount 5 : SELECT * from test

 重点：wait millis 60000, active 20, maxActive 20, creating 0

活跃链接数20，最大就20，没办法新增链接，说明链接数全被占用。

针对报错代码位置，继续分析：发现单表数据达到了2个亿，应该是历史数据未成功清除。

---

问题定位 

清除历史数据定时任务报错：Prohibition of full table deletion

 ### Error updating database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Prohibition of full table deletion ### The error may exist in URL [jar:file:/project/test.jar!/BOOT-INF/lib/test-1.0.0.jar!/mapper/business/testMapper.xml] ### The error may involve com.tunnel.business.mapper.test.delete ### The error occurred while executing an update ### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Prohibition of full table deletion at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy123.delete(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:304) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:70) at com.baomidou.mybatisplus

问题解决

 将删除语句加上时间限制，比如删除前一天的数据：

 delete from test where create_time < date_sub(sysdate(),interval 1 day)

暂时运行几天观察看看。

---

另一种解决方案（未验证）

另一种办法，未实际验证：mapper方法上添加注解@InterceptorIgnore(blockAttack = "true")

参考链接：

Prohibition of full table deletion 解决方法_彬彬有礼的彬彬同学的博客-CSDN博客

mybatis-plus3.4x 多租户屏蔽某个特定mapper的方法@InterceptorIgnore_是潮汕的灿灿展吖的博客-CSDN博客