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

   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.