How to Solve MySQL Timezone Issue in Wildlfy Java Application Server

Posted on

This article focus on how to solve the problem related with the failure of executing Wildfly Java Application Server’s configuration. It is when the JDBC JNDI is specified later on it is used for the MySQL database to be connected. It is done by adding the datasource which can be referred from the article titled ‘How to Add Datasource in Wildfly Java Application Server via Command Line’ in this link . Based on the article given in that link, datasource added has a parameter –jndi-name as specified as follows :

[standalone@localhost:9990 /] data-source add --name=my-datasource --jndi-name=java:/jdbc/mydb --driver-name=mysql-connector-java-8.0.11.jar --connection-url=jdbc:mysql://localhost:3306/dbtest --user-name=root --password=password
[standalone@localhost:9990 /]

The above is the actual command executed in Wildfly Management Console for adding a new datasource. After adding the new datasource, the next step is to reload the Wildlfy Java Application Server by executing the following command :

[standalone@localhost:9990 /] :reload
{
"outcome" => "success",
"result" => undefined
}
[standalone@localhost:9990 /]

The actual outcome from the above command generated which is ‘:reload’ is showing that the restart or the reload process of Wildlfy Java Application Server is actually success. It means that the datasource has successfully been loaded. The next step is to test the datasource by executing the following command below :

[standalone@localhost:9990 /] /subsystem=datasources/data-source=my-datasource:test-connection-in-pool
{
"outcome" => "failed",
"failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid",
"rolled-back" => true,
"response-headers" => {"process-state" => "reload-required"}
}
[standalone@localhost:9990 /]

Below are steps taken to solve the above problem :

  1. Take a look into the error in details.

Based on the output generated above, there is an error where the outcome shown is failed. The error message specified the failure is also pointed out which is “WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid”.

After searching for the main cause which can actually be considered as the problem triggering the invalid connection as shown in the above error message, the error is located in the time zone value which is not supported by the new MySQL JDBC Driver connector. It can be retrieved from the error message written in server.log file as shown below :

2018-06-10 18:57:34,611 WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (External Management Request Threads -- 1) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: IJ031084: Unable to create connection
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:345)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:352)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:287)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1327)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:499)
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:1067)
        at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:93)
        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:240)
        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$1.execute(PoolOperations.java:97)
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:982)
        at org.jboss.as.controller.AbstractOperationContext.processStages(AbstractOperationContext.java:726)
        at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:450)
        at org.jboss.as.controller.OperationContextImpl.executeOperation(OperationContextImpl.java:1408)
        at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:423)
        at org.jboss.as.controller.ModelControllerImpl.lambda$execute$1(ModelControllerImpl.java:243)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:263)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:229)
        at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:243)
        at org.jboss.as.domain.http.server.DomainApiHandler.handleRequest(DomainApiHandler.java:212)
        at io.undertow.server.handlers.encoding.EncodingHandler.handleRequest(EncodingHandler.java:72)
        at org.jboss.as.domain.http.server.DomainApiCheckHandler.handleRequest(DomainApiCheckHandler.java:93)
        at org.jboss.as.domain.http.server.security.ElytronIdentityHandler.lambda$handleRequest$0(ElytronIdentityHandler.java:62)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:287)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:244)
        at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:254)
        at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:225)
        at org.jboss.as.domain.http.server.security.ElytronIdentityHandler.handleRequest(ElytronIdentityHandler.java:61)
        at io.undertow.server.handlers.BlockingHandler.handleRequest(BlockingHandler.java:56)
        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:360)
        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
        at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
        at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
        at java.lang.Thread.run(Thread.java:748)
        at org.jboss.threads.JBossThread.run(JBossThread.java:485)

Caused by: java.sql.SQLException: The server time zone value 'XXX' 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 com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
        at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:444)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321)
        ... 35 more
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'XXX' 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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:83)
        at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128)
        at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2201)
        at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2225)
        at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1391)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:993)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:852)
        ... 39 more
Caused by: java.sql.SQLException: The server time zone value 'XXX' 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 com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:87)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:61)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:71)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862)
        at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:444)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321)
        ... 35 more
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'XXX' 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 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:59)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:83)
        at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128)
        at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2201)
        at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2225)
        at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1391)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:993)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:852)
        ... 39 more

2018-06-10 18:57:34,613 ERROR [org.jboss.as.controller.management-operation] (External Management Request Threads -- 1) WFLYCTL0013: Operation ("test-connection-in-pool") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "myown-DS")
]) - failure description: "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid"

The error as shown above is focusing on the timezone which can be summarized as follows :

This first error message is shown above :

Caused by: java.sql.SQLException: The server time zone value 'XXX' 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.

Another error message is shown below :

Caused by: java.sql.SQLException: The server time zone value 'XXX' 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.

And another error message is presented :

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'XXX' 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.

Those error messages above, all of those error messages are concerning on the server time zone which is not supported by the JDBC driver configured. So, the JNDI JDBC URL connection after searching the solutions by googling it up, it can be solved by modifying it as shown below :

Solution  
jdbc:mysql://localhost/dbtest?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

2.  For the solution to be used, just add the datasource after removing the old one. Below is the process of adding the new one which has been modified :

[standalone@localhost:9990 /] data-source add --name=my-datasource --jndi-name=jdbc:mysql://localhost/dbtest?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC  --user-name=root --password=password
operation-requires-reload: true
process-state: reload-required
[standalone@localhost:9990 /]

3. Reload the newly added datasource by executing the following command :

[standalone@localhost:9990 /] :reload
{
"outcome" => "success",
"result" => undefined
}

4. Try to test the newly added datasource by executing the following command :

[standalone@localhost:9990 /] /subsystem=datasources/data-source=my-datasource:test-connection-in-pool
{
"outcome" => "success",
"result" => [true]
}
[standalone@localhost:9990 /]

 

Leave a Reply