Duplicate entry '0' for key 'PRIMARY' exceptions in log
This behavior has been observed in the following applications:
- JIRA
- Bamboo
Problem
When using MySQL and accessing certain pages in Atlassian applications, MySQLIntegrityConstraintViolationExceptions are written to the application log, i.e. atlassian-<product>.log.
Bamboo > Branch Status
atlassian-bamboo.log
2017-02-15 23:13:16,405 ERROR [http-nio-8085-exec-1] [ThrowableExceptionMapper] Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
Database:
- name:MySQL
- version:5.6.32
- minor version:6
- major version:5
Driver:
- name:MySQL Connector Java
- version:mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad )
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:MySQL
- version:5.6.32
- minor version:6
- major version:5
Driver:
- name:MySQL Connector Java
- version:mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad )
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:94)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:266)
at sun.reflect.GeneratedMethodAccessor935.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
(...)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'
at sun.reflect.GeneratedConstructorAccessor339.newInstance(Unknown Source)
(...)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:92)
... 229 more
The following error message appears in the UI:
An error occurred while trying to update the branch view. Please select a different repository/branch or reload the page.
Diagnosis
Environment
- Database: MySQL
Reference: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_value_on_zero
NO_AUTO_VALUE_ON_ZEROaffects handling ofAUTO_INCREMENTcolumns. Normally, you generate the next sequence number for the column by inserting eitherNULLor0into it.NO_AUTO_VALUE_ON_ZEROsuppresses this behavior for0so that onlyNULLgenerates the next sequence number.This mode can be useful if
0has been stored in a table'sAUTO_INCREMENTcolumn. (Storing0is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the0values, resulting in a table with contents different from the one that was dumped. EnablingNO_AUTO_VALUE_ON_ZERObefore reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enablesNO_AUTO_VALUE_ON_ZERO, to avoid this problem.
Cause
In the MySQL configuration ( my.ini (Windows) / my.cnf (Unix) ), the parameter of sql_mode is set to "NO_AUTO_VALUE_ON_ZERO".
Resolution
- Stop the application.
- Stop MySQL.
- Edit the MySQL configuration file.
Remove "NO_AUTO_VALUE_ON_ZERO" from sql_mode.
- Start MySQL
- Start the application.