Table 'confluence.SPACEPERMISSIONS' doesn't exist

[Nils Bluemer]

After a server failure (both disks in a raid1 with severe errors) I had to recover from file backups and mysql dumps. This worked for JIRA 7, but not for Confluence 6 (which takes user credentials from Jira).

I tried an upgrade with the current Confluence Server file (6.3.1) which went through smoothly. However, I now get the error message (redirected from http://dmft.org:8090):

http://dmft.org:8090/errors.jsp "Oops - an error has occurred - Confluence"

System Error

A system error has occurred — our apologies!

For immediate troubleshooting, consult our knowledge base for a solution.

If you would like to receive support from Atlassian's support team, ask your Confluence administrator to create a support issue on Atlassian's support system with the following information:

1. a description of your problem and what you were doing at the time it occurred
2. a copy of the error and system information found below
3. a copy of the application logs (if possible).

Your Confluence administrator can use the support request form to create a support ticket which will include this information.

We will respond as promptly as possible.
Thank you!

Return to site homepage…

Cause

java.lang.reflect.UndeclaredThrowableException
    at com.sun.proxy.$Proxy97.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: Table 'confluence.SPACEPERMISSIONS' doesn't exist
    at sun.reflect.GeneratedConstructorAccessor305.newInstance(Unknown Source)

Stack Trace:[hide]

java.lang.reflect.UndeclaredThrowableException
	at com.sun.proxy.$Proxy97.permissionExists(Unknown Source)
	at com.atlassian.confluence.security.access.DefaultConfluenceAccessManager.hasGlobalPermissionAsUser(DefaultConfluenceAccessManager.java:122)
...	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
	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 com.atlassian.confluence.impl.security.SpacePermissionManagerFactory.lambda$new$0(SpacePermissionManagerFactory.java:70)
	... 306 more
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)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:366)
	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:313)
	at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateSpacePermissionDao.executeSpacePermissionQueryBySubject(HibernateSpacePermissionDao.java:135)
	at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateSpacePermissionDao.hasPermission(HibernateSpacePermissionDao.java:37)
	at com.atlassian.confluence.security.CachingSpacePermissionManager.findCachedPermissionOrFetchFromDao(CachingSpacePermissionManager.java:178)
	at com.atlassian.confluence.security.CachingSpacePermissionManager.permissionExists(CachingSpacePermissionManager.java:117)
	... 311 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2123)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
	at org.hibernate.loader.Loader.doQuery(Loader.java:932)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
	at org.hibernate.loader.Loader.doList(Loader.java:2615)
	at org.hibernate.loader.Loader.doList(Loader.java:2598)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
	at org.hibernate.loader.Loader.list(Loader.java:2425)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1458)
	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
	at com.atlassian.confluence.security.persistence.dao.hibernate.HibernateSpacePermissionDao.lambda$executeSpacePermissionQueryBySubject$3(HibernateSpacePermissionDao.java:153)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:359)
	... 316 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'confluence.SPACEPERMISSIONS' doesn't exist
	at sun.reflect.GeneratedConstructorAccessor305.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
	at com.mysql.jdbc.Util.getInstance(Util.java:360)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
	... 333 more

        

Referer URL

Unknown

[Nils Bluemer]

I found out that the MySQL dump (which contains tables in alphabetic order) was incomplete. I will try to recover from an earlier backup of /var/mysql.

[Deleted user]

Hi @Nils Bluemer ,

Could you please let us know if recovering from earlier backup worked for you?

We are facing same issue : We upgraded to confluence 6 from 5.8.X

Error : 

caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet     

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) caused by: org.postgresql.util.PSQLException: ERROR: column spacepermi0_.permalluserssubject does not exist Position: 211     
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)