Description
Summary
Migrating FishEye Database from MS SQL 2008 results in:
Database error at cwd_user
...
java.io.IOException: Error writing to the database
..
Restore failed. This may leave your target FISHEYE_INST directory in an inconsistent state.
Environment
MS SQL 2008
Any FishEye version
Steps to Reproduce
Migrating from the FishEye Web UI
- Go to Administration > Database
- Fill details for target Database
- Save and Migrate
Command Line
- Create back for your existing MS SQL 2008 database
- Run the following command:
fisheyectl.sh restore --sql --file /path/to/backup.zip --dbtype <database type> --jdbcurl jdbc:<database-type-jdbc>://<hostname>:<port>/<database> --username <user name> --password <password> --force
Expected Results
Migration should complete successfully
Actual Results
Command:
fisheyectl.sh restore --sql --file ../data-migration.zip --dbtype postgresql --jdbcurl jdbc:postgresql://<host>:5432/<database> --username <user> --password <passwd> --force
Error:
Database error at cwd_user:2361 (table:row) of the input: Batch entry 0 INSERT INTO cwd_user (id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, external_id, directory_id, credential) VALUES ('1', 'Username@domain-name.com', 'Username@domain-name.com', 'T', '2016-10-04 20:45:26.3500000', '2016-10-04 20:45:26.3500000', 'Username', 'Username', 'Last Name', 'Last Name', 'Username Last Name', 'Username Last Name', 'Username@domain-name.com', 'Username@domain-name.com', '1:3a6899d2-ffe6-415b-9927-34ee52c17501', '2', 'nopass') was aborted. Call getNextException to see the cause. INFO - Shutting down cross-repo lucene index INFO - Shutdown in progress... INFO - Shutting down DB. INFO - *** application context closed *** java.io.IOException: Error writing to the database: Batch entry 0 INSERT INTO cwd_user (id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, external_id, directory_id, credential) VALUES ('1', 'Username@domain-name.com', 'Username@domain-name.com', 'T', '2016-10-04 20:45:26.3500000', '2016-10-04 20:45:26.3500000', 'Username', 'Username', 'Last Name', 'Last Name', 'Username Last Name', 'Username Last Name', 'Username@domain-name.com', 'Username@domain-name.com', '1:3a6899d2-ffe6-415b-9927-34ee52c17501', '2', 'nopass') was aborted. Call getNextException to see the cause. at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:211) at com.atlassian.crucible.migration.item.SQLBackup$1.restore(SQLBackup.java:172) ... Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO cwd_user (id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, external_id, directory_id, credential) VALUES ('1', 'Username@domain-name.com', 'Username@domain-name.com', 'T', '2016-10-04 20:45:26.3500000', '2016-10-04 20:45:26.3500000', 'Username', 'Username', 'Last Name', 'Last Name', 'Username Last Name', 'Username Last Name', 'Username@domain-name.com', 'Username@domain-name.com', '1:3a6899d2-ffe6-415b-9927-34ee52c17501', '2', 'nopass') was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569) at org.postgresql.core.v3.QueryExecutorImpl$ErrorTrackingResultHandler.handleError(QueryExecutorImpl.java:348) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796) ... Restore failed.
Or Command with No Batch:
fisheyectl.sh restore --sql --file ../data-migration.zip --dbtype postgresql --jdbcurl jdbc:postgresql://<host>:5432/<database> --username <user> --password <passwd> --force --no-batch-sql
Error:
ERROR - There was an exception thrown trying to dispatch event [com.atlassian.plugin.event.events.PluginFrameworkShutdownEvent@5fe36dec] from the invoker [SingleParameterMethodListenerInvoker{method=public void com.atlassian.plugin.loaders.ScanningPluginLoader.onShutdown(com.atlassian.plugin.event.events.PluginFrameworkShutdownEvent), listener=com.atlassian.plugin.loaders.BundledPluginLoader@a07479d}] java.lang.RuntimeException: java.lang.NullPointerException at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:39) [atlassian-event-3.1.1.jar:?] at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$1$1.run(AsynchronousAbleEventDispatcher.java:38) [atlassian-event-3.1.1.jar:?] at com.google.common.util.concurrent.MoreExecutors$DirectExecutorService.execute(MoreExecutors.java:299) [guava-18.0.jar:?] ... Caused by: java.lang.NullPointerException at com.atlassian.plugin.osgi.container.felix.FelixOsgiContainerManager.removeBundleListener(FelixOsgiContainerManager.java:506) [atlassian-plugins-osgi-4.4.0.jar:?] at com.atlassian.plugin.osgi.factory.OsgiBundlePlugin.uninstallInternal(OsgiBundlePlugin.java:276) [atlassian-plugins-osgi-4.4.0.jar:?] ... 25 more INFO - *** application context closed *** java.io.IOException: Error writing to the database: ERROR: column "created_date" is of type timestamp without time zone but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 282 at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:211) at com.atlassian.crucible.migration.item.SQLBackup$1.restore(SQLBackup.java:172) ... Caused by: org.postgresql.util.PSQLException: ERROR: column "created_date" is of type timestamp without time zone but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 282 ... 9 more Restore failed. This may leave your target FISHEYE_INST directory in an inconsistent state.
Notes
This is due to an incompatibility in date an timestamp format. Reason is sqlserver timestamp is saved as string, rather than a timestamp and FE is not able to restore that to New Target Database
cwd_user.created_date and cwd_user.updated_date have the following formats in sql/database.xml:
DB | date example |
---|---|
H2 | <timestamp>2017-01-31T12:08:56.207Z</timestamp> |
postgres | <timestamp>2017-01-31T12:08:56.207Z</timestamp> |
mysql | <timestamp>2017-01-31T14:07:45.000Z</timestamp> |
sql server 2008 | <escapedString>2017-01-31 12:33:06.2070000</escapedString> |
Workaround
Manually correct the sql/database.xml file
$ unzip backup.zip sql/database.xml $ sed -E 's|<escapedString>([0-9]{4}-[0-9]{2}-[0-9]{2}) ([0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{3})0000</escapedString>|<timestamp>\1T\2Z</timestamp>|g' sql/database.xml > sql/database2.xml $ mv sql/database2.xml sql/database.xml $ zip -u backup.zip sql/database.xml