Importing XML site backup from SQL Server-based or PostgreSQL-based Confluence to MySQL-based Confluence fails due to size limit for DATA column in AO_9412A1_AOREGISTRATION table

Summary of the issue

Importing XML site backup from a SQL Server-based or PostgreSQL-based Confluence to a MySQL-based Confluence fails due to size limit for DATA column in AO_9412A1_AOREGISTRATION table (maximum size limit is 65535).

In SQL Server-based or PostgreSQL-based Confluence database, DATA column in AO_9412A1_AOREGISTRATION table datatype is set to NTEXT and TEXT respectively. These 2 datatypes can hold string up to 1GB or 1073741823 bytes/characters.

In the other hand, it seems that in the Active Object, the biggest string-based data type for MySQL is TEXT. Which could only hold data up to 65535 characters/bytes. Therefore, any data/value in TEXT/NTEXT-based column that contains more than 65535 characters/bytes in the SQL Server-based or PostgreSQL-based Confluence database, would fail/skipped during the import to MySQL-based Confluence.

The following appears in the log:

2012-12-27 15:03:02,109 WARN [Long running task: Importing data] [activeobjects.confluence.backup.LoggingRestoreProgressMonitor] beginRestore Begin restoring Active Objects backup, adjust log level for com.atlassian.activeobjects.confluence.backup for more detailed logging.
 -- referer: http://localhost:8090/admin/backup.action | url: /admin/restore-local-file.action | userName: admin | action: restore-local-file
2012-12-27 15:03:04,360 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
 -- referer: http://localhost:8090/admin/backup.action | url: /admin/restore-local-file.action | userName: admin | action: restore-local-file
com.atlassian.confluence.importexport.ImportExportException: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Notifications and Tasks - Host Plugin(com.atlassian.mywork.mywork-confluence-host-plugin) #1.0.3:Could not import data in table 'AO_9412A1_AOREGISTRATION' column #1, value is too big for column which size limit is 65535, value is:
{<A very long value is here>}

	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:39)
	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importPluginData(FileBackupImporter.java:353)
	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:134)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:151)
	at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
	at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:143)
	at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:75)
	at com.atlassian.confluence.importexport.DefaultImportExportManager.doImport(DefaultImportExportManager.java:67)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy102.doImport(Unknown Source)
	at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:81)
	at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
	at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:35)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
Caused by: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Notifications and Tasks - Host Plugin(com.atlassian.mywork.mywork-confluence-host-plugin) #1.0.3:Could not import data in table 'AO_9412A1_AOREGISTRATION' column #1, value is too big for column which size limit is 65535, value is:
{<A very long value is here>}

	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportException(ImportExportErrorServiceImpl.java:24)
	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setString(DataImporter.java:363)
	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setValue(DataImporter.java:409)
	at com.atlassian.dbexporter.importer.DataImporter.importTable(DataImporter.java:123)
	at com.atlassian.dbexporter.importer.DataImporter.access$000(DataImporter.java:33)
	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:73)
	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:62)
	at com.atlassian.dbexporter.jdbc.JdbcUtils.withConnection(JdbcUtils.java:31)
	at com.atlassian.dbexporter.importer.DataImporter.doImportNode(DataImporter.java:61)
	at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:49)
	at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:73)
	at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:158)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy338.restore(Unknown Source)
	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:35)
	... 28 more

Workaround

Rather than importing the Site XML backup during setup wizard, install a fresh MySQL-based Confluence. Then go to Confluence Admin > Backup & Restore > Restore the site XML backup. The import should finish successful, but the affected data/rows will be skipped and not imported to the new Confluence database.

References

