DEV Community

Salad Lam
Salad Lam

Posted on

(Bug) value of TIME is altered by MySQL JDBC driver

#java #mysql

Notice

I wrote this article and was originally published on Qiita on 30 August 2022.

Description

In MySQL specification the type TIME is not affected by time zone setting. But value of TIME is changed when write to database if following conditions match:

  1. default time zone of MySQL server is different from the time zone of Java application
  2. using JPA, the field of the entity is LocalTime and the corresponding column is TIME type
  3. using mysql-connector-java driver version 8.0.22 (I don't sure which version of driver is in affect also)

Bug replication

Set the following parameter in "my.ini".

default-time-zone = "+00:00"
Enter fullscreen mode Exit fullscreen mode

Create table "timetest" in database "test"

CREATE TABLE `timetest` (
  `id` int(11) NOT NULL,
  `rtime` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `timetest`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `timetest`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Then the following is the source code.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath />
    </parent>

    <groupId>info.saladlam.example</groupId>
    <artifactId>mysql-timezone</artifactId>
    <version>0.1</version>
    <name>mysql-timezone</name>
    <description>Test time zone problem of MySQL JDBC driver</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>javax.persistence-api</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
Enter fullscreen mode Exit fullscreen mode 
debug=true
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
Enter fullscreen mode Exit fullscreen mode 
package info.saladlam.example.spring.test;

import javax.persistence.*;
import java.time.LocalTime;

@Entity
@Table(name = "timetest")
public class TestEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "rtime")
    private LocalTime time;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public LocalTime getTime() {
        return time;
    }

    public void setTime(LocalTime time) {
        this.time = time;
    }
}
Enter fullscreen mode Exit fullscreen mode 
package info.saladlam.example.spring.test;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceContextType;
import java.time.LocalTime;

@SpringBootApplication
public class TimeTest implements CommandLineRunner {

    private static Logger LOGGER = LoggerFactory.getLogger(TimeTest.class);

    private JdbcTemplate jdbcTemplate;

    private TransactionTemplate transactionTemplate;

    @PersistenceContext(type = PersistenceContextType.TRANSACTION)
    private EntityManager em;

    @Autowired
    public TimeTest(PlatformTransactionManager platformTransactionManager, JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.transactionTemplate = new TransactionTemplate(platformTransactionManager);
    }

    public static void main(String[] args) {
        SpringApplication.run(TimeTest.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        // clear entry
        transactionTemplate.execute(transactionStatus -> {
            jdbcTemplate.execute("USE `test`;");
            jdbcTemplate.execute("DELETE FROM `timetest` WHERE `id` = 1;");
            jdbcTemplate.execute("ALTER TABLE `timetest` AUTO_INCREMENT = 1;");
            return null;
        });

        // insert by JPA
        transactionTemplate.execute(transactionStatus -> {
            TestEntity e = new TestEntity();
            e.setTime(LocalTime.of(14, 0));
            em.persist(e);
            return null;
        });

        // using SQL to retrieve
        transactionTemplate.execute(transactionStatus -> {
            LOGGER.info("global time zone: {}", jdbcTemplate.queryForObject("SELECT @@global.time_zone;", String.class));
            LOGGER.info("session time zone: {}", jdbcTemplate.queryForObject("SELECT @@session.time_zone;", String.class));
            jdbcTemplate.execute("SET @@session.time_zone = '+01:00';");
            LOGGER.info("session time zone: {}", jdbcTemplate.queryForObject("SELECT @@session.time_zone;", String.class));

            jdbcTemplate.execute("USE `test`;");
            LOGGER.info("time: {}", jdbcTemplate.queryForObject("SELECT `rtime` FROM `timetest` WHERE `id` = 1;", String.class));
            return null;
        });
    }

}
Enter fullscreen mode Exit fullscreen mode

Logging

2022-08-30 20:41:38.758 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [USE `test`;]
2022-08-30 20:41:38.774 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [DELETE FROM `timetest` WHERE `id` = 1;]
2022-08-30 20:41:38.775 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [ALTER TABLE `timetest` AUTO_INCREMENT = 1;]
2022-08-30 20:41:38.811 DEBUG 14716 --- [           main] org.hibernate.SQL                        : insert into timetest (rtime) values (?)
2022-08-30 20:41:38.864 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL query [SELECT @@global.time_zone;]
2022-08-30 20:41:38.876  INFO 14716 --- [           main] i.saladlam.example.spring.test.TimeTest  : global time zone: +00:00
2022-08-30 20:41:38.876 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL query [SELECT @@session.time_zone;]
2022-08-30 20:41:38.881  INFO 14716 --- [           main] i.saladlam.example.spring.test.TimeTest  : session time zone: +00:00
2022-08-30 20:41:38.881 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [SET @@session.time_zone = '+01:00';]
2022-08-30 20:41:38.883 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL query [SELECT @@session.time_zone;]
2022-08-30 20:41:38.885  INFO 14716 --- [           main] i.saladlam.example.spring.test.TimeTest  : session time zone: +01:00
2022-08-30 20:41:38.885 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL statement [USE `test`;]
2022-08-30 20:41:38.886 DEBUG 14716 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL query [SELECT `rtime` FROM `timetest` WHERE `id` = 1;]
2022-08-30 20:41:38.889  INFO 14716 --- [           main] i.saladlam.example.spring.test.TimeTest  : time: 13:00:00
2022-08-30 20:41:38.898  INFO 14716 --- [extShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-08-30 20:41:38.902  INFO 14716 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-08-30 20:41:38.933  INFO 14716 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
Enter fullscreen mode Exit fullscreen mode

Time value "14:00" is written to database, but actual value "13:00" is saved.

Bug fix

This bug was fixed on driver version 8.0.30. (may be fixed on the earlier driver, but I don't try it.)

Top comments (0)

Read next

susangithaigan profile image

Connect MySQL Workbench to MySQL Server in WSL using Node

Susan Githaiga -

jelilio profile image

Implementing Soft Delete in Spring WebFlux with R2DBC

Jelili Adesina -

arshisaxena26 profile image

Demystifying hashCode() and equals(): The Backbone of Java Hash Collections

Arshi Saxena -

apow profile image

Introducing the PII Mask Maven Dependency: Secure Your JSON Data with Ease

Victor Ihedioha -