"java.sql.SQLException: Index column size too large" error after upgrading Confluence and MySQL

[Peter Murray]

After upgrading Confluence to 7.8.0 and MySQL from 5.7.x to 8.x, we are seeing this error in our logs.

ERROR [http-nio-127.0.0.1-8090-exec-93] [plugin.descriptor.web.ConfluenceWebInterfaceManager] getHtml Failed to render web panel: com.atlassian.plugin.web.descriptors.DefaultWebPanelModuleDescriptor$ContextAwareWebPanel@1d4e865b
 -- url: /display/DD/Decision+log | traceId: 4f913c631079d71d | userName: malc
com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.atlassian.confluence.plugins.confluence-feature-discovery-plugin]
...
Caused by: java.lang.RuntimeException: Could not read fields for table AO_6384AB_DISCOVERED
...
Caused by: java.sql.SQLException: Index column size too large. The maximum column size is 767 bytes.

We are seeing this error when attempting to create a new page from a blueprint and when trying to create a new space.  This type of error seems to be related to Unicode handling in the database.  I have used the directions on How to Fix the Collation and Character Set of a MySQL Database manually (twice!) and there are no additional databases, tables, or columns that need to have their metadata changed. 

We are using AWS for hosting—dedicated EC2 and MySQL RDS instances for Confluence. The complete runbook for the upgrade is posted on our Jira instance. (It includes the steps in the "Fix the Collation" document above.)

Our local issue tracking this problem is FOLIO-2867.  It has the troubleshooting steps we've tried so far. Are there suggestions for things we can try to fix the issue?

[Daniel Ebers]

Hi Peter,

first of all I am thrilled by the documentation you did during troubleshooting - you present the runbook along with your issue in a public issuetracker, whew that far beyond the standard procedure.

Unfortunately this is maybe the best part of my answer. I will try two different questions anyways - accept my apologies as this is just trivial.

1.) Could you please confirm on the version of the JDBC driver?
I was not able to identify the exact version from runbook. Although very unlikely could you please double check if the version matches against the Confluence version 7.8.0
From my understanding the version 8.0.21 of JDBC driver is to be used with that Confluence version.

2.) This might look like the big bang and comes to a price, even if it succeeds, but...
... have to tried to export data via XML export and restore it to a (non-production!) Confluence server.
Documentation as per: https://confluence.atlassian.com/doc/manually-backing-up-the-site-152405.html
I know, in case of a complex site this will most likely fail but it could be a pointer if something basic is broken or if it is related to database.
Sadly, even with an output this would only shed some light on where to go next.

So, overall, no answers like >press this single button and everything will be fine< - but probably somebody else in here will contribute something?

Cheers,
Daniel

[Peter Murray]

Thank you for the two tips, Daniel!  I thought for sure the first one might have been the issue.  I ran `find /confluence -name "mysql-connector*" -print` to see what connectors were in the installation.  I found `confluence/WEB-INF/lib/mysql-connector-java-8.0.21.jar` as expected, but also found `confluence/mysql-connector-java-5.1.44-bin.jar` (root level of the Confluence installation).  But removing that MySQL 5.1.44  connector from the root level of the installation and restarting didn't fix the issue.  (I reran the How to Fix the Collation and Character Set of a MySQL Database manually SQL commands just to be sure there wasn't a lingering effect.)

The second one is more complex, and the errors generated seem to point to a Unicode character set problem.  I stopped the Nginx proxy on the server to make sure I was the only one accessing Confluence, then set up an ssh tunnel to talk directly to tomcat (`ssh -N -L 8090:127.0.0.1:8090 wiki.folio.org` — http://localhost:8090/admin/backup.do).  I tried all four combinations of the "Include attachments" and "Also save a copy to the Backups directory" options.  Each time the backup ran about 12 minutes before getting an error returned in the HTML from the server:

com.atlassian.confluence.importexport.ImportExportException: java.lang.RuntimeException: Could not read fields for table AO_54C900_C_TEMPLATE_REF
at com.atlassian.activeobjects.confluence.backup.ActiveObjectsBackupRestoreProvider.backup(ActiveObjectsBackupRestoreProvider.java:30)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupPluginData(FileXmlExporter.java:215)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupEverything(FileXmlExporter.java:109)
at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.doExport(AbstractXmlExporter.java:94)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExportInternal(FileXmlExporter.java:60)
at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExport(FileXmlExporter.java:54)
at com.atlassian.confluence.importexport.DefaultImportExportManager.doExport(DefaultImportExportManager.java:212)
at com.atlassian.confluence.importexport.DefaultImportExportManager.exportAs(DefaultImportExportManager.java:178)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
....
Caused by: java.lang.RuntimeException: Could not read fields for table AO_54C900_C_TEMPLATE_REF
at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getFields(DatabaseMetaDataReaderImpl.java:141)
at net.java.ao.schema.ddl.SchemaReader.readFields(SchemaReader.java:126)
at net.java.ao.schema.ddl.SchemaReader.readTable(SchemaReader.java:110)
at net.java.ao.schema.ddl.SchemaReader.access$000(SchemaReader.java:62)
at net.java.ao.schema.ddl.SchemaReader$1.apply(SchemaReader.java:99)
at net.java.ao.schema.ddl.SchemaReader$1.apply(SchemaReader.java:97)
at com.google.common.collect.Iterators$6.transform(Iterators.java:785)
....
Caused by: java.sql.SQLException: Index column size too large. The maximum column size is 767 bytes.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)
at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getResultSetMetaData(DatabaseMetaDataReaderImpl.java:271)
at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getFields(DatabaseMetaDataReaderImpl.java:91) 
... 438 more 

The errors logged in `logs/atlassian-confluence.log` were over 2000 lines, but I condensed what looked like the critical lines from stack traces and posted them in a Gist: https://gist.github.com/dltj/c0d0e1e70b00f28c0582dd2e7dc0dc0c

No backup file was produced—it wasn't in the `confluence` user home directory and `find /atlassian -mmin -60 -type f -exec ls -l {} \;` (list any files that have changed in the last 60 minutes on the Confluence installation and runtime partition) didn't show anything that looked like a backup or partial backup.

The table names (`AO_54C900_C_TEMPLATE_REF` and `AO_6384AB_DISCOVERED`) look like generated names—is there a way to figure out what is responsible for those tables?  If it is a plugin, I can try disabling to plugin to see if I can get farther.

[Daniel Ebers]

I have not been able to figure out what AO_54C900_C_TEMPLATE_REF & AO_6384AB_DISCOVERED are especially for.
There are very strong indicators they come from core Confluence, not through an App.
But at least there has been a bug report in the past where AO_54C900_C_TEMPLATE_REF was involved.

One can read from the Bug that this would be a "table that can grow quite large".
https://jira.atlassian.com/browse/CONFSERVER-58513

One guess would be that it comes to a timeout (as of the size) but they would be the question where a timeout should originate from - or in other words: why the connection to the database does not keep until the error messages pop up.

Also, despite it could be red herring, it *could* be related to the index that was introduced later (the bug report says version 7.0.1) - when looking at the error from your first post again

Caused by: java.sql.SQLException: Index column size too large. The maximum column size is 767 bytes.

it is at least referring to the size of the index column...
The hints found to mitigate that vary much and I am not confident to recommend "that one" command you should run against your database now.
Rather on the contrary I believe this now needs somebody who exactly knows the database structure from Confluence (with all of its specialities) and/or further debugging by a database specialist.

[Peter Murray]

Thanks for your additional thoughts, Daniel. I've logged an issue with Atlassian Support to see if they can offer a suggestion.