Problem

Restoring an XML backup taken my SQL Server and importing into MySQL, fails at 90% with an error: 

Import failed. Check your server logs for more information. com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Confluence Space IA (Left Sidebar)(com.atlassian.confluence.plugins.confluence-space-ia) #9.4 (table AO_187CCC_SIDEBAR_LINK):Error executing update for SQL statement 'CREATE TABLE AO_187CCC_SIDEBAR_LINK ( CATEGORY VARCHAR(255), CUSTOM_ICON_CLASS VARCHAR(255), CUSTOM_TITLE VARCHAR(255), DEST_PAGE_ID BIGINT, HARDCODED_URL VARCHAR(255), HIDDEN BOOLEAN, ID INTEGER AUTO_INCREMENT, POSITION INTEGER, SPACE_KEY VARCHAR(255), TYPE VARCHAR(255), WEB_ITEM_KEY VARCHAR(255), ) ENGINE=InnoDB'

The following appears in the atlassian-confluence.log

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=InnoDB' at line 13

Diagnosis

Environment

• Export from MS SQL Server to MySQL

Cause

This error is due to missing primary keys on one or more of the AO tables. 

Workaround

1. Extract the backup zip file and open the file, activeObjectsBackupRestoreProvider.pdata, located in the plugin-data > com.atlassian.activeobjects.confluence.spi folder.
2. Locate the table definition for the table noted in the stack trace. In the example log snippet, the table name was 'AO_187CCC_SIDEBAR_LINK'.

3. Compare the table definition in your production database to the table definition in the .pdata file. Note the primary key column. For example (using the table above), in SQL Server, we can see that the primary key is on the ID column: 

   
   
   
   Looking at the table definition in activeObjectsBackupRestoreProvider.pdata, the ID column has the attribute: primaryKey="false"

   <table name="AO_187CCC_SIDEBAR_LINK">
       <column name="CATEGORY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="CUSTOM_ICON_CLASS" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="CUSTOM_TITLE" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="DEST_PAGE_ID" primaryKey="false" autoIncrement="false" sqlType="-5" precision="19"/>
       <column name="HARDCODED_URL" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="HIDDEN" primaryKey="false" autoIncrement="false" sqlType="-7" precision="1"/>
       <column name="ID" primaryKey="false" autoIncrement="true" sqlType="4" precision="10"/>
       <column name="POSITION" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/>
       <column name="SPACE_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="TYPE" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
       <column name="WEB_ITEM_KEY" primaryKey="false" autoIncrement="false" sqlType="12" precision="255"/>
     </table>

4. For the column identified as the primary key, update primaryKey="false" to primaryKey="true".
5. Save the file and re-zip.  Do not use Windows ZIP compression to compress the XML backup. Use 7Zip or WinZip instead.
6. Re-import the XML backup. 

NOTE: You may need to repeat this process if multiple tables are identified.