Summary

When trying to create a new Space on Confluence, it fails with "An unknown server error has occurred" message in the UI, like this:

Checking the application logs, there may be an entry similar to this:

2020-08-06 18:14:58,143 WARN [http-nio-8090-exec-7] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
  ->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #799660109)
 -- referer: <Confluence Base URL>/index.action | url: /rest/create-dialog/1.0/space-blueprint/dialog/web-items | traceId: 274991252570b00e | userName: admin

Diagnosis

There are a number of issues that can cause symptoms similar to this.  If a MySQL database is being used by Confluence, please follow the steps below to validate whether Confluence is affected by this particular issue:

• Add  DEBUG logging  to the net.java.ao class and reproduce the problem.

• Check if this results in application log entries similar to this:

  2020-08-06 21:35:41,460 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT ID,CONTENT_ID,LAST_VIEW_DATE FROM AO_92296B_AORECENTLY_VIEWED WHERE USER_KEY = ? ORDER BY LAST_VIEW_DATE DESC LIMIT 1
  2020-08-06 21:35:41,495 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT DATE,ID,USER_KEY,PLUGIN_KEY,`KEY` FROM AO_6384AB_DISCOVERED WHERE USER_KEY = ?
  2020-08-06 21:35:41,503 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT ID,CONTENT FROM AO_21F425_MESSAGE_AO LIMIT 1
  2020-08-06 21:35:41,504 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT ID,CONTENT FROM AO_21F425_MESSAGE_AO LIMIT 1
  2020-08-06 21:35:41,511 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT message.ID,message.CONTENT FROM AO_21F425_MESSAGE_AO message JOIN AO_21F425_MESSAGE_MAPPING_AO message_mapping ON message.ID = message_mapping.MESSAGE_ID WHERE USER_HASH = ? OR USER_HASH = ? OR USER_HASH = ?
  2020-08-06 21:35:41,514 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT message.ID,message.CONTENT FROM AO_21F425_MESSAGE_AO message JOIN AO_21F425_MESSAGE_MAPPING_AO message_mapping ON message.ID = message_mapping.MESSAGE_ID WHERE USER_HASH = ? OR USER_HASH = ? OR USER_HASH = ?
  2020-08-06 21:35:44,839 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql SELECT NAME,PROMOTED_BPS,PLUGIN_CLONE,CATEGORY,ID,UUID,PLUGIN_MODULE_KEY FROM AO_54C900_SPACE_BLUEPRINT_AO
  2020-08-06 21:35:44,843 DEBUG [http-nio-8090-exec-7] [net.java.ao.sql] onSql INSERT INTO AO_54C900_C_TEMPLATE_REF (ID) VALUES (DEFAULT)
  2020-08-06 21:35:44,844 WARN [http-nio-8090-exec-7] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
    ->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #2070606288)
   -- referer: <Confluence Base URL> | url: /rest/create-dialog/1.0/space-blueprint/dialog/web-items | traceId: 44fbcdb84218459a | userName: admin

   Note:  The issue occurs right after Confluence tries an INSERT operation on one of the  AO tables.

• Checking the Confluence Health Check status shows a problem with the SQL Mode.
  

• The same can be found in the healthcheckResult.txt file from a Support Zip.

  Name: Server SQL Mode
  Is healthy: false
  Failure reason: Your SQL mode is set to NO_AUTO_VALUE_ON_ZERO. This is not a supported configuration.
  Severity: CRITICAL

  If none of these symptoms are found, please check the See Also section for other possible causes.

Cause

The MySQL instance is configured with the NO_AUTO_VALUE_ON_ZERO functionality enabled.  It's mandatory that this be disabled when configuring Confluence with MySQL.

Solution

1. Stop Confluence.
2. If necessary, pair with your DBA to confirm that the Confluence MySQL instance follows all recommended configurations in Database Setup For MySQL.
   1. At a minimum, ensure that the following is configured within the my.cnf or my.ini configuration file:
      
3. Restart the MySQL database.
4. Start Confluence.

See Also

Creating new page or space loads forever

Database Setup For MySQL

Unable to Add Comment Due to Enabled MySQL Mode NO_AUTO_VALUE_ON_ZERO

"Page Not Found" error when attempting to save a new page

Cannot Update Universal Plugin Manager (UPM) due to NO_AUTO_VALUE_ON_ZERO set on MySQL's sql_mode parameter