Table 'xxx' doesn't exist error in Confluence logs when using MySQL

Summary

The Confluence application logs report that one or more Confluence tables don't exist in MySQL database. This error can impact different features in Confluence, depending on the table affected by this error.

Environment

• Confluence Server or Data Center (any version)

• MySQL database

Diagnosis

A MySQLSyntaxErrorException is thrown in <home-directory>/logs/atlassian-confluence.log.

Here are a couple of examples.

First, this example shows the error as Confluence is trying to record the results of a scheduled task execution:

atlassian-confluence.log

Here's another example in which Confluence is unable to find certain key tables during startup:

In this particular case, similar errors will be found within the Tomcat Catalina log along with the following SEVERE errors:

Confirming the issue in MySQL:

Run the following SQL on your Confluence MySQL database:

You are affected by this issue if:

• Query 1 (upper case) fails and Query 2 (lower case) succeeds.

Cause

Either the table does not exist in the source database or the MySQL instance has case sensitive table names (as it should for Confluence).

Solution

1. Verify that the table(s) exist.

2. Shutdown Confluence

3. ⚠️Backup the Confluence database

4. Make sure the Confluence database user and database meet our recommendations that are outlined in Database Setup For MySQL.

5. Set the MySQL lower_case_table_names system variable to the value '0' followed by restarting MySQL and Confluence. This particularly applies to Unix based systems as this value should default to 0, per the note below.

6. Check that MySQL lower_case_table_names is set to 0 in Linux and 2 in Windows:

   1 show global variables like 'lower_case_table_names';

   Example SQL output...

   1 2 3 Variable_name Value ---------------------- ----- lower_case_table_names 0

   Windows Setting

   
   The lower_case_table_names setting can't be changed on a Windows server once it is installed.

7. Check the collation of the Confluence database

   1 2 -- Substitute <confluence_db_name> with your Confluence database name SELECT default_collation_name FROM information_schema.schemata WHERE schema_name = '<confluence_db_name>';

   Example SQL output...

   1 SELECT default_collation_name FROM information_schema.schemata WHERE schema_name = 'confluence';

   Example 1: utf8 collation expected output

   1 2 3 DEFAULT_COLLATION_NAME ---------------------- utf8_bin

   Example 2: utf8mb4 collation expected output

   1 2 3 DEFAULT_COLLATION_NAME ---------------------- utfmb4_bin

8. Generate the Table Rename SQL statements (no changes are made) by running the SQL corresponding to your above database collation:

   Please note, the query below will produce a series of RENAME TABLE statements, which you must then run against your database. 

   Run this SQL if your database collation is utf8...

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- For Collation utf8 -- Substitute <confluence_db_name> with your Confluence database name SELECT concat('RENAME TABLE ', TABLE_NAME, ' to ', UPPER(TABLE_NAME), ';') FROM INFORMATION_SCHEMA.TABLES where ( TABLE_NAME like 'ao_%' COLLATE utf8_bin or TABLE_NAME like 'mig_%' COLLATE utf8_bin or TABLE_NAME like 'denormalised_%' COLLATE utf8_bin or TABLE_NAME COLLATE utf8_bin in ( 'attachmentdata', 'attachments', 'auditrecord', 'audit_affected_object', 'audit_changed_value', 'bandana', 'bodycontent', 'clustersafety', 'confancestors', 'confversion', 'confzdu', 'content', 'contentproperties', 'content_label', 'content_perm', 'content_perm_set', 'content_relation', 'decorator', 'diagnostics_alerts', 'events', 'extrnlnks', 'follow_connections', 'imagedetails', 'indexqueueentries', 'keystore', 'label', 'likes', 'links', 'most_used_labels_cache', 'notifications', 'os_propertyentry', 'pagetemplates', 'plugindata', 'secrets', 'snapshots', 'spacepermissions', 'spacegrouppermissions', 'spacegroups', 'spaces', 'thiswillnotbecreated', 'trackbacklinks', 'trustedapp', 'trustedapprestriction', 'usercontent_relation', 'user_relation') ) and TABLE_SCHEMA = '<confluence_db_name>' order by TABLE_NAME;

   Run this SQL if your database collation is utf8mb4...

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- For Collation utf8mb4 -- Substitute <confluence_db_name> with your Confluence database name SELECT concat('RENAME TABLE ', TABLE_NAME, ' to ', UPPER(TABLE_NAME), ';') FROM INFORMATION_SCHEMA.TABLES where ( TABLE_NAME like 'ao_%' COLLATE utf8mb4_bin or TABLE_NAME like 'mig_%' COLLATE utf8mb4_bin or TABLE_NAME like 'denormalised_%' COLLATE utf8mb4_bin or TABLE_NAME in ( 'attachmentdata', 'attachments', 'auditrecord', 'audit_affected_object', 'audit_changed_value', 'bandana', 'bodycontent', 'clustersafety', 'confancestors', 'confversion', 'confzdu', 'content', 'contentproperties', 'content_label', 'content_perm', 'content_perm_set', 'content_relation', 'decorator', 'diagnostics_alerts', 'events', 'extrnlnks', 'follow_connections', 'imagedetails', 'indexqueueentries', 'keystore', 'label', 'likes', 'links', 'most_used_labels_cache', 'notifications', 'os_propertyentry', 'pagetemplates', 'plugindata', 'secrets', 'snapshots', 'spacepermissions', 'spacegrouppermissions', 'spacegroups', 'spaces', 'thiswillnotbecreated', 'trackbacklinks', 'trustedapp', 'trustedapprestriction', 'usercontent_relation', 'user_relation') ) and TABLE_SCHEMA = '<confluence_db_name>' order by TABLE_NAME;

   ℹ️ You may not get a row for every table mentioned above.

   ℹ️ The number of rows that are returned are dependent on your Confluence version and the Apps installed.

   ℹ️ If you get 0 rows returned, you are not affected by the issue described on this KB.

   Example output...

   1 2 3 4 5 RENAME TABLE ao_187ccc_sidebar_link to AO_187CCC_SIDEBAR_LINK; RENAME TABLE ao_21d670_whitelist_rules to AO_21D670_WHITELIST_RULES; RENAME TABLE ao_21f425_message_ao to AO_21F425_MESSAGE_AO; ... ..

9. For the above generated RENAME TABLE SQL statements, manually copy and paste your returned output and run against the Confluence database.

10. Once the RENAME TABLES are complete, start Confluence and the Table 'xxx' doesn't exist error should be resolved.

MySQL 5.7 Reference Guide

From: https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html:

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows, the default value is 1. On macOS, the default value is 2.