Home → Installation and System Configuration →

Database error on upgrade to 6.7.3

• Edit

jkreuzig

28 Oct, 2010 01:28 AM

I have an error that started appearing in my catalina.out on my test server that I just updated from 6.7.2 -> 6.7.3 (see below). I don't have replication enabled, but I am using binary logging to provide point-in-time recovery in case of system crash. I'm going to start testing tomorrow, but I want to get some sort of resolution on this before I put it into production. Any ideas what is going on?

-Jim

2010-10-27 18:18:04,275 ERROR [LocalDataSourceJobStore] : ClusterManager: Error managing cluster: Failure obtaining db row lock: Binary logging not possible.  Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' [See nested exception: java.sql.SQLException: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT']
    at org.quartz.impl.jdbcjobstore.UpdateLockRowSemaphore.executeSQL(UpdateLockRowSemaphore.java:107)
    at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3196)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3820)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.run(JobStoreSupport.java:3856)
Caused by: java.sql.SQLException: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
    at org.quartz.impl.jdbcjobstore.UpdateLockRowSemaphore.executeSQL(UpdateLockRowSemaphore.java:86)

1. Support Staff 1 Posted by Tim on 28 Oct, 2010 03:02 PM

   

   Hi Jim,

   Can you please attach the entire cascade.log file (from Oct. 27) here?

   Thanks!

   • Edit

2. 2 Posted by jkreuzig on 28 Oct, 2010 04:10 PM

   

   Tim,

   Attached is a zip file with the log from Oct. 27. I compressed it because it was well over the 10MB in size limit (108MB)

   Thanks,

   Jim

   • cascade.log.2010-10-27.zip 862 KB
   • Edit

3. Support Staff 3 Posted by Tim on 28 Oct, 2010 06:51 PM

   

   Thanks Jim! I've been looking over some of the errors but I may need a little more time for investigating. I'll let you know as soon as I have more information for you.

   In the meantime, can you tell me:

   • Which version of MySQL is the test instance using?
   • Which version of MySQL is the production instance using?
   • If test and production point to separate databases (same MySQL version or not), are the MySQL configuration files the same? (my.cnf/my.ini)

   Thanks

   • Edit

4. 4 Posted by jkreuzig on 28 Oct, 2010 10:36 PM

   

   Tim,

   • Which version of MySQL is the test instance using? - We are running mysql-5.1.33-solaris10-sparc-64bit
   • Which version of MySQL is the production instance using? - Same as test **(mysql-5.1.33-solaris10-sparc-64bit)
   • If test and production point to separate databases (same MySQL version or not), are the MySQL configuration files the same? (my.cnf/my.ini) - I thought this might have been the problem. The my.cnf files were not the same, but I shut everything down on the test server and changed the my.cnf to match the production server. A restart of everything produces the same results as mentioned above

   I've attached mysql config file. It's remained largely unchanged for the last year and a half. The only thing I changed is when I allocated more memory to the innodb_buffer_pool_size variable. It was set to 2GB when we were running a 32bit version of mysql and I increased it to 6GB when we moved to the 64bit version. That was about a year ago.

   Jim

   • cascade-my.cnf.zip 8.74 KB
   • Edit

5. Support Staff 5 Posted by Tim on 29 Oct, 2010 01:52 PM

   

   Hi Jim,

   Thanks for that information. We use the READ-COMMITTED isolation level for better reliability in our Quartz scheduling layer. This change was introduced in 6.7.3.

   Unfortunately, this isolation level is incompatible with statement-level binary logging in MySQL . After a bit of research, it doesn't seem like MySQL is going to address this underlying issue. As of now, it appears that the only option will be to either switch to row-level binary logging or disable it entirely.

   Please let me know if you have further questions.

   Thanks

   • Edit

6. 6 Posted by jkreuzig on 29 Oct, 2010 10:45 PM

   

   Tim,

   I've gone ahead and changed the my.cnf file to add the following:

   transaction_isolation = READ-COMMITTED
   binlog_format=ROW

   It seems to work. The only real side affect now is that my binary log files grow bigger quicker.
   

   I don't know how many of your customers use MySQL, but the default setting on the "transaction_isolation" setting varies with which version of MySQL one is using. See the following:

   http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#sysvar_binlog_format

   It describes how they changed the default value from version to version. This is probably going to be something that affects more than just us here at UCI.

   Thanks again for the prompt feedback.

   Jim

   • Edit

7. 7 Posted by Bradley Wagner on 29 Oct, 2010 11:14 PM

   

   You do not want to set an isolation level for the entire MySQL instance. Our application specifies this for individual transactions on it's own.

   • Edit

8. 8 Posted by jkreuzig on 01 Nov, 2010 05:47 PM

   

   Bradley,

   I mistook Tim's message about it being READ-COMMITTED. I set it back to the default value (REPEATABLE -READ) and it works. Having binlog_format = ROW seems to be the only change necessary.

   Thanks.

   Jim

   • Edit

9. 9 Posted by Bradley Wagner on 01 Nov, 2010 06:12 PM

   

   Great, glad to hear it!

   Thanks,
   Bradley

   • Edit

10. Bradley Wagner closed this discussion on 01 Nov, 2010 06:12 PM.