• https://developer.atlassian.com/display/AO/AO+0.18.x+Upgrade+Guide#AO0.18.xUpgradeGuide-TypeChanges
• http://www.electrictoolbox.com/maximum-length-mysql-text-field-types/
• http://msdn.microsoft.com/en-us/library/ms187993.aspx
• http://stackoverflow.com/questions/10395975/maximum-size-of-text-datatype-in-postgresql
• Same log with DEBUG logging enabled on:
  com.atlassian.activeobjects.confluence.backup
  com.atlassian.dbexporter
  net.java.ao.sql

  ....
  2012-12-27 19:40:43,596 DEBUG [Long running task: Importing data] [net.java.ao.sql] executeUpdate DROP TABLE AO_9412A1_AOREGISTRATION
  ....
  2012-12-27 19:40:44,818 INFO [Long running task: Importing data] [activeobjects.confluence.backup.LoggingRestoreProgressMonitor] beginTableCreationRestore Begin table creation for : AO_9412A1_AOREGISTRATION
  2012-12-27 19:40:44,820 DEBUG [Long running task: Importing data] [net.java.ao.sql] executeUpdate CREATE TABLE AO_9412A1_AOREGISTRATION (
      DATA TEXT,
      ID VARCHAR(255),
      UPDATED DATETIME,
      PRIMARY KEY(ID)
  ) ENGINE=InnoDB
  2012-12-27 19:40:44,908 INFO [Long running task: Importing data] [activeobjects.confluence.backup.LoggingRestoreProgressMonitor] endTableCreationRestore Completed table creation for : AO_9412A1_AOREGISTRATION
  ....
  2012-12-27 19:40:45,291 INFO [Long running task: Importing data] [activeobjects.confluence.backup.LoggingRestoreProgressMonitor] beginTableDataRestore Begin restoring table data for : AO_9412A1_AOREGISTRATION
  2012-12-27 19:40:45,403 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
   -- referer: http://localhost:8090/admin/backup.action | url: /admin/restore-local-file.action | userName: admin | action: restore-local-file
  com.atlassian.confluence.importexport.ImportExportException: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Notifications and Tasks - Host Plugin(com.atlassian.mywork.mywork-confluence-host-plugin) #1.0.3:Could not import data in table 'AO_9412A1_AOREGISTRATION' column #1, value is too big for column which size limit is 65535, value is:
  {<A very long value is here>}}
  
  	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:39)
  	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importPluginData(FileBackupImporter.java:353)
  	at com.atlassian.confluence.importexport.xmlimport.FileBackupImporter.importEverything(FileBackupImporter.java:134)
  	at com.atlassian.confluence.importexport.xmlimport.BackupImporter$1.doInTransactionWithoutResult(BackupImporter.java:151)
  	at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)
  	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
  	at com.atlassian.confluence.importexport.xmlimport.BackupImporter.doImportInternal(BackupImporter.java:143)
  	at com.atlassian.confluence.importexport.Importer.doImport(Importer.java:75)
  	at com.atlassian.confluence.importexport.DefaultImportExportManager.doImport(DefaultImportExportManager.java:67)
  	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
  	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
  	at java.lang.reflect.Method.invoke(Method.java:597)
  	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
  	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  	at $Proxy81.doImport(Unknown Source)
  	at com.atlassian.confluence.importexport.actions.ImportLongRunningTask.runInternal(ImportLongRunningTask.java:81)
  	at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
  	at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:35)
  	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
  	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
  	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
  	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
  	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
  	at java.lang.Thread.run(Thread.java:662)
  Caused by: com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Notifications and Tasks - Host Plugin(com.atlassian.mywork.mywork-confluence-host-plugin) #1.0.3:Could not import data in table 'AO_9412A1_AOREGISTRATION' column #1, value is too big for column which size limit is 65535, value is:
  {<A very long value is here>}
  
  	at com.atlassian.activeobjects.backup.ImportExportErrorServiceImpl.newImportExportException(ImportExportErrorServiceImpl.java:24)
  	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setString(DataImporter.java:363)
  	at com.atlassian.dbexporter.importer.DataImporter$BaseInserter.setValue(DataImporter.java:409)
  	at com.atlassian.dbexporter.importer.DataImporter.importTable(DataImporter.java:123)
  	at com.atlassian.dbexporter.importer.DataImporter.access$000(DataImporter.java:33)
  	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:73)
  	at com.atlassian.dbexporter.importer.DataImporter$1.call(DataImporter.java:62)
  	at com.atlassian.dbexporter.jdbc.JdbcUtils.withConnection(JdbcUtils.java:31)
  	at com.atlassian.dbexporter.importer.DataImporter.doImportNode(DataImporter.java:61)
  	at com.atlassian.dbexporter.importer.AbstractImporter.importNode(AbstractImporter.java:49)
  	at com.atlassian.dbexporter.DbImporter.importData(DbImporter.java:73)
  	at com.atlassian.activeobjects.backup.ActiveObjectsBackup.restore(ActiveObjectsBackup.java:158)
  	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
  	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
  	at java.lang.reflect.Method.invoke(Method.java:597)
  	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
  	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
  	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
  	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
  	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
  	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
  	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
  	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  	at $Proxy325.restore(Unknown Source)
  	at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.restore(ActiveObjectsBackupRestoreProvider.java:35)
  	... 28 more
  ....
  

