Cannot create XML backup or access Plugin Data Storage

[Ben]

Jira Server version: 8.19

MySQL version: 5.7.42

OS: Windows Server 2012 R2 (Jira and MySQL)

I get the following error whenever I try to either create an XML backup or access the Plugin Data Storage area of the Jira UI:

java.lang.RuntimeException: Could not read fields for table ao_187ccc_sidebar_link
...

Caused by: java.sql.SQLSyntaxErrorException: Table 'jira.ao_187ccc_sidebar_link' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) [mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) [mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) [mysql-connector-j-8.0.33.jar:8.0.33]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972) [mysql-connector-j-8.0.33.jar:8.0.33]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) [commons-dbcp2-2.1.jar:2.1]
at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getResultSetMetaData(DatabaseMetaDataReaderImpl.java:271) [?:?]
at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getFields(DatabaseMetaDataReaderImpl.java:91) [?:?]

I don't understand why Jira is even looking for the table ao_187ccc_sidebar_link because that is a Confluence table. We do have a Confluence instance running on the same server and same MySQL instance. Jira is the auth provider for the Confluence server (using internal Jira user directory).

I have tried clearing plugin cache and restarting the server.

Please let me know if you can help.

[Ben · Accepted solution]

For anyone else who may have issues with this I am leaving the solution here:

Background

• We moved our MySQL instance from one server to another.
• We re-provisioned the users on the new instance, the grants were not the same; the Jira user on the new MySQL server had full grants (not just those required for the Jira database).

Solution

• Delete and re-create the user Jira uses to connect to the database, granting only the privileges required to manage its database.
• We did try to remove the permissions and re-grant on the existing user, but this did not work, only recreating the user worked for some reason.

[Robert Wen_ReleaseTEAM_]

hello @Ben 

I found the following KnowledgeBase article.

https://confluence.atlassian.com/jirakb/xml-backup-or-restore-fails-due-to-could-not-read-fields-for-table-317947954.html

[Ben]

Hi @Robert Wen_ReleaseTEAM_ I should have mentioned in my original post that I have already been through all the KB articles I could find, including the one linked.

That article is for MSSQL (not MySQL), and isn't relevant to this issue, though the error is indeed similar.

Another thing to note is that the referenced table in the KB article is in fact a Jira table, however the one in my issue is a Confluence table that Jira, for some reason, appears to be attempting to reference in its backup process.

This is also not a casing issue (there are other KB articles about issue related to `utf8` encoding), but our instance has correct encoding and charset values.

I have attached screenshots to help make this issue more clear.

Thank you, though!