Unknown column 'this.external_id' when upgrading to Confluence 5.2.5 or above on MySQL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Symptoms
When upgrading to Confluence 5.2.5 or above, the following appears in atlassian-confluence.log:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.
1
2
3
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.
Solution
Resolution
Follow the directions given at How to Fix the Collation and Character Set of a MySQL Database
Was this helpful?