ShardingSphere：使用information_schema查询时报错：Table ‘数据库名称.tables‘ doesn‘t exist

---

简介

1、程序jdk1.8升级为jdk21

2、ShardingSphere框架从4.1.1升级为5.2.1

3、使用 information_schema.tables 查询时报错

---

异常信息

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table '数据库名称.tables' doesn't exist
### The error may exist in file [F:\xxx\project\airobot_llm\robotmanagement\source4cloud-jdk21\server\FocusRobotMg-llm\target\classes\mappings\trainplatform\RobotModelVersionMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(DISTINCT 1) FROM information_schema.tables WHERE table_schema = DATABASE() AND TABLE_NAME = 't_model_train_version'
### Cause: java.sql.SQLSyntaxErrorException: Table '数据库名称.tables' doesn't exist
; bad SQL grammar []
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table '数据库名称.tables' doesn't exist
### The error may exist in file [F:\xxx\project\airobot_llm\robotmanagement\source4cloud-jdk21\server\FocusRobotMg-llm\target\classes\mappings\trainplatform\RobotModelVersionMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(DISTINCT 1) FROM information_schema.tables WHERE table_schema = DATABASE() AND TABLE_NAME = 't_model_train_version'
### Cause: java.sql.SQLSyntaxErrorException: Table '数据库名称.tables' doesn't exist
; bad SQL grammar []at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:246)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:347)at jdk.proxy2/jdk.proxy2.$Proxy116.selectOne(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:154)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)at jdk.proxy2/jdk.proxy2.$Proxy368.isExistTable(Unknown Source)at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)at java.base/java.lang.reflect.Method.invoke(Method.java:580)at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)at jdk.proxy2/jdk.proxy2.$Proxy369.isExistTable(Unknown Source)at com.xxx.utils.RedisInitUtil.initReleaseModelVersion(RedisInitUtil.java:432)at com.xxx.utils.RedisInitUtil.setCompanyOriginInf(RedisInitUtil.java:345)at com.xxx.utils.ConstantUtil.initRedis(ConstantUtil.java:91)at com.xxx.utils.ConstantUtil.initialize(ConstantUtil.java:68)at com.xxx.component.InitCommandLineRunner.run(InitCommandLineRunner.java:38)at org.springframework.boot.SpringApplication.lambda$callRunner$5(SpringApplication.java:790)at org.springframework.util.function.ThrowingConsumer$1.acceptWithException(ThrowingConsumer.java:82)at org.springframework.util.function.ThrowingConsumer.accept(ThrowingConsumer.java:60)at org.springframework.util.function.ThrowingConsumer$1.accept(ThrowingConsumer.java:86)at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:798)at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:789)at org.springframework.boot.SpringApplication.lambda$callRunners$3(SpringApplication.java:774)at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357)at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:510)at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:774)at org.springframework.boot.SpringApplication.run(SpringApplication.java:342)at org.springframework.boot.SpringApplication.run(SpringApplication.java:1363)at org.springframework.boot.SpringApplication.run(SpringApplication.java:1352)at com.xxx.RobotManagerSvr.main(RobotManagerSvr.java:36)
Caused by: java.sql.SQLSyntaxErrorException: Table '数据库名称.tables' doesn't existat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:442)at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:438)at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:95)at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:75)at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131)at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116)at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:156)at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:145)at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:403)at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:65)at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:75)at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)at java.base/java.lang.reflect.Method.invoke(Method.java:580)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:333)... 40 common frames omitted

---

SQL

查询表是否存在的sql

SELECT count(DISTINCT 1) FROM information_schema.tables WHERE table_schema = DATABASE() AND TABLE_NAME = 't_model_train_version'

---

排查

ShardingSphere4.1.1时，该SQL是正常的，应该是框架升级导致的

---

原因

ShardingSphere4.1.1时，该SQL是正常的，应该是框架升级导致的

具体原因不明

---

解决

方法一：直接查询表，在代码中通过try/catch来判断是否存在该表

SELECT 1 FROM 表名 LIMIT 1

方法二：使用 SHOW TABLES

SHOW TABLES LIKE 表名