Duplicate entry '0' for key 'PRIMARY' exceptions in log

Cross Product Knowledge

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

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.