JIRA Cannot Create Issues when Using MySQL with Binary Logging

If you use JIRA with MySQL and attempt to create a JIRA issue, JIRA may generate an error similar to the following:

Error creating issue: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][id,null][name,jira][state,0]
(SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?)
(Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

OR

Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

OR

org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:IssueType][id,1][iconurl,/images/icons/issuetypes/bug.png] (SQL Exception while executing the following:UPDATE issuetype SET ICONURL=? WHERE ID=? (Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.))

 

You may encounter this problem if your JIRA MySQL database configuration:

  • Makes use of the InnoDB database storage engine (which is recommended)
    AND
  • Uses MySQL's default binary logging format

JIRA uses the 'READ-COMMITTED' transaction isolation level with MySQL, which currently only supports row-based binary logging. For more information about this, please refer to MySQL issue no. 40360.

To overcome this problem, you must configure MySQL's binary logging format to use 'row-based' binary logging.

To do this:

  1. Shutdown JIRA and your MySQL service if necessary.
  2. Open the MySQL configuration file (my.cnf) in a text editor.
    (info) On UNIX-based systems, this file may be located in the /etc directory.
  3. Locate the binlog_format property in this file in the [mysqld] section and ensure that its value is row, such that you end up with:

    binlog_format=row

    (info) This is only needed (and valid) for MySQL versions 5.1.5 and later.

  4. Save your changes to this file and restart your MySQL service and JIRA.
Last modified on Apr 18, 2013

Was this helpful?

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