Problem when moving from the internal database to MySQL

[Tobias Herzner]

When we moved the data from the internal database to MySQL we followed this guide https://confluence.atlassian.com/doc/migrating-to-another-database-148867.html

This also has until the import of the XML into the new installation is working. During the import, however, we receive the message:

Error during import. For more information, see your server logs. com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not execute statement.

However, the logs under C: \ Program Files \ Atlassian \ Confluence \ logs are all empty except for Catalina. We could not find a solution through Google and the community. We hope you can help us.

Many Thanks

[Tobias Herzner]

I found the Problem. It was an Incorrect string value. Solution was this Article: https://confluence.atlassian.com/confkb/incorrect-string-value-error-thrown-when-restoring-xml-backup-in-confluence-777009599.html

Thanks a lot Shannon for your Support to find the correct Log an how i can find the error!

[SMS]

Tobias,

That's great news! Thank you for letting us know.

Take care, and have a pleasant week.

Regards,

Shannon

[SMS]

Hello Tobias,

Welcome to the Community! It's nice to meet you.

Can you just clarify exactly which issue you're having? The following is a bit unclear for me, and I think there might be a typo or some missing words:

This also has until the import of the XML into the new installation is working. During the import, however, we receive the message:

From my understanding, you've setup the new installation, and it's working, but when you attempt to import your XML site backup, this is where it fails. Can you confirm?

Please let us know your Confluence version, MySQL version. Also, can you let me know if you're staying on the same server/version when migrating your data or are if you are also performing an upgrade?

Lastly, I think you might also be looking in the wrong location for the Confluence Server Logs, so let's verify this.

Having a look at Working with Confluence Logs, 

Finding the Confluence log files

This section describes Confluence's default logging behavior, and assumes that you have not changed the destination of the logs. In order to unify logging across different application servers, Confluence uses the atlassian-confluence.log as its primary log, not the application server log.

• When you start Confluence, log entries will be sent to the application server logs until Confluence has completed its initial bootstrap. Any log entries written to the console will be repeated into the log in the Confluence home directory as described below.
• Once the initial startup sequence is complete, all logging will be to <confluence-home>/logs/atlassian-confluence.log. For example: c:/confluence/data/logs/atlassian-confluence.log.

Note that the default location is the Confluence home directory, not the application server's log file. The home directory is specified in <confluence-installation>/confluence/WEB-INF/classes/confluence-init.properties.

You can check the confluence-init.properties file in order to be sure of your home directory location. Once you find that, it should be in that directory, under logs/atlassian-confluence.log.

Looking forward to your reply! 

Regards,

Shannon

[Tobias Herzner]

Hi Shannon,

thank you for your quick response!

From my understanding, you've setup the new installation, and it's working, but when you attempt to import your XML site backup, this is where it fails. Can you confirm?

Thats rigth.

Please let us know your Confluence version, MySQL version. Also, can you let me know if you're staying on the same server/version when migrating your data or are if you are also performing an upgrade?

Confluence version is 6.15.1-x64
MySQL version is 5.7.26.0
Server is a Winows 7 Machine with no Upgrade at this Time.
The Trial of Confluence with the internal Database was on the same Server as the try to install a productive System.

You´ve right i serached on the wrong place for the log. Here is the right one from C:\Program Files\Atlassian\Application Data\Confluence\logs

Many Thanks for your support

Tobias

[Tobias Herzner]

Unfortunately, the log is apparently too big to be posted here :/

[SMS]

Hello Tobias,

Thank you for following up with those details. It's very helpful.

As for the log, you don't need to send me the whole file. What you can do is trigger the error message in the UI again, while you tail your Confluence Server logs. Then, you can send me the exact error that occurs in those logs at the same time as the error in the UI.

You can look for lines with the word ERROR in the beginning, and compare the timestamp to the time that you triggered the error. You can then include any follow-up lines after the error message. In particular, any lines that say "caused by" will have further details for us.

Let me know if you have any trouble with this.

Regards,

Shannon

[Tobias Herzner]

Hello Shannon,

here is the Log. I've croped it on the lines with Errormessages.

