Solution to Confluence migration problems (Data too long for column 'stringval ')

Migration steps

Let's take a rough look at the basic migration steps:

Create backup

Log on to Confluence using the administrator account, click the "gear" icon in the upper right corner, and select General Configuration
In the sidebar, select Backup & Restore
Select Archive to backups folder to Archive the generated backup to the Confluence automatic backup directory. If you do not click it, it will be generated in the temporary directory and will be automatically cleared 24 hours later.
Select Backup attachments to add the attachment content to the Backup.
Click the Backup button and wait until the Backup is complete.
System Recovery

In the newly installed Confluence, log in with the administrator account, click the "gear" icon in the upper right corner, and select General Configuration
In the sidebar, select Backup & Restore
Click Choose File and select the backup File created in the previous step in the pop-up box.
Click Upload and Restore to Upload and Restore
The basic steps are described above. If you do not have any problems, it is the best. If it fails, you can see an error similar to this in the log:

Com. atlassian. confluence. importexport. ImportExportException: Unable to complete import: Error while importing backup: cocould not insert: [com. atlassian. confluence. content. ContentProperty #32178794]
......
ERROR [Long running task: Importing data] [sf. hibernate. util. JDBCExceptionReporter] logExceptions Data truncation: Data too long for column 'stringval' at row 1
Note: The error message only intercepts important content, omitting some of the exception stack content.

This error is still in open state in JIRA, so it cannot be solved by upgrading. If you encounter a similar problem, continue to look down. If it is another error, check whether there are similar error causes: Troubleshooting XML backups that fail on restore

Solution

Since Confluence is not open-source and cannot contribute patches to it, it mainly analyzes and processes the backup files.

Error message analysis: we can find that the STRINGVAL field is too long when the database is inserted, and this field is located in the ContentProperty object.
View the CONTENTPROPERTY table in the database. The table structure is as follows. The STRINGVAL field length is 255.
+ -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------- + ------ + ----- + --------- + ------- +
| PROPERTYID | bigint (20) | NO | PRI | NULL |
| PROPERTYNAME | varchar (255) | NO | MUL | NULL |
| STRINGVAL | varchar (255) | YES | MUL | NULL |
| LONGVAL | bigint (20) | YES | MUL | NULL |
| DATEVAL | datetime | YES | MUL | NULL |
| CONTENTID | bigint (20) | YES | MUL | NULL |
+ -------------- + ------ + ----- + --------- + ------- +

Decompress the backup file and you can see that we have the following three items. By viewing the content, you can roughly locate the data source with import errors from entities. xml.

Entities. xml
ExportDescriptor. properties
Plugin-data

Let's look at the structure of entities. xml, which is roughly as follows:

<Object class = "ContentProperty" package = "com. atlassian. confluence. content">
<Id name = "id"> 1705394 </id>
<Property name = "name"> <! [CDATA [inline-original-selection]> </property>
<Property name = "stringValue"> <! [CDATA [XXXXXXX]> </property>
<Property name = "longValue"/> <property name = "dateValue"/>
</Object>

It can be roughly determined that the super-long field has the following characteristics: The property tag content of the Object label whose class attribute is ContentProperty and whose name is stringValue. Based on this feature, you can find ultra-long content through the following code.

File file = new File (getClass (). getResource ("/entities. xml"). getFile ());
SAXReader reader = new SAXReader ();
Document document = reader. read (file );
List <Element> list = document. getRootElement (). selectNodes ("// object [@ class = 'contentproperties']");
For (Element e: list ){
Element element = (Element) e. elements ("property"). get (1 );
String text = element. getText ();
If (text. length () & gt; 255 ){
System. out. println (text );
    }
}
After finding the corresponding content, you can delete some content and try to import it again. Readers can also make slight modifications based on the above code to generate new xml usage.
Postscript

Because the database defined field is 255, it is certainly 255 when the data is retrieved from the database. Therefore, it is assumed that something is added during the xml organization of the export. According to our exception data, the final excess part is exactly three points:.... I don't know if the problem is similar. Please leave a message to find out the specific cause. Recently, I was too busy. I had a rough decompilation and read some export content, but I didn't find the specific reason. I will take a look later.

In addition, this part of the confluence class is not in the confluence/WEB-INF/classes Directory, we have the desire to study the need to go to confluence/WEB-INF/lib to extract the confluence-5.x.x.jar, and obtain the decompilation, if you find the specific reason, please leave a message or email.