Database error on upgrade to 6.7.3
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)
Discussions are closed to public comments.
If you need help with Cascade CMS please
start a new discussion.
Keyboard shortcuts
Generic
? | Show this help |
---|---|
ESC | Blurs the current field |
Comment Form
r | Focus the comment reply box |
---|---|
^ + ↩ | Submit the comment |
You can use Command ⌘
instead of Control ^
on Mac
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!
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
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:
Thanks
4 Posted by jkreuzig on 28 Oct, 2010 10:36 PM
Tim,
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
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
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:
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:
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
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.
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
9 Posted by Bradley Wagner on 01 Nov, 2010 06:12 PM
Great, glad to hear it!
Thanks,
Bradley
Bradley Wagner closed this discussion on 01 Nov, 2010 06:12 PM.