Uploaded image for project: 'FishEye'
  1. FishEye
  2. FE-6771

Migrating from MS SQL 2008 to another Database results in: Error writing to the database

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Low
    • 4.8.0
    • 4.1.2, 4.4.0, 4.6.0
    • None

    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

      1. Go to Administration > Database
      2. Fill details for target Database
      3. Save and Migrate

      Command Line

      1. Create back for your existing MS SQL 2008 database
      2. 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

      Attachments

        Issue Links

          Activity

            People

              mtokarski@atlassian.com Marek Tokarski
              lroscillo Lonnie
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 0.55h
                  0.55h