Unknown column 'this.external_id' when upgrading to Confluence 5.2.5 or above on MySQL
Symptoms
When upgrading to Confluence 5.2.5 or above, the following appears in atlassian-confluence.log:
2013-11-25 16:03:57,598 ERROR [http-8081-9] [[Standalone].[localhost].[/].[noop]] log Servlet.service() for servlet noop threw exception
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Unable to perform find; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this.external_id' in 'field list'
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.hibernate.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:364)
at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:351)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUser(HibernateUserDao.java:474)
at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindByName(HibernateUserDao.java:451)
at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.findByName(HibernateUserDao.java:431)
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this.external_id' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1051)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3495)
...Diagnosis
You can detect the cause for this using the below SQL statements, replacing <DATABASE_NAME> in each:
Make sure to set MySQL to use the information_schema DB to make the queries below work. Use this command "Use information_schema" before and replace <DATABASE_NAME> with confluence database name you want to check.
SELECT schema_name, default_character_set_name, default_collation_name FROM schemata WHERE schema_name = '<DATABASE_NAME>' AND (default_character_set_name <> 'utf8' OR default_collation_name <> 'utf8_bin');
SELECT table_name, table_collation, engine FROM tables WHERE table_schema = '<DATABASE_NAME>' AND table_collation <> 'utf8_bin';
SELECT table_name, column_name, character_set_name, collation_name FROM columns WHERE table_schema = '<DATABASE_NAME>' and data_type in ('varchar', 'text') and (character_set_name <> 'utf8' OR collation_name <> 'utf8_bin');If any rows are returned in any of those three queries, then there is something in the Confluence database which is either not set to use utf8 encoding, or does not have the utf8_bin collation.
Cause
At least one object in the Confluence database does not have the utf8 encoding and/or utf8_bin collation, as described in Database Setup For MySQL.
Resolution
Follow the directions given at How to Fix the Collation and Character Set of a MySQL Database