• home/halatas/src/atlassian/plugins/activeobjects/activeobjects-plugin-0.19.12-sources/net/java/ao/types/TypeManager.java

  ....
      public static TypeManager derby()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("BLOB"))
                  .addMapping(booleanType(), schemaType("SMALLINT").jdbcWriteType(TINYINT).precisionAllowed(true), qualifiers().precision(1))
                  .addMapping(dateType(), schemaType("DATETIME"))
                  .addMapping(doubleType(), schemaType("DOUBLE"))
                  .addMapping(integerType(), schemaType("INTEGER"))
                  .addMapping(longType(), schemaType("BIGINT"))
                  .addStringTypes("VARCHAR", "CLOB")
                  .build();
      }
  
      public static TypeManager hsql()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("LONGVARBINARY"))
                  .addMapping(booleanType(), schemaType("BOOLEAN"))
                  .addMapping(dateType(), schemaType("DATETIME"))
                  .addMapping(doubleType(), schemaType("DOUBLE"))
                  .addMapping(integerType(), schemaType("INTEGER"))
                  .addMapping(longType(), schemaType("BIGINT"))
                  .addStringTypes("VARCHAR", "LONGVARCHAR")
                  .build();
      }
  
      public static TypeManager mysql()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("BLOB"))
                  .addMapping(booleanType(), schemaType("BOOLEAN"))
                  .addMapping(dateType(), schemaType("DATETIME"))
                  .addMapping(doubleType(), schemaType("DOUBLE"))
                  .addMapping(integerType(), schemaType("INTEGER"))
                  .addMapping(longType(), schemaType("BIGINT"))
                  .addStringTypes("VARCHAR", "TEXT")
                  .build();
      }
  
      public static TypeManager postgres()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("BYTEA"))
                  .addMapping(booleanType(), schemaType("BOOLEAN"))
                  .addMapping(dateType(), schemaType("TIMESTAMP"))
                  .addMapping(doubleType(), schemaType("DOUBLE PRECISION"))
                  .addMapping(integerType(), schemaType("INTEGER"))
                  .addMapping(longType(), schemaType("BIGINT"))
                  .addStringTypes("VARCHAR", "TEXT")
                  .build();
      }
  
      public static TypeManager sqlServer()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("IMAGE"))
                  .addMapping(booleanType(), schemaType("BIT"))
                  .addMapping(dateType(), schemaType("DATETIME"))
                  .addMapping(doubleType(), schemaType("FLOAT"))
                  .addMapping(integerType(), schemaType("INTEGER"))
                  .addMapping(longType(), schemaType("BIGINT"))
                  .addStringTypes("VARCHAR", "NTEXT")
                  .build();
      }
  
      public static TypeManager oracle()
      {
          return new TypeManager.Builder()
                  .addMapping(blobType(), schemaType("BLOB"))
                  .addMapping(booleanType(), schemaType("NUMBER").precisionAllowed(true), qualifiers().precision(1))
                  .addMapping(dateType(), schemaType("TIMESTAMP"))
                  .addMapping(doubleType(), schemaType("DOUBLE PRECISION"))
                  .addMapping(integerType(), schemaType("NUMBER").precisionAllowed(true), qualifiers().precision(11))
                  .addMapping(longType(), schemaType("NUMBER").precisionAllowed(true), qualifiers().precision(20))
                  .addStringTypes("VARCHAR", "CLOB")
                  .build();
      }
  ....
  

• /home/halatas/src/atlassian/plugins/activeobjects/activeobjects-plugin-0.19.12-sources/com/atlassian/dbexporter/importer/DataImporter.java line#352-367

  ...
          private void setString(String value) throws SQLException
          {
              if (value == null)
              {
                  ps.setNull(col, Types.VARCHAR);
              }
              else
              {
                  int maxSize = maxColumnSize.get(col);
                  if (maxSize != -1 && value.length() > maxSize)
                  {
                      throw errorService.newImportExportException(tableName, "Could not import data in table '" + tableName + "' column #" + col + ", value is too big for column which size limit is " + maxSize + ", value is:\n" + value + "\n");
                  }
                  ps.setString(col, value);
              }
          }
  ...