Importing XML site backup fails due to size limit for DESCRIPTION column in AO_9412A1_AONOTIFICATION table

Importing XML site backup from a PostgreSQL-based Confluence to a MySQL-based Confluence fails due to size limit for DESCRIPTION column in AO_9412A1_AONOTIFICATION table (maximum size limit is 65535). The Notification table in Confluence contains a JIRA comment notification that exceeds the maximum size limit, which only occurs when users receive a notification on the comments on watched JIRA issues.

Steps to reproduce

1. Set up JIRA 5.2 or above with Demo project and issues created
2. Set up Confluence behind PostgreSQL database
3. Ensure that the Application Links between JIRA and Confluence are established
4. Follow this documentation to configure Workbox Notifications. The following needs to be configured:
   • In-App Applications
   • Including notifications from JIRA
5. Create two users both in Confluence and JIRA (e.g. admin and test)
6. Login as admin, go to an issue in JIRA and watch the issue
7. Login as test:
   1. Go to the same issue in JIRA watched by admin
   2. Copy the whole content of 99999characters.txt file
   3. Paste this into a comment of the issue page
8. Ensure that the admin user receive the notification
9. Create a site XML backup for Confluence and then stop this instance
10. Set up a new Confluence instance behind MySQL database
11. Restore the XML backup

The restore does not complete with the following error:

The following appears in the log:

2012-12-11 20:13:15,751 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
 -- referer: http://localhost:8050/conf/setup/setup-restore-start.action | url: /conf/setup/setup-restore.action | userName: anonymous | action: setup-restore
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.1.2:
Could not import data in table 'AO_9412A1_AONOTIFICATION' column #6, value is too big for column which size limit is 65535, value is:
<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. 
Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. 
Donec pede justo, fringilla vel, aliquet nec, vulputate eget, arcu. 
In enim justo, rhoncus ut, imperdiet a, venenatis vitae, justo. Nullam dictum felis eu pede mollis pretium. Integer tincidunt. Cras dapibus. Vivamus elementum semper nisi. Aenean vulputate eleifend tellus. Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim. Aliquam lorem ante, dapibus in, viverra quis, feugiat a, tellus. Phasellus viverra nulla ut metus varius laoreet. Quisque rutrum. Aenean imperdiet. Etiam ultricies nisi vel augue. Curabitur ullamcorper ultricies nisi. Nam eget dui. Etiam rhoncus. Maecenas tempus, tellus eget condimentum rhoncus, sem quam semper libero, sit amet adipiscing sem neque sed ipsum. Nam quam nunc, blandit vel, luctus pulvinar, hendrerit id, lorem. Maecenas nec odio et ante tincidunt tempus. Donec vitae sapien ut libero 
.........

Workaround

1. Backup the Confluence database (PostgreSQL) of the original instance
2. Identify the notifications that exceed the size limit in the Confluence database (PostgreSQL):

   select char_length ("DESCRIPTION"), "ID" from "AO_9412A1_AONOTIFICATION" where char_length ("DESCRIPTION") > 65535 order by 1 DESC;

3. You can then run the SQL to update/clear these notifications:

   UPDATE "AO_9412A1_AONOTIFICATION" SET "DESCRIPTION" ='NULL' 
   WHERE "ID" IN (select "ID" from "AO_9412A1_AONOTIFICATION" where char_length ("DESCRIPTION") > 65535);

4. Export the XML site
5. Import it into MySQL-based Confluence instance

Possible cause

The max length data types for "String (clob)" as per this table don't hold the same max length across all the database types. In this case, for example:

• MySQL's TEXT can hold up to 65535 characters/bytes (reference)
• PostgreSQL's TEXT can hold up to 1GB (reference), that is 1073741824 bytes/characters

That explains, why TEXT-data-type columns in Postgres DB can hold bigger values which causes the data can't be imported to TEXT-data-type columns in MySQL. Perhaps we need to use different datatype in MySQL columns that could hold data up to 1GB, e.g. LONGTEXT?