MariaDB 또는 MySQL에 접속 시 DBC connection 이 실패하는 경우가 발생한다. 발생하는 에러 중에 이번 블로그에서는 "The server time zone value 'KST' is unrecognized or represents more than one time zone." 에러에 대해서 알아보겠다.
■ 에러
2023-01-11 00:37:10.619 DEBUG 1 --- [ main] o.a.tomcat.jdbc.pool.PooledConnection : Instantiating driver using class: com.mysql.jdbc.Driver [url=jdbc:mysql://xxx.xxx.xxx:3306/K8S_BROKER?characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10]
2023-01-11 00:37:10.620 DEBUG 1 --- [ main] o.a.tomcat.jdbc.pool.ClassLoaderUtil : Attempting to load class[com.mysql.jdbc.Driver] from org.springframework.boot.loader.LaunchedURLClassLoader@7c16905e
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2023-01-11 00:37:28.875 WARN 1 --- [ main] o.h.e.j.e.i.JdbcEnvironmentInitiator : HHH000342: Could not obtain connection to query metadata
java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 10 times. Giving up.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:1677) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1601) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:319) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:744) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:676) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:483) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131) ~[tomcat-jdbc-9.0.41.jar!/:na]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) ~[hibernate-core-5.4.30.Final.jar!/:5.4.30.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) ~[spring-orm-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1858) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1795) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1109) ~[spring-context-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869) ~[spring-context-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551) ~[spring-context-5.2.14.RELEASE.jar!/:5.2.14.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:755) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:402) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:312) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1247) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1236) ~[spring-boot-2.3.10.RELEASE.jar!/:2.3.10.RELEASE]
at paasta.delivery.pipeline.common.api.Application.main(Application.java:18) ~[classes!/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) ~[app.jar:na]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) ~[app.jar:na]
at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) ~[app.jar:na]
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:88) ~[app.jar:na]
Caused by: com.mysql.cj.core.exceptions.InvalidConnectionAttributeException: The server time zone value 'KST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[na:na]
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:118) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.mysqla.MysqlaSession.configureTimezone(MysqlaSession.java:293) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2399) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:1642) ~[mysql-connector-java-6.0.6.jar!/:6.0.6]
... 61 common frames omitted
■ MySQL & MariaDB 타임존(TimeZone) 확인하기
1) 현재 타임존과 현재 시간을 찍어서 확인하기
SELECT @@time_zone, now();
@@time_zone|now() |
-----------+-----------------------+
SYSTEM |2023-01-11 10:08:33.000|2) 시스템 타임존 확인하기
SHOW VARIABLES WHERE Variable_name LIKE '%time_zone%';
Variable_name |Value |
----------------+------+
system_time_zone|KST |
time_zone |SYSTEM|
■ JDBC URL 변경
현재 사용하고 있는 DB의 타임존을 확인하고 jdbc URL에 serverTimezone 프로퍼티를 추가해주면 해결이 된다.
jdbc:mysql://localhost/db?useUnicode=true&serverTimezone=Asia/Seoul
출처 및 참고 사이트
MySQL JDBC 에러 해결 "The server time zone value 'KST' is unrecognized or represents more than one time zone. "
www.lesstif.com
MySQL & MariaDB 타임존(TimeZone) 확인하기
데이터베이스에 날짜와 시간을 저장할 때 어느 시간을 기준으로 저장을 해야할지 지정해줄 필요가 있습니다. MySQL 과 1. MySQL & MariaDB 타임존(TimeZone) 확인하기 1) 현재 타임존과 현재 시간을 찍어
harpuria.tistory.com
