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_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.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is 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 the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_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

  1. Stop the application.
  2. Stop MySQL.
  3. Edit the MySQL configuration file.
  4. Remove "NO_AUTO_VALUE_ON_ZERO" from sql_mode.

  5. Start MySQL
  6. Start the application.

 

Last modified on Feb 21, 2017

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.