Summary

In localities that observe Daylight Savings Time (DST), there is an annual "spring forward" event where the clock skips one hour. Therefore, no data should exist in Jira Data Center or the database during this "lost hour". Under certain conditions, timezones can be misaligned, resulting in MySQL timestamps during the lost hour.

If violated, MySQL Connector/J (JDBC driver) will throw an exception and impact usability. This is identified with the following exceptions:

java.lang.IllegalArgumentException: HOUR_OF_DAY: 1 -> 2

java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3

java.lang.IllegalArgumentException: HOUR_OF_DAY: 3 -> 4

Environment

Jira Data Center 7.3 or later

MySQL Server 5.7 or later

MySQL Connector/J (JDBC driver) 8.0 or later

Diagnosis

User interface errors

Users may notice errors when retrieving entities – including issues, comments, and work logs. For example, the issue browser will display the following if a retrieved issue is affected:

Failed reindex

Jira administrators typically first notice the problem when indexing. Since the process touches every indexed entity, it's likely to encounter affected data.

Log error

The application logs (<jira-home>/log/atlassian-jira.log) will contain errors, including IllegalArgumentException: HOUR_OF_DAY. Here's a stack trace thrown while loading an affected issue:

2025-03-15 14:30:00,000-0000 http-nio-8080-exec-1 ERROR charlie 1x1x1 abcdef 0.0.0.0 /rest/api/2/issue/TIS-1 [c.a.p.r.v2.exception.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericEntityException: Error creating GenericValue (SQL Exception while getting value:  (HOUR_OF_DAY: 2 -> 3))
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: Error creating GenericValue (SQL Exception while getting value:  (HOUR_OF_DAY: 2 -> 3))
	at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:85)
	[...]
Caused by: org.ofbiz.core.entity.GenericEntityException: Error creating GenericValue (SQL Exception while getting value:  (HOUR_OF_DAY: 2 -> 3))
	at org.ofbiz.core.entity.EntityListIterator.getCompleteList(EntityListIterator.java:342)
	at org.ofbiz.core.entity.GenericDAO.selectByAnd(GenericDAO.java:735)
	[...]
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while getting value:  (HOUR_OF_DAY: 2 -> 3)
	at org.ofbiz.core.entity.jdbc.SqlJdbcUtil.getValue(SqlJdbcUtil.java:714)
	[...]
Caused by: java.sql.SQLException: HOUR_OF_DAY: 2 -> 3
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	[...]
Caused by: com.mysql.cj.exceptions.WrongArgumentException: HOUR_OF_DAY: 2 -> 3
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	[...]
Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3
	at java.base/java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2790)
	at java.base/java.util.Calendar.updateTime(Calendar.java:3411)
	at java.base/java.util.Calendar.getTimeInMillis(Calendar.java:1805)
	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromDatetime(SqlTimestampValueFactory.java:191)
	[...]

Any timestamp stored in the MySQL database can be affected. Atlassian Support has frequently observed this error with Automation for Jira (A4J) audit logs and Marketplace apps.

Cause

Timezone interpolation

This error shouldn't occur under normal conditions. Jira uses the timezone-neglecting DATETIME data type (MySQL reference). This means MySQL will allow you to store data during the lost hour. Even if you switch MySQL's timezone, the DATETIME data will be unaffected.

When Connector/J retrieves these timestamps for Jira, Java tries to apply the Java Virtual Machine (JVM) timezone. For example:

MySQL DATETIME	2024-03-10 02:30:00
JVM Timezone	America/Chicago
java.sql.Timestamp	2024-03-10 02:30:00 CDT

You can find your JVM timezone at ⚙️ (gear icon) > System > System info > jvm.system.timezone and update it by following Set the timezone for the Java environment for Jira Data Center.

Root cause

If you're experiencing these symptoms, one of the following likely occurred:

1. The JVM timezone was updated, causing some timestamps to fall in the lost hour.
   1. Related: switching from a timezone that does not observe DST to one that does.
