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

Related content

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

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 
2020-07-31 07:14:09,752 ERROR [Caesium-1-1] [scheduler.caesium.impl.SchedulerQueueWorker] executeJob Unhandled exception thrown by job QueuedJob[jobId=synchronyStatusCheck,deadline=1596179649748]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [DELETE FROM scheduler_run_details WHERE start_time < ?]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence2.scheduler_run_details' doesn't exist
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
...
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence2.scheduler_run_details' doesn't exist
	at sun.reflect.GeneratedConstructorAccessor307.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
	...
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)

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

...
2021-09-24 00:35:37,236 WARN [Catalina-utility-1] [confluence.impl.setup.DefaultBootstrapDatabaseAccessor] readValue Unable to determine build number from database. If you are upgrading from a Confluence version prior to 2.3, this is expected.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence.CONFVERSION' doesn't exist
...

2021-09-24 00:36:11,386 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.BandanaPluginStateStore] load Exception when loading plugins state from the database. Falling back to in memory state.
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence.BANDANA' doesn't exist

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

24-Sep-2021 00:38:15.042 SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.startInternal One or more Filters failed to start. Full details will be found in the appropriate container log file
24-Sep-2021 00:38:15.043 SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.startInternal Context [] startup failed due to previous errors
WARNING: Cannot update an inactive framework.

Confirming the issue in MySQL:

Run the following SQL on your Confluence MySQL database:

-- Query 1: table name in capital letters
select * from CONFVERSION;

-- Query 2: table name in lower case letters
select * from confversion;


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. (warning) 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:

    show global variables like 'lower_case_table_names';
    Example SQL output... 
    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

    -- 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... 
    SELECT default_collation_name FROM information_schema.schemata WHERE schema_name = 'confluence';
    Example 1: utf8 collation expected output 
    DEFAULT_COLLATION_NAME
----------------------
utf8_bin
    Example 2: utf8mb4 collation expected output 
    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... 
    -- 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... 
    -- 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;

    (info) You may not get a row for every table mentioned above.
    (info) The number of rows that are returned are dependent on your Confluence version and the Apps installed.
    (info) If you get 0 rows returned, you are not affected by the issue described on this KB.

    Example output... 
    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.


If the MySQL server is used by different Atlassian applications, the modification of the lower_case_table_names system variable could cause issues for those other applications.  For instance, while this Fisheye KB mentions setting this value to 0, Bamboo MySQL Configuration guide recommends setting this to 1.

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.

ValueMeaning
0Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as for lower_case_table_names=1.



Last modified on May 1, 2023

Was this helpful?

Yes
No
Provide feedback about this article

Related content

Powered by Confluence and Scroll Viewport.