Unable to Create a New Space
Symptoms
Using MySQL for a database with the MyISAM engine, users are unable to create a new space.
The following appears in the atlassian-confluence.log:
2010-12-15 13:41:31,324 ERROR [http-8080-15] [atlassian.confluence.event.ConfluenceEventManager] publishEvent An exception was encountered while processing the event: com.atlassian.confluence.event.events.space.SpaceCreateEvent[source=com.atlassian.confluence.spaces.DefaultSpaceManager@79e9532c]
-- url: /spaces/createspace.action | userName: admin | referer: http://confluenceurl/spaces/createspace-start.action | action: createspace
org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.security.SpacePermission#100728854]; SQL []; Duplicate entry 'admin' for key 'sp_puname_idx'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'admin' for key 'sp_puname_idx'
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'admin' for key 'sp_puname_idx'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
The atlassian-confluence.log also contains an error similar to the following:
2010-12-16 02:35:55,031 ERROR [http-8080-15] [atlassian.confluence.security.DefaultSpacePermissionManager] hasPermission Error checking permission (). Denying access.
-- url: /dashboard.action | userName: admin | referer: http://confluenceurl
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute query; uncategorized SQLException for SQL []; SQL state [HY000]; error code [126]; Incorrect key file for table './confluence/SPACEPERMISSIONS.MYI'; try to repair it; nested exception is java.sql.SQLException: Incorrect key file for table './confluence/SPACEPERMISSIONS.MYI'; try to repair it
Caused by: java.sql.SQLException: Incorrect key file for table './confluence/SPACEPERMISSIONS.MYI'; try to repair it
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3495)
Cause 1
A MySQL server crash can cause the database indices to be incorrect. This usually happens when using MyISAM engine.
Cause 2
The NO_AUTO_VALUE_ON_ZERO configuration is enable in the mysql server. NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. Since the default value of the Space table is not NULL, it will not trigger the auto increment of the SPACEID in the spaces table during the space creation.
Resolution 1
- Repair the database following MySQL's doc: http://dev.mysql.com/doc/refman/5.1/en/myisam-repair.html
After fixing it, please switch your database engine to InnoDB
Resolution 2
- Shutdown your Confluence and MYSQL server
- Open your MySQL configuration file
my.ini(Windows) ormy.cnf(Unix) Find
sql_modeand deleteNO_AUTO_VALUE_ON_ZERO.For example, if you have the following:sql_mode = NO_AUTO_VALUE_ON_ZERORemove that line
Save the file and restart MySQL and Confluence server