2. A database administrator manually updated DATETIME data with date/time functions (MySQL reference).
3. You migrated database vendors (Switching databases).
4. Your locality's "spring forward" event recently occurred.

Atlassian Support suspects the fourth root cause is a JVM bug. In these environments, we've observed timestamps within the first 100ms of the lost hour, suggesting a problem with the java.util.Calendar method called by Connector/J. A similar problem is observed with Oracle's JDBC driver. We will continue investigating and update this article accordingly.

To be explicit, the following factors have NO impact:

1. The MySQL Server timezone, as set in your my.cnf or the global time_zone variable. MySQL DATETIME columns completely ignore timezones.
2. Jira's "default user time zone" global setting (⚙️ (gear icon) > System > Edit settings) or any particular user's timezone preference.

This problem is tracked by MySQL on https://bugs.mysql.com/bug.php?id=96276.

Solution

At a high level, this solution involves:

1. Scanning all DATETIME rows in the lost hour and recording the results in a temporary table.
2. Stopping Jira (full outage).
3. Creating a database backup (mysqldump).
4. Repairing the identified tables/columns.
5. Starting Jira.

Step 1 may take several minutes/hours to complete, but can be completed with minimal impact before the outage window.

Add temporary result table

We'll use this table to track which tables, columns, and year we need to fix. This will reduce the outage window and allow you to see what's impacted.

DROP TABLE IF EXISTS tmp_jira_dst_affected;

CREATE TABLE tmp_jira_dst_affected
(
    table_name          VARCHAR(64),
    column_name         VARCHAR(64),
    spring_forward_date DATE,
    affected_rows       INTEGER,
    PRIMARY KEY (table_name, column_name, spring_forward_date)
);

Add the "scan and record" procedure

Add the jira_scan_dst stored procedure to your MySQL database.

# Clear previous versions of the procedure
DROP PROCEDURE IF EXISTS jira_scan_dst;

# Change the delimiter to // to allow the procedure to be copied/pasted into the CLI
DELIMITER //