2019-05-08 20:19:17,973 ERROR [Long running task: Importing data] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Incorrect string value: '\xF0\x9F\x98\x8B</...' for column 'BODY' at row 1
-- url: /setup/longrunningtaskxml.action | referer: http://localhost:8090/setup/setup-restore-local.action | traceId: ed71d9c330861585 | userName: anonymous | action: longrunningtaskxml
2019-05-08 20:19:18,015 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.DefaultImportProcessor] persist Error while saving object: Imported[com.atlassian.confluence.core.BodyContent] [Id[id]=3113008, Property[body]=<p>Nicht vergessen, wir sind von 19.04. - 21.04. nicht da.</p><p>Sind in der Ferienwohnung alter Pfarrhof in M&ouml;nchsondheim.</p><p>Haben Charlotte dabei.</p>, Property[content]=com.atlassian.confluence.pages.BlogPost[Id[id]=2981939], Property[bodyType]=2]
-- url: /setup/longrunningtaskxml.action | referer: http://localhost:8090/setup/setup-restore-local.action | traceId: ed71d9c330861585 | userName: anonymous | action: longrunningtaskxml
2019-05-08 20:19:18,015 ERROR [Long running task: Importing data] [confluence.importexport.xmlimport.BackupImporter] importEntities Cannot import the entities: 
-- url: /setup/longrunningtaskxml.action | referer: http://localhost:8090/setup/setup-restore-local.action | traceId: ed71d9c330861585 | userName: anonymous | action: longrunningtaskxml
com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not execute statement

Caused by: org.xml.sax.SAXException: Error while importing backup: could not execute statement
net.sf.hibernate.exception.GenericJDBCException: could not execute statement

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8B</...' for column 'BODY' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3003)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3503)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at com.atlassian.hibernate.util.SessionHelper.flushAllowNoTransaction(SessionHelper.java:80)
... 59 more
2019-05-08 20:19:18,019 WARN [Long running task: Importing data] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
[com.atlassian.confluence.importexport.DefaultImportExportManager.performImport]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #143843201)
[com.atlassian.confluence.impl.schedule.caesium.SchedulerClusteredJobDao.delete]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #501365535)
->[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1646678432)
Showing 10 last transactions at this level out of 18 in total:
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #507312912)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1931764130)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1358908323)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1958308774)
[com.atlassian.confluence.upgrade.ddl.HibernateDdlExecutor.createCreateIndexCommand]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #268025618)
[com.atlassian.confluence.upgrade.ddl.HibernateDdlExecutor.createCreateIndexCommand]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #87972751)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #864268611)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1681721742)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #696854875)
[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #958627967)
-- url: /setup/longrunningtaskxml.action | referer: http://localhost:8090/setup/setup-restore-local.action | traceId: ed71d9c330861585 | userName: anonymous | action: longrunningtaskxml
2019-05-08 20:19:18,180 ERROR [Long running task: Importing data] [confluence.importexport.actions.ImportLongRunningTask] runInternal Failure during import
-- url: /setup/longrunningtaskxml.action | referer: http://localhost:8090/setup/setup-restore-local.action | traceId: ed71d9c330861585 | userName: anonymous | action: longrunningtaskxml
com.atlassian.confluence.importexport.ImportExportException: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not execute statement


Caused by: com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Error while importing backup: could not execute statement
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImportInternal(DefaultXmlImporter.java:71)
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImport(DefaultXmlImporter.java:49)
at com.atlassian.confluence.importexport.xmlimport.BackupImporter.importEntities(BackupImporter.java:397)
... 39 more
Caused by: org.xml.sax.SAXException: Error while importing backup: could not execute statement
net.sf.hibernate.exception.GenericJDBCException: could not execute statement
at com.atlassian.confluence.importexport.xmlimport.parser.BackupParser.endElement(BackupParser.java:45)
at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at com.atlassian.security.xml.RestrictedXMLReader.parse(RestrictedXMLReader.java:103)
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.parseBackup(DefaultXmlImporter.java:93)
at com.atlassian.confluence.importexport.xmlimport.DefaultXmlImporter.doImportInternal(DefaultXmlImporter.java:58)
... 41 more


Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8B</...' for column 'BODY' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3003)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3503)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
at com.atlassian.hibernate.util.SessionHelper.flushAllowNoTransaction(SessionHelper.java:80)
... 59 more

I hope that helps with troubleshooting

Many Thanks