Summary

In localities that observe Daylight Savings Time (DST), there is an annual "spring forward" event where the hour of 02:00 is skipped to 03:00. Therefore, 02:00 is an invalid hour. No data should exist in Jira or the database between 02:00:00 and 02:59:59 on the annual transition date. Under certain conditions, timezones can be misaligned, resulting in MySQL timestamps during the invalid hour.

If violated, MySQL Connector/J (JDBC driver) 8.0 or later will throw an exception and affect usability within Jira. This is identified with the following exception:

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

Environment

Jira 7.3 or later

MySQL Server 5.7 or later¹

MySQL Connector/J (JDBC driver) 8.0 or later²

¹ Support for MySQL Server 5.7 ended with Jira 9.2.

² Connector/J 5.x is not susceptible to this problem, as it simply shifts the timestamp one hour ahead during retrieval. However, Connector/J 5.x is being deprecated.

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 print errors, including IllegalArgumentException: HOUR_OF_DAY: 2 -> 3. Here's a stack trace thrown while loading an affected issue:

2023-08-01 17:00:00,000-0500 http-nio-8080-exec-1 url: /browse/TIS-1; user: charlie ERROR charlie 1xx1 xxxxxxx 0.0.0.0 /browse/TIS-1 [o.a.c.c.C.[Catalina].[localhost].[/]] Unhandled exception occurred whilst decorating page
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)
	[...]
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:130)
	[...]
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(Unknown Source)
	at java.base/java.util.Calendar.updateTime(Unknown Source)
	at java.base/java.util.Calendar.getTimeInMillis(Unknown Source)
	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromDatetime(SqlTimestampValueFactory.java:191)
	[...]

MySQL data

Database administrators may identify data during the invalid hour. For example, the exception above was generated with:

mysql> SELECT p.pkey, ji.issuenum, CREATED FROM jiraissue ji JOIN project p on p.ID = ji.PROJECT WHERE CREATED BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:59:59';
+------+----------+---------------------+
| pkey | issuenum | CREATED             |
+------+----------+---------------------+
| TIS  |        1 | 2024-03-10 02:30:00 |
+------+----------+---------------------+
1 rows in set (0.00 sec)

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 invalid hour. Even if you switch MySQL's timezone, the DATETIME data will be unaffected.

When Connector/J retrieves these timestamps for Jira, it 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-010 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.

If Java can't parse this timestamp, it throws an IllegalArgumentException: HOUR_OF_DAY: 2 -> 3.

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 invalid hour.
   1. Related: switching from a timezone that does not observe DST (such as GMT) 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).

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. The Linux/Windows host timezone, only if the JVM timezone is manually overridden in Jira's setenv file. Otherwise, the JVM uses the host operating system's timezone.
3. Jira's "default user time zone" global setting (⚙️ (gear icon) > System > Edit settings) or any particular user's timezone preference.

With Connector/J 5.x and below, this problem would be silently ignored and the timestamp would simply be advanced by one hour. This is why some Jira administrators notice this problem after upgrading their environment. This problem is tracked on https://bugs.mysql.com/bug.php?id=96276. Since Connector/J 5.x is deprecated, we can't advise downgrading your driver as a workaround.

Solution

Add database stored 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), 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'));
    SET @ihe = CONCAT(spring_forward_date, ' ', TIME('02:59:59'));

    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 ;

UPDATE jiradb.changegroup SET CREATED = ADDTIME(CREATED, '01:00') WHERE CREATED BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:59:59';

Execute procedure

CALL jira_fix_dst('jiradb', '2024-03-10');
CALL jira_fix_dst('jiradb', '2023-03-12');
CALL jira_fix_dst('jiradb', '2022-03-13');
CALL jira_fix_dst('jiradb', '2021-03-14');
CALL jira_fix_dst('jiradb', '2020-03-08');
CALL jira_fix_dst('jiradb', '2019-03-10');

Restart Jira

If you didn't stop your cluster as recommended, you must cold restart your cluster after updating the database. This ensures any caches with invalid data are completely flushed.