# Save the procedure
CREATE PROCEDURE jira_scan_dst(jira_db VARCHAR(64), spring_forward_date DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tn VARCHAR(64);
    DECLARE cn VARCHAR(64);
    DECLARE cursor_t CURSOR FOR SELECT c.TABLE_NAME, c.COLUMN_NAME
                                FROM information_schema.COLUMNS c
                                WHERE c.TABLE_SCHEMA = jira_db
                                  AND c.DATA_TYPE = 'datetime'
                                ORDER BY c.TABLE_NAME, c.COLUMN_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    # Modify 02:00 and 02:59 below if your locality doesn't "spring forward" at 02:00
    SET @lhs = CONCAT(spring_forward_date, ' ', TIME('02:00:00.000000'));
    SET @lhe = CONCAT(spring_forward_date, ' ', TIME('02:59:59.999999'));

    OPEN cursor_t;
    select_loop:
    LOOP
        FETCH cursor_t INTO tn, cn;
        IF done THEN
            LEAVE select_loop;
        END IF;

        SET @affected_rows = 0;

        SET @select_sql = CONCAT('SELECT COUNT(*) INTO @affected_rows FROM ', jira_db, '.`', tn,
                                 '` WHERE `', cn, '` BETWEEN ? AND ?;');

        PREPARE select_stmt FROM @select_sql;
        EXECUTE select_stmt USING @lhs, @lhe;
        DEALLOCATE PREPARE select_stmt;

        IF @affected_rows > 0 THEN
            SET @insert_sql = CONCAT('INSERT INTO ', jira_db, '.tmp_jira_dst_affected VALUE ',
                                     '(''', tn, ''', ''', cn, ''', ''', spring_forward_date, ''', ?) ',
                                     'ON DUPLICATE KEY UPDATE affected_rows = ?');

            PREPARE insert_stmt FROM @insert_sql;
            EXECUTE insert_stmt USING @affected_rows, @affected_rows;
            DEALLOCATE PREPARE insert_stmt;
        END IF;

    END LOOP;
END //

# Change the CLI delimiter back to a semicolon
DELIMITER ;

SELECT COUNT(*) FROM jiradb.jiraissue WHERE created BETWEEN '2025-03-09 02:00:00.000000' AND '2025-03-09 02:59:59.999999';

INSERT INTO jiradb.tmp_jira_dst_affected VALUE ('jiraissue', 'created', '2025-03-09', 15);

SET @lhs = CONCAT(spring_forward_date, ' ', TIME('02:00:00.000000'));
SET @lhe = CONCAT(spring_forward_date, ' ', TIME('02:59:59.999999'));

Execute the "scan and record" procedure

Call the procedure for all potentially-affected years. This may take several minutes to hours to process, and should be done before the outage window.

For example: to scan for the last 20 years in the United States:

CALL jira_scan_dst('jiradb', '2025-03-09');
CALL jira_scan_dst('jiradb', '2024-03-10');
CALL jira_scan_dst('jiradb', '2023-03-12');
CALL jira_scan_dst('jiradb', '2022-03-13');
CALL jira_scan_dst('jiradb', '2021-03-14');
CALL jira_scan_dst('jiradb', '2020-03-08');
CALL jira_scan_dst('jiradb', '2019-03-10');
CALL jira_scan_dst('jiradb', '2018-03-11');
CALL jira_scan_dst('jiradb', '2017-03-12');
CALL jira_scan_dst('jiradb', '2016-03-13');
CALL jira_scan_dst('jiradb', '2015-03-08');
CALL jira_scan_dst('jiradb', '2014-03-09');
CALL jira_scan_dst('jiradb', '2013-03-10');
CALL jira_scan_dst('jiradb', '2012-03-11');
CALL jira_scan_dst('jiradb', '2011-03-13');
CALL jira_scan_dst('jiradb', '2010-03-14');
CALL jira_scan_dst('jiradb', '2009-03-08');
CALL jira_scan_dst('jiradb', '2008-03-09');
CALL jira_scan_dst('jiradb', '2007-03-11');
CALL jira_scan_dst('jiradb', '2006-04-02');
CALL jira_scan_dst('jiradb', '2005-04-03');

(Optional) Review affected data

You may optionally review the affected data to assess the impact.

1. Obtain results from temporary table:
   

   mysql> SELECT * FROM jiradb.tmp_jira_dst_affected;
   +----------------------+-------------+---------------------+---------------+
   | table_name           | column_name | spring_forward_date | affected_rows |
   +----------------------+-------------+---------------------+---------------+
   | AO_589059_AUDIT_ITEM | created     | 2025-03-09          |             2 |
   | jiraissue            | created     | 2024-03-10          |             7 |
   | jiraissue            | created     | 2025-03-09          |             3 |
   +----------------------+-------------+---------------------+---------------+
   3 rows in set (0.00 sec)

2. Reference the listed table, column, and spring forward date:
   

   mysql> SELECT created FROM jiradb.jiraissue WHERE created BETWEEN '2025-03-09 02:00:00.000000' AND '2025-03-09 02:59:59.999999';
   +----------------------------+
   | created                    |
   +----------------------------+
   | 2025-03-09 02:05:36.509000 |
   | 2025-03-09 02:06:20.450000 |
   | 2025-03-09 02:37:38.516000 |
   +----------------------------+
   3 rows in set (0.00 sec)

Stop Jira and perform backup (begin outage window)

To avoid further corruption, Atlassian Support highly recommends performing the following steps with Jira fully stopped. Since this procedure will update several tables, we highly suggest obtaining a complete database backup. Please proceed with caution.

Add the "fix" procedure

Add the jira_fix_dst stored procedure to your MySQL database.

# Clear previous versions of the procedure
DROP PROCEDURE IF EXISTS jira_fix_dst;

# Change the delimiter to // to allow the procedure to be copied/pasted into the CLI
DELIMITER //

# Save the procedure
CREATE PROCEDURE jira_fix_dst(jira_db VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tn VARCHAR(64);
    DECLARE cn VARCHAR(64);
    DECLARE sfd DATE;
    DECLARE cursor_t CURSOR FOR SELECT table_name, column_name, spring_forward_date FROM tmp_jira_dst_affected ta;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cursor_t;
    update_loop:
    LOOP
        FETCH cursor_t INTO tn, cn, sfd;
        IF done THEN
            LEAVE update_loop;
        END IF;

        SET @lhs = CONCAT(sfd, ' ', TIME('02:00:00.000000'));
        SET @lhe = CONCAT(sfd, ' ', TIME('02:59:59.999999'));

        SET @update_sql = CONCAT('UPDATE ', jira_db, '.`', tn, '` SET `',
                                 cn, '` = ADDTIME(`', cn, '`, ''01:00'') WHERE `', cn, '` BETWEEN ? AND ?;');

        PREPARE update_stmt FROM @update_sql;
        EXECUTE update_stmt USING @lhs, @lhe;
        DEALLOCATE PREPARE update_stmt;
    END LOOP;
END //

# Change the CLI delimiter back to a semicolon
DELIMITER ;

UPDATE jiradb.jiraissue SET created = ADDTIME(created, '01:00') WHERE created BETWEEN '2025-03-09 02:00:00.000000' AND '2025-03-09 02:59:59.999999';

SET @lhs = CONCAT(spring_forward_date, ' ', TIME('02:00:00.000000'));
SET @lhe = CONCAT(spring_forward_date, ' ', TIME('02:59:59.999999'));

Execute the "fix" procedure

CALL jira_fix_dst('jiradb');

Replace jiradb with Jira's database name.

Start Jira

Bring Jira online one node at a time.

Drop stored procedures and temporary table

DROP PROCEDURE IF EXISTS jira_scan_dst;
DROP PROCEDURE IF EXISTS jira_fix_dst;
DROP TABLE IF EXISTS tmp_jira_dst_affected;

# Clear previous versions of the procedure
DROP PROCEDURE IF EXISTS jira_fix_dst;
 
# Change the delimiter to // to allow the procedure to be copied/pasted into the CLI
DELIMITER //
 
# Save the procedure
CREATE PROCEDURE jira_fix_dst(jira_db VARCHAR(64), spring_forward_date DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tn VARCHAR(64);
    DECLARE cn VARCHAR(64);
    DECLARE cursor_t CURSOR FOR SELECT c.TABLE_NAME, c.COLUMN_NAME
                                FROM information_schema.COLUMNS c
                                WHERE c.TABLE_SCHEMA = jira_db
                                  AND c.DATA_TYPE = 'datetime'
                                ORDER BY c.TABLE_NAME, c.COLUMN_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    SET @ihs = CONCAT(spring_forward_date, ' ', TIME('02:00:00.000000'));
    SET @ihe = CONCAT(spring_forward_date, ' ', TIME('02:59:59.999999'));
 
    OPEN cursor_t;
    update_loop:
    LOOP
        FETCH cursor_t INTO tn, cn;
        IF done THEN
            LEAVE update_loop;
        END IF;
 
        SET @update_sql = CONCAT('UPDATE ', jira_db, '.`', tn, '` SET `', cn, '` = ADDTIME(`', cn, '`, ''01:00'') WHERE `', cn,
                                 '` BETWEEN ''', @ihs, ''' AND ''', @ihe, ''';');
        PREPARE update_stmt FROM @update_sql;
        EXECUTE update_stmt;
        DEALLOCATE PREPARE update_stmt;
    END LOOP;
END //
 
# Change the CLI delimiter back to a semicolon
DELIMITER ;