mycat下mysql jdbc connector使用高版本报PacketTooBigException异常
如下所示:
5.1.30切换为mysql-connector 5.1.46/40,报错,可参考https://blog.csdn.net/n447194252/article/details/75304303。
Tue Jun 26 14:23:20 CST 2018 WARN: Invalid value 'null' for server variable named 'auto_increment_increment', falling back to sane default of '1'.
14:23:20.773 [Druid-ConnectionPool-Create-932336632] ERROR com.alibaba.druid.pool.DruidDataSource - create connection holder error
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (44 > -1). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:578) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:418) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3115) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2344) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2739) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3001) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:347) ~[druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.filter.FilterAdapter.connection_getTransactionIsolation(FilterAdapter.java:871) ~[druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:344) ~[druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.getTransactionIsolation(ConnectionProxyImpl.java:260) ~[druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.pool.DruidConnectionHolder.<init>(DruidConnectionHolder.java:98) ~[druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.pool.DruidDataSource.put(DruidDataSource.java:1721) [druid-1.0.14.jar:1.0.14]
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:1890) [druid-1.0.14.jar:1.0.14]
当目标表不存在的时候会出现上述异常,且进入死循环,日志狂刷。官方解释:在使用mycat1.3和mycat1.4版本情况下,不要使用jdbc5.1.37和38版本的驱动,会出现如下异常报错:com.mysql.jdbc.PacketTooBigException: Packet for query is too large (60 > -1). You can change this value on the server by setting the max_allowed_packet' variable。建议使用jdbc5.1.35或者36的版本。
实际上可以认为开发者没有找到原因,是测试出来的。
回到5.1.30之后,正常。会提示正常的错误信息,如下:
如果后台报错,也会返回如下信息,包括SQL state [HY000]; error code [1105],但是如果是后台数据库报错,则会返回具体的错误号
A.D_OUTPUTDATE = 0 THEN ' ' ELSE TO_CHAR(A.D_OUTPUTDATE) END) D_OUTPUTDATE , NVL(B.C_CUSTNAME, ' ') C_CUSTNAME , NVL(B.C_IDENTITYPE, ' ') C_IDENTITYPE , NVL(B.C_IDENTITYNO, ' ') C_IDENTITYNO , (CASE WHEN A.D_REQOUTPUTDATE = 0 THEN ' ' ELSE TO_CHAR(A.D_REQOUTPUTDATE) END) D_REQOUTPUTDATE , A.C_FORCEREDEMPTIONTYPE , A.C_MEMO , A.F_REQRDMBALANCE , A.F_PROTECTBALANCE FROM ta_tconfirm_his A LEFT JOIN ta_taccoinfo B ON A.C_FUNDACCO = B.C_FUNDACCO AND A.C_TENANTID = B.C_TENANTID LEFT JOIN ta_tagencyinfo C ON A.C_AGENCYNO = C.C_AGENCYNO AND A.C_TENANTID = C.C_TENANTID LEFT JOIN ta_tbusinflag D ON A.C_BUSINFLAG = D.C_BUSINFLAG AND A.C_TENANTID = D.C_TENANTID LEFT JOIN ta_terrormess E ON A.C_CAUSE = E.C_CAUSE AND A.C_TENANTID = E.C_TENANTID LEFT JOIN ta_tfundinfo F ON A.C_FUNDCODE = F.C_FUNDCODE AND A.C_TENANTID = F.C_TENANTID LEFT JOIN ta_tnetinfo H ON A.C_NETNO = H.C_NETNO AND A.C_AGENCYNO=H.C_AGENCYNO AND A.C_TENANTID = H.C_TENANTID WHERE A.C_TENANTID = ? AND A.c_tacode = ? AND A.D_CDATE >= ? AND A.D_CDATE <= ? AND A.C_FUNDCODE IN ( SELECT fundcode FROM role_fundcode WHERE role_id IN ( ? ) AND fundcode IN(SELECT c_fundcode FROM ta_tfundinfo) UNION ALL SELECT ' ' AS fundcode FROM dual UNION ALL SELECT '*' AS fundcode FROM dual ) ) C
### Cause: java.sql.SQLException: ORA-00942: ????????????
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1105]; ORA-00942: ????????????
; nested exception is java.sql.SQLException: ORA-00942: ????????????
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) ~[spring-jdbc-3.2.14.RELEASE.jar:3.2.14.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.14.RELEASE.jar:3.2.14.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.14.RELEASE.jar:3.2.14.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~[mybatis-spring-1.2.3.jar:1.2.3]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399) ~[mybatis-spring-1.2.3.jar:1.2.3]
at com.sun.proxy.$Proxy40.selectOne(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:165) ~[mybatis-spring-1.2.3.jar:1.2.3]
at net.hs.cw.bomp.engine.service.JdbcServiceEngine.selectPageCount(JdbcServiceEngine.java:498) ~[bomp-runtime-1.0.0-SNAPSHOT.jar:?]
at net.hs.cw.bomp.engine.service.JdbcServiceEngine.callQueryService(JdbcServiceEngine.java:460) ~[bomp-runtime-1.0.0-SNAPSHOT.jar:?]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_45]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_45]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_45]
at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_45]
mycat中对应的错误信息如下:
06/26 14:45:19.609 WARN [BusinessExecutor1] (MultiNodeHandler.java:126) -error response from JDBCConnection [id=68,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@1dbc0e73, schema=hs_tatrade2, dbType=ORACLE, oldSchema=hs_tatrade2, packetId=-55, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CConnection@12645708, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1529975130206, lastTime=1529995519607, isSpark=false, processor=org.opencloudb.net.NIOProcessor@2558af45] err ORA-00942: 表或视图不存在
mysql 8.0已经不支持5.x系列的jdbc connector了,目前测试下来,还是推荐大家使用sharding jdbc 2.x。
补充:
升级到mycat 1.6.5版本之后,上述问题就没有了,所以用mycat的可以考虑。去哪儿的数据库负责人最近写了一篇驳斥mycat定位的文档http://www.sohu.com/a/258761170_411876,大家可以看看。
相关文章
- MySQL触发器
- 【MySQL从入门到精通】【高级篇】(十)MyISAM的索引方案&&索引的优缺点
- 图解MySQL系列(2)-SQL实战研究InnoDB架构设计
- mysql 插入汉字 异常 Incorrect string value: 'xE8xA7x84xE5x88x99' for column 'name'
- mysql-异常
- 部署tidb同步到mysql(drainer)
- mysql异常处理
- mysql的全局锁和表锁 flush tables write read lock ;set global readonly=true; lock tables ;unlock tables,
- mysql json字段查询 属性
- mysql显示行号
- Mysql:5.5-5.6:Components & Plugins
- 记录一次mysql由5.6升级到5.7出现的异常---Expression #23 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'c.commentCount' which is not functionally dependent on columns in GROUP BY clause;
- java Date时间的各种转换方式和Mysql存时间类型字段的分析
- MySQL数据库的数据类型详解【转】
- 2023-01-15 mysql列存储引擎-列的读取及pack的使用方-分析
- 2022-11-17 mysql列存储引擎-聚合中间缓存结果-分析
- 2022-10-30 mysql列存储引擎-自定义函数-return返回select语句结果异常-问题定位
- 2022-09-09 mysql列存储引擎-tianmu_ini_allowmysqlquerypath参数开启后的异常记录
- MySQL day5 日志管理
- Mysql基本操作
- Mysql存储过程(四)——异常处理
- mysql java连接异常及断开解决秘籍
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- linux下如何实现mysql数据库每天自动备份定时备份
- mysql遇见contains nonaggregated column 'information_schema.PROFILING.SEQ'异常