How to solve MySQLSyntaxErrorException: Unknown column 'spacepermi0_.xxx' in field list

[Debayan Saha]

while upgrading from 5.0.3 to 6.15.9, after setting character encode and collation, we checked there's no bad collation settings on our database.We are using trial license and database name is also fine.Still we are getting below error in confluence:

Cause

java.lang.reflect.UndeclaredThrowableException
    at com.sun.proxy.$Proxy192.permissionExists(Unknown Source)

caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:224)

caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)

caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'spacepermi0_.PERMALLUSERSSUBJECT' in 'field list'
    at sun.reflect.GeneratedConstructorAccessor305.newInstance(Unknown Source)

Stack Trace:[hide]

java.lang.reflect.UndeclaredThrowableException
	at com.sun.proxy.$Proxy192.permissionExists(Unknown Source)
	at com.atlassian.confluence.security.access.DefaultConfluenceAccessManager.hasGlobalPermissionAsUser(DefaultConfluenceAccessManager.java:127)
	at com.atlassian.confluence.security.access.DefaultConfluenceAccessManager.anonymousCanUseConfluence(DefaultConfluenceAccessManager.java:103)
	at com.atlassian.confluence.security.access.DefaultConfluenceAccessManager.getAccessStatusForAnonymous(DefaultConfluenceAccessManager.java:63)
	at com.atlassian.confluence.security.access.DefaultConfluenceAccessManager.getUserAccessStatusNoExemptions(DefaultConfluenceAccessManager.java:55)
	at com.atlassian.confluence.security.access.CachingConfluenceAccessManager.getUserAccessStatusNoExemptions(CachingConfluenceAccessManager.java:38)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy191.getUserAccessStatusNoExemptions(Unknown Source)
	at com.atlassian.confluence.security.DefaultPermissionManager.canUseConfluence(DefaultPermissionManager.java:140)
	at com.atlassian.confluence.security.DefaultPermissionManager.hasPermissionNoExemptions(DefaultPermissionManager.java:92)
	at com.atlassian.confluence.security.DefaultPermissionManager.hasPermission(DefaultPermissionManager.java:48)
	at com.atlassian.confluence.security.DefaultPermissionManager.isSystemAdministrator(DefaultPermissionManager.java:230)
	at sun.reflect.GeneratedMethodAccessor417.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	

[Kurt Klinner]

@Debayan Saha 

Hi Debayan

welcome to the Atlassian community

assuming that you followed the steps described in 

https://confluence.atlassian.com/confkb/unknown-column-error-when-upgrading-from-confluence-5-x-to-newer-versions-915476193.html

could you share the results of the sql queries

Cheers

Kurt

[Debayan Saha]

Hi Kurt,

We have run the below queries.

USE information_schema;

SELECT schema_name, default_character_set_name, default_collation_name FROM schemata WHERE schema_name = 'confluence' 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');

and found below result:

Empty set (0.00 sec) which implies there is no bad collation in the database.

We are trying to incrementally update confluence from version 3.3 to version 6.15 because we are trying to export space from confluence(version we are using is 3.3) which will be imported to a confluence whose version is 6.15.That's why this upgradation is required.And we have successfully upgraded till 5.0.3. Now as we are trying to upgrade to confluence 6.15, we are facing this issue.

Please provide us with some solution if there is any.

[Kurt Klinner]

@Debayan Saha 

Just to be on the safe side you adjusted the schema/database in your query reflecting your db - right?

Cheers

Kurt

[Debayan Saha]

Yes Kurt.We have adjusted the schema/database in our query reflecting our db and the database name is confluence