Duplicate entry 0 for key PRIMARY error in MySQL

Owned by Jack Hunter (Unlicensed)
Last updated: Jul 24, 2017
1 min read

Symptoms

Automated Log Work plugin for JIRA Server does not work (timer does not start in both Manual and Automated modes) and JIRA server log contains following error:

2017-06-29 10:24:55,810 https-jsse-nio-8443-exec-4 ERROR user 624x107725x1 1r183oq 172.20.1.254 /rest/com.gebsun.plugins.jira.startwork/1.0/toggle/DFP-2211/false [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
    Database:
        - name:MySQL
        - version:5.7.18-0ubuntu0.16.04.1
        - minor version:7
        - major version:5
    Driver:
        - name:MySQL Connector Java
        - version:mysql-connector-java-5.1.42 ( Revision: 1f61b0b0270d9844b006572ba4e77f19c0f230d4 )
    
    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:

Cause

In MySQL configuration (my.ini on Windows or my.cnf on Unix), parameter of sql_mode is set to  NO_AUTO_VALUE_ON_ZERO.

Resolution

  1. Stop JIRA.
  2. Stop MySQL.
  3. Edit the my.cnf file (often named my.ini on Windows operating systems or my.cnf on UNIX operating systems) in your MySQL server.

  4. Remove NO_AUTO_VALUE_ON_ZERO from sql_mode.

  5. Start MySQL.
  6. Start JIRA.

Resolution based on JIRA Knowledge Base document: https://confluence.atlassian.com/jirakb/duplicate-entry-0-for-key-primary-exceptions-in-log-646251198.html