Could not restore the backup to a MySQL database - Reason: The database schema could not be restored.

Symptoms

After running the restore client to a newly created MySQL DB instance, the following appears in the <path/to/backup/client>/log/atlassian-sbc-YYYY-MM-DD-HHMM.log:

2014-06-23 21:29:46,628 INFO Initializing
2014-06-23 21:29:50,043 INFO Unpacking stash-20140618-221025-932.tar to /var/atlassian/application-data/stash
2014-06-23 21:29:53,806 INFO Validating database before restore
2014-06-23 21:30:02,726 INFO Examining structure of source database
2014-06-23 21:30:03,487 INFO Restoring database schema definition
2014-06-23 21:30:03,643 ERROR stash-20140618-221025-932.tar could not be restored. Reason: The database schema could not be restored.
com.atlassian.stash.internal.backup.client.RestoreException: The database schema could not be restored.
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.createSchema(DefaultRestoreClient.groovy:287) ~[stash-restore-client.jar:1.2.1]
at com.atlassian.stash.internal.backup.client.DefaultRestoreClient.withDataSource(DefaultRestoreClient.groovy:251) ~[stash-restore-client.jar:1.2.1]
... 5 more frames available in the log file

Cause

The jdbc.url in use is incorrect. Please make sure you specified all the parameters, specially the sessionVariables. If your default db engine (check with mysql> show engines;) is not InnoDB and this parameter is not specified on the JDBC URL, the exception above will be thrown. An example of the complete JDBC URL can be found in:

Restoring Stash into a newly created DB instance 

jdbc:mysql://HOSTNAME:PORT/DATABASE?autoReconnect=true&characterEncoding=utf8&useUnicode=true&sessionVariables=storage_engine%3DInnoDB

Resolution

That said, you should follow the steps below to fix this issue:

• Drop your current schema. The previous attempt will have inserted some data in there so you need to clean it up. From your jdbc.url, you called it stash, hence the command below will do if you're connected as root:

• Empty the directory you defined on stash.home inside your backup-config.properties for the same reason as you dropped the schema above.
  • stash.home=/var/atlassian/application-data/stash.. So:
  • rm /var/atlassian/application-data/stash/* will do the work.
• Recreate the database as defined on
  • Connecting Stash to MySQL

• Proceed with the restore again but with the correct JDBC URL this time around. Make sure this is there:

  sessionVariables=storage_engine%3DInnoDB