Attempting to connect to TiDB using Spring Boot + MyBatis results in the error: Table 'information_schema.KEYWORDS' doesn't exist

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 尝试使用springboot+Mybatis连接tidb报错Table ‘information_schema.KEYWORDS’ doesn’t exist

| username: TiDBer_sa4USb17

[TiDB Usage Environment] Test
[TiDB Version] V7.5.0
[Reproduction Path] The original database used MySQL without any issues. Then, TiDB was installed, and a local test cluster was deployed. The database was migrated using Navicat, and the JDBC path was modified to TiDB’s path (without changing the MySQL driver). An error occurred: Table ‘information_schema.KEYWORDS’ doesn’t exist.
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
16:15:09.650 [main] DEBUG o.s.c.e.PropertySourcesPropertyResolver TID: N/A - [logKeyFound,115] - Found key ‘spring.liveBeansView.mbeanDomain’ in PropertySource ‘systemProperties’ with value of type String
Spring Boot Version: 2.3.12.RELEASE
Spring Application Name: ${spring.application.name}
16:15:11.485 [main] WARN c.a.c.n.c.NacosPropertySourceBuilder TID: N/A - [loadNacosData,87] - Ignore the empty Nacos configuration and get it based on dataId[psf-system-foundation] & group[DEFAULT_GROUP]
16:15:11.489 [main] WARN c.a.c.n.c.NacosPropertySourceBuilder TID: N/A - [loadNacosData,87] - Ignore the empty Nacos configuration and get it based on dataId[psf-system-foundation.yml] & group[DEFAULT_GROUP]
16:15:11.492 [main] WARN c.a.c.n.c.NacosPropertySourceBuilder TID: N/A - [loadNacosData,87] - Ignore the empty Nacos configuration and get it based on dataId[psf-system-foundation-local.yml] & group[DEFAULT_GROUP]
16:15:11.520 [main] INFO c.n.NtFoundationApplication TID: N/A - [logStartupProfileInfo,652] - The following profiles are active: local
16:15:13.675 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [multipleStoresDetected,249] - Multiple Spring Data modules found, entering strict repository configuration mode!
16:15:13.675 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [registerRepositoriesIn,127] - Bootstrapping Spring Data JPA repositories in DEFAULT mode.
16:15:13.845 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [registerRepositoriesIn,187] - Finished Spring Data repository scanning in 161ms. Found 0 JPA repository interfaces.
16:15:13.865 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [multipleStoresDetected,249] - Multiple Spring Data modules found, entering strict repository configuration mode!
16:15:13.867 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [registerRepositoriesIn,127] - Bootstrapping Spring Data Redis repositories in DEFAULT mode.
16:15:14.007 [main] INFO o.s.d.r.c.RepositoryConfigurationDelegate TID: N/A - [registerRepositoriesIn,187] - Finished Spring Data repository scanning in 123ms. Found 0 Redis repository interfaces.
16:15:14.117 [main] WARN o.s.b.a.e.EndpointId TID: N/A - [logWarning,155] - Endpoint ID ‘service-registry’ contains invalid characters, please migrate to a valid format.
16:15:14.368 [main] INFO o.s.c.c.s.GenericScope TID: N/A - [setSerializationId,295] - BeanFactory id=64c29a7d-f2ec-373b-8069-0cfe2b3209ad
16:15:14.405 [main] INFO c.u.j.c.EnableEncryptablePropertiesBeanFactoryPostProcessor TID: N/A - [postProcessBeanFactory,40] - Post-processing PropertySource instances
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource bootstrapProperties-psf-system-foundation-local.yml,DEFAULT_GROUP [org.springframework.cloud.bootstrap.config.BootstrapPropertySource] to EncryptableEnumerablePropertySourceWrapper
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource bootstrapProperties-psf-system-foundation.yml,DEFAULT_GROUP [org.springframework.cloud.bootstrap.config.BootstrapPropertySource] to EncryptableEnumerablePropertySourceWrapper
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource bootstrapProperties-psf-system-foundation,DEFAULT_GROUP [org.springframework.cloud.bootstrap.config.BootstrapPropertySource] to EncryptableEnumerablePropertySourceWrapper
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource bootstrapProperties-application-test.yml,DEFAULT_GROUP [org.springframework.cloud.bootstrap.config.BootstrapPropertySource] to EncryptableEnumerablePropertySourceWrapper
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,76] - Skipping PropertySource configurationProperties [class org.springframework.boot.context.properties.source.ConfigurationPropertySourcesPropertySource
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,76] - Skipping PropertySource servletConfigInitParams [class org.springframework.core.env.PropertySource$StubPropertySource
16:15:14.406 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,76] - Skipping PropertySource servletContextInitParams [class org.springframework.core.env.PropertySource$StubPropertySource
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource systemProperties [org.springframework.core.env.PropertiesPropertySource] to EncryptableMapPropertySourceWrapper
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource systemEnvironment [org.springframework.boot.env.SystemEnvironmentPropertySourceEnvironmentPostProcessor$OriginAwareSystemEnvironmentPropertySource] to EncryptableSystemEnvironmentPropertySourceWrapper
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource random [org.springframework.boot.env.RandomValuePropertySource] to EncryptablePropertySourceWrapper
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource springCloudClientHostInfo [org.springframework.core.env.MapPropertySource] to EncryptableMapPropertySourceWrapper
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource springCloudDefaultProperties [org.springframework.core.env.MapPropertySource] to EncryptableMapPropertySourceWrapper
16:15:14.407 [main] INFO c.u.j.EncryptablePropertySourceConverter TID: N/A - [makeEncryptable,81] - Converting PropertySource cachedrandom [org.springframework.cloud.util.random.CachedRandomPropertySource] to EncryptablePropertySourceWrapper
16:15:14.808 [main] INFO o.s.c.s.PostProcessorRegistrationDelegate$BeanPostProcessorChecker TID: N/A - [postProcessAfterInitialization,335] - Bean ‘com.netintech.common.redis.configure.RedisConfig’ of type [com.netintech.common.redis.configure.RedisConfig$$EnhancerBySpringCGLIB$$35e527dd] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
16:15:15.098 [main] INFO c.u.j.f.DefaultLazyPropertyFilter TID: N/A - [lambda$new$2,31] - Property Filter custom Bean not found with name ‘encryptablePropertyFilter’. Initializing Default Property Filter
16:15:15.106 [main] INFO c.u.j.r.DefaultLazyPropertyResolver TID: N/A - [lambda$new$2,35] - Property Resolver custom Bean not found with name ‘encryptablePropertyResolver’. Initializing Default Property Resolver
16:15:15.106 [main] INFO c.u.j.d.DefaultLazyPropertyDetector TID: N/A - [lambda$new$2,35] - Property Detector custom Bean not found with name ‘encryptablePropertyDetector’. Initializing Default Property Detector
16:15:15.398 [main] INFO o.s.b.w.e.t.TomcatWebServer TID: N/A - [initialize,108] - Tomcat initialized with port(s): 9301 (http)
16:15:15.413 [main] INFO o.a.c.h.Http11NioProtocol TID: N/A - [log,173] - Initializing ProtocolHandler [“http-nio-9301”]
16:15:15.414 [main] INFO o.a.c.c.StandardService TID: N/A - [log,173] - Starting service [Tomcat]
16:15:15.414 [main] INFO o.a.c.c.StandardEngine TID: N/A - [log,173] - Starting Servlet engine: [Apache Tomcat/9.0.46]
16:15:15.623 [main] INFO o.a.c.c.C.[.[.[/] TID: N/A - [log,173] - Initializing Spring embedded WebApplicationContext
16:15:15.624 [main] INFO o.s.b.w.s.c.ServletWebServerApplicationContext TID: N/A - [prepareWebApplicationContext,285] - Root WebApplicationContext: initialization completed in 4069 ms
16:15:15.771 [main] INFO c.u.j.e.DefaultLazyEncryptor TID: N/A - [lambda$new$2,37] - String Encryptor custom Bean not found with name ‘jasyptStringEncryptor’. Initializing Default String Encryptor
16:15:15.787 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.key-obtention-iterations, using default value: 1000
16:15:15.787 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.pool-size, using default value: 1
16:15:15.788 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.provider-name, using default value: null
16:15:15.788 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.provider-class-name, using default value: null
16:15:15.788 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.salt-generator-classname, using default value: org.jasypt.salt.RandomSaltGenerator
16:15:15.790 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.iv-generator-classname, using default value: org.jasypt.iv.RandomIvGenerator
16:15:15.791 [main] INFO c.u.j.c.StringEncryptorBuilder TID: N/A - [get,102] - Encryptor config not found for property jasypt.encryptor.string-output-type, using default value: base64
16:15:16.612 [main] INFO org.redisson.Version TID: N/A - [logVersion,41] - Redisson 3.15.6
16:15:17.512 [redisson-netty-2-15] INFO o.r.c.p.MasterPubSubConnectionPool TID: N/A - [lambda$run$0,166] - 1 connections initialized for 172.16.1.194/172.16.1.194:6379
16:15:18.521 [redisson-netty-2-19] INFO o.r.c.p.MasterConnectionPool TID: N/A - [lambda$run$0,166] - 24 connections initialized for 172.16.1.194/172.16.1.194:6379
16:15:18.830 [main] INFO c.z.h.HikariDataSource TID: N/A - [getConnection,110] - HikariPool-1 - Starting…
16:15:19.056 [main] INFO c.z.h.HikariDataSource TID: N/A - [getConnection,123] - HikariPool-1 - Start completed.
16:15:19.220 [main] INFO o.h.j.i.u.LogHelper TID: N/A - [logPersistenceUnitInformation,31] - HHH000204: Processing PersistenceUnitInfo [name: default]
16:15:19.329 [main] INFO o.hibernate.Version TID: N/A - [logVersion,44] - HHH000412: Hibernate ORM core version 5.4.32.Final
16:15:19.519 [main] INFO o.h.a.common.Version TID: N/A - [,56] - HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
16:15:19.733 [main] INFO o.h.dialect.Dialect TID: N/A - [,175] - HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect
16:15:19.782 [main] WARN o.h.e.j.e.i.JdbcEnvironmentInitiator TID: N/A - [initiateService,121] - HHH000339: Could not obtain connection metadata: java.sql.SQLSyntaxErrorException: Table ‘information_schema.KEYWORDS’ doesn’t exist
java.sql.SQLSyntaxErrorException: Table ‘information_schema.KEYWORDS’ doesn’t exist
at 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.StatementImpl.executeQuery(StatementImpl.java:1198)
at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getSQLKeywords(DatabaseMetaDataUsingInfoSchema.java:1193)
at com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData.getSQLKeywords(HikariProxyDatabaseMetaData.java)
at org.hibernate.engine.jdbc.env.internal.ExtractedDatabaseMetaDataImpl$Builder.apply(ExtractedDatabaseMetaDataImpl.java:202)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.(JdbcEnvironmentImpl.java:265)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:114)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.(InFlightMetadataCollectorImpl.java:176)
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1858)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1795)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1109)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:755)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:402)
at org.springframework

