Fix for WSO2 API Manager startup error "Caused by: java.sql.SQLException: 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..." With MariaDB 5.5

Exception

ERROR - DefaultRealmService Cannot initialize the realm.
org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:377)
at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:199)
at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:108)
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:225)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:96)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:109)
at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:68)
at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:390)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1176)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:559)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:544)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:457)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:243)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:438)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:1)
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:334)
... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:733)
at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:3445)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:252)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:194)
... 27 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:534)
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:729)
... 30 more
Caused by: java.sql.SQLException: 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.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:524)
... 31 more
[2015-08-21 10:54:27,698] ERROR - Activator Cannot start User Manager Core bundle
org.wso2.carbon.user.core.UserStoreException: Cannot initialize the realm.
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:233)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:96)
at org.wso2.carbon.user.core.common.DefaultRealmService.(DefaultRealmService.java:109)
at org.wso2.carbon.user.core.internal.Activator.startDeploy(Activator.java:68)
at org.wso2.carbon.user.core.internal.BundleCheckActivator.start(BundleCheckActivator.java:61)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:390)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1176)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:559)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:544)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:457)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:243)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:438)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.dispatchEvent(StartLevelManager.java:1)
at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340)
Caused by: org.wso2.carbon.user.core.UserStoreException: nullType class java.lang.reflect.InvocationTargetException
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:377)
at org.wso2.carbon.user.core.common.DefaultRealm.initializeObjects(DefaultRealm.java:199)
at org.wso2.carbon.user.core.common.DefaultRealm.init(DefaultRealm.java:108)
at org.wso2.carbon.user.core.common.DefaultRealmService.initializeRealm(DefaultRealmService.java:225)
... 19 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at org.wso2.carbon.user.core.common.DefaultRealm.createObjectWithOptions(DefaultRealm.java:334)
... 22 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:733)
at org.wso2.carbon.user.core.common.AbstractUserStoreManager.persistDomain(AbstractUserStoreManager.java:3445)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:252)
at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.(JDBCUserStoreManager.java:194)
... 27 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating database
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:534)
at org.wso2.carbon.user.core.util.UserCoreUtil.persistDomain(UserCoreUtil.java:729)
... 30 more
Caused by: java.sql.SQLException: 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.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)
at org.wso2.carbon.user.core.util.DatabaseUtil.updateDatabase(DatabaseUtil.java:524)
... 31 more

Fix

1. Shutdown Confluence and your MySQL database

2. Open the MySQL configuration file (my.cnf) in a text editor

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

4. Save your changes to this file

5. Restart Confluence and your MySQL database

References

[1]. http://bugs.mysql.com/bug.php?id=40360

[2]. https://confluence.atlassian.com/display/STASHKB/Stash+2.7+fails+to+start+with+MySQL+when+binary+logging+is+enabled

Note : 

When using WSO2 products with MariaDB it is recommended to use compatible version MySQL JDBC connector. EX: MariaDB 10.0.20 with mysql-connector-java-5.1.36-bin.jar