MySQL Binary Logging Problem with InnoDB When Creating a Workflow
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
When creating a Workflow, the user receives exceptions like:
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'))
Another possible scenario:
com.atlassian.jira.plugin.ext.bamboo.service.PlanStatusUpdateServiceImpl:job [module.propertyset.ofbiz.OFBizPropertySet] Error setting value in PropertySet
org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:OSPropertyString][id,11709][value,true] (SQL Exception while executing the following:UPDATE propertystring SET propertyvalue=? 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.))
Cause
This is required by MySQL:
Statement based binlogging does not work in isolation level
READ UNCOMMITTED and READ COMMITTED since the necessary
locks cannot be taken.
Resolution
To change to row based binary logging, set the following in /etc/my.cnf (or your my.cnf if it's elsewhere):
binlog_format=row
Please see http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html for more information.