2
| username: TiDBer_sa4USb17 | Original post link

I checked the system database of TiDB, and indeed, there is no table named KEYWORDS under INFORMATION_SCHEMA. However, the same table exists in the MySQL 8.0 version. It seems that my Spring Boot auto-configuration uses this table, causing my program to fail to start properly. Is there any way to solve this problem?

3
| username: ShawnYan | Original post link

Currently, there is indeed no KEYWORDS table. Are you using MySQL JDBC 8? Try switching to 5.7.

It is recommended to update the Hibernate version and try again.

4
| username: ShawnYan | Original post link

This table will be added in a later version of TiDB. Please refer to the PR: parser, infoschema, executor: Add information_schema.keywords by dveeden · Pull Request #48807 · pingcap/tidb · GitHub

5
| username: 小龙虾爱大龙虾 | Original post link

Why insist on having this table? If it doesn’t work, just create one and move on. :joy_cat:

6
| username: oceanzhang | Original post link

The issue with the JDBC version, and also check for case sensitivity issues.

7
| username: TiDBer_sa4USb17 | Original post link

I tried replacing the driver

   <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
    </dependency>

with

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>

and the program returned to normal. I also did not find the KEYWORDS table in the MySQL 5.7 database. This should be an issue with the MySQL 8 driver. Although the 8.0 driver can adapt to MySQL 5.7, it seems it cannot adapt to TiDB.

8
| username: xfworld | Original post link

You can use TiDB’s dedicated driver

image
image1048×1000 106 KB

9
| username: tidb狂热爱好者 | Original post link

This is obviously a code issue. Look for the cause.

10
| username: Jellybean | Original post link

Obviously, this is an issue with the JDBC driver version your program is using to connect to TiDB.

When developing applications that interface with TiDB, it is recommended to use the dedicated driver officially recommended by TiDB: tidb-driver-orm.

11
| username: ShawnYan | Original post link

Yes, this table was added in MySQL 8.0.13, but using 5.1 is also possible.
The new version of Hibernate is compatible with TiDB, try upgrading and see.

12
| username: ShawnYan | Original post link

There are some other considerations for Hibernate, please refer to

13
| username: dba远航 | Original post link

This phenomenon is often caused by the use of unsupported functions in SQL statements, which we have encountered during migration.

14
| username: ShawnYan | Original post link

How was it handled at that time? Did you block the relevant statements?

15
| username: dba远航 | Original post link

First, find functions that are identical or similar to replace them. If none are available, directly rewrite the SQL.

16
| username: TiDBer_pYYfNEmY | Original post link

In the latest version of the TiDB driver, the getSQLKeywords method still performs a keywords query:

ResultSet rs = stmt.executeQuery("SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD");

It is recommended to modify the driver source code.

17
| username: MrSylar | Original post link

Are you referring to the MySQL connector when you mention the driver here?

18
| username: ShawnYan | Original post link

There are two temporary solutions to this problem:

  1. Use 5.1 JDBC, which will not look for the keyword table.
  2. Change the TiDB version number, prefix it with 5.7.

Third, the latest version of TiDB has added the keyword table. After the release of 8.1 LTS, you can upgrade.

19
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.