Bamboo with MySQL DB throws SQL exception HOUR_OF_DAY: 2 -> 3 while exporting the configuration

Issue Summary

This is reproducible on Data Center: Yes

Bamboo throws below SQL Exception while exporting the configuration using ⚙️ > Overview > Export page.

Export has failed. Errors encountered while exporting: JDBC exception on Hibernate data access: SQLException for SQL [select labelimpl0_.LABEL_ID as label_id1_85_, labelimpl0_.NAME as name2_85_, labelimpl0_.NAMESPACE as namespac3_85_, labelimpl0_.CREATED_DATE as created_4_85_, labelimpl0_.UPDATED_DATE as updated_5_85_ from LABEL labelimpl0_]; SQL state [S1009]; error code [0]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query.

The Above error means there is a invalidate date ( In context of Daylights savings time ). 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 Bamboo database between 02:00:00 and 02:59:59 on the annual transition date. For example is 2023 in Europe, it was 26-Mar-2023, so if there is any entry like for example 2023-03-26 02:42:33 in anyone of the date time columns in the DB, while this data is processed by the application you'll see the error.

To know which dates across different regions are affected by this error, please refer https://www.timeanddate.com/time/change/

There are few possible reasons how this error would come up, for example

1) Your Bamboo Instance was previously hosted on a timezone which does not follow Day light savings time for example Asia/Kolkata.

2) The instance is shifted to a timezone which follows Day light savings time for example Europe/Brussels.

3) In the first case Bamboo will insert any dates on DB tables and will not skip 02:00 on the DB tables on the day when the Day light savings is applicable for example in 2023 for Europe it was 26 Mar 2023.

4) So in the first timezone the below entry in the DB is correct, but in the second timezone it's incorrect.

The problem is only seen on Bamboo connected to MySQL DB and using connector 8 and this is not seen on connector 5.1, this is actually a bug in MySQL connector which is tracked here https://bugs.mysql.com/bug.php?id=96276

Steps to Reproduce

To reproduce the issue I've manually modified some data in label table, please note this error is not limited to label table, but can come for any other tables where there is datetime datatype column. The export fails because it starts reading data from label table first. 

1) Create any labels in Bamboo and below entry would be created in the DB.

2) Manually modify the create_date column to a time which is invalid in your current timezone for example Europe/Brussels, for 2023 it was 2023-03-26

 

Expected Results

Bamboo should complete the export properly and generate the zip

Actual Results

The export fails and below exceptions are seen

Export has failed. Errors encountered while exporting: JDBC exception on Hibernate data access: SQLException for SQL [select labelimpl0_.LABEL_ID as label_id1_85_, labelimpl0_.NAME as name2_85_, labelimpl0_.NAMESPACE as namespac3_85_, labelimpl0_.CREATED_DATE as created_4_85_, labelimpl0_.UPDATED_DATE as updated_5_85_ from LABEL labelimpl0_]; SQL state [S1009]; error code [0]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query.

Problems occurred while exporting
org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select labelimpl0_.LABEL_ID as label_id1_85_, labelimpl0_.NAME as name2_85_, labelimpl0_.NAMESPACE as namespac3_85_, labelimpl0_.CREATED_DATE as created_4_85_, labelimpl0_.UPDATED_DATE as updated_5_85_ from LABEL labelimpl0_]; SQL state [S1009]; error code [0]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query

Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 2 -> 3
	at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2829)
	at java.util.Calendar.updateTime(Calendar.java:3393)

Workaround

1) Use MySQL Connector/J 5.1.49 with MySQL 8, as per Supported platform we don't advise you to do that, but this is supported as per https://dev.mysql.com/blog-archive/mysql-connector-j-5-1-49-ga-has-been-released/. You can probably just use this connector to download an export zip and replace back the old connector once done. All this process will required stopping and restarting Bamboo.

2) Second option is to change the JVM timezone to a timezone which doesn't have Day light savings time, so that when this data is parsed Application should not think it as invalid, you can refer the timezone value at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones. To change the timezone you can use the below JVM argument in setenv.sh/bat file and it will require restart of Bamboo, refer How to force the timezone of Bamboo Server for more details, an example below

JVM_SUPPORT_RECOMMENDED_ARGS="-Duser.timezone=UTC"

If you put this parameter all the new timedata value stored in the DB will be on this timezone and will not actually reflect the exact timezone your server is located in.

3) Lastly you can update all the invalid datetime columns in your DB with an update query, please refer Jira throws HOUR_OF_DAY: 2 -> 3 exception with MySQL
Please restrain in using this option as it updates mostly all the tables in your Bamboo DB.