Assets import with an SQL Selector fails due to a database query issue
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When running a SQL Selector for an Assets Import, receiving an error message stating that the database could not be queried. This is causing the import to fail, however the DB is accessible and can be reached from the Jira server running the Assets import.
Environment
Jira instance with Assets. An Assets Import must be configured with a SQL Selector on the import that accesses a MySQL database.
Diagnosis
Within the <Jira Home>/log/insight_import.log file the following error message can be found:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2024-03-15 10:51:14,004 [insight-InsightImportThreadGroup-worker-thread-1] | Unable to fetch data holder from database using conf com.riadalabs.jira.plugins.insight.services.imports.modules.db.DBConfiguration@20f07ff8, Error: ConnectionInsightException: Error in DBImportDataReader, message: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
com.riadalabs.jira.plugins.insight.common.exception.ConnectionInsightException: ConnectionInsightException: Error in DBImportDataReader, message: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
at com.riadalabs.jira.plugins.insight.services.imports.modules.db.DBImportDataReader.readData(DBImportDataReader.java:145)
at com.riadalabs.jira.plugins.insight.services.imports.modules.db.DBImportModule.dataHolder(DBImportModule.java:67)
at com.riadalabs.jira.plugins.insight.services.imports.modules.db.DBImportModule.dataHolder(DBImportModule.java:35)
at com.riadalabs.jira.plugins.insight.services.jira.module.ImportModuleDelegator.dataHolder(ImportModuleDelegator.java:57)
at com.riadalabs.jira.plugins.insight.services.imports.common.importjobprovider.ImportJobProvider.getImportDataHolder(ImportJobProvider.java:137)
at com.riadalabs.jira.plugins.insight.services.imports.common.importjobprovider.DataHolderFetcherJobProvider$DataHolderFetcher.fetchDataHolder(DataHolderFetcherJobProvider.java:96)
at com.riadalabs.jira.plugins.insight.services.imports.common.importjobprovider.DataHolderFetcherJobProvider$DataHolderFetcher.executeTask(DataHolderFetcherJobProvider.java:86)
at com.riadalabs.jira.plugins.insight.services.imports.common.importjobprovider.DataHolderFetcherJobProvider$DataHolderFetcher.executeTask(DataHolderFetcherJobProvider.java:70)
at com.riadalabs.jira.plugins.insight.services.core.multithreadservice.InsightServiceJob.call(InsightServiceJob.java:42)
at com.atlassian.sal.core.executor.ThreadLocalDelegateCallable.call(ThreadLocalDelegateCallable.java:38)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)Cause
This is related to behavior from MySQL/Java which cannot convert an invalid date '0000-00-00 00:00:00' to a Java DateTime object. It is referenced within this MySQL Bug.
Solution
There are a few options to address this:
Removing the date column returned from the SQL Selector which contains the '0000-00-00 00:00:00' date time value will resolve the issue. Also updating the value (if feasible) within the database to valid date time will prevent the query from failing.
You can also add the zeroDateTimeBehavior=convertToNull option to the JDBC connector for the database where the SQL Selector is running. This will convert the '0000-00-00 00:00:00' date time object to a null value.
Was this helpful?