MySQL doesn't honor lock in JDBC Master Slave configuration?

• Key: AMQ-992
• Type: Bug
• Status: Reopened
• Priority: Major
• Assignee: Unassigned
• Reporter: Steven Lotito
• Affects Version/s: 4.1.0
• Fix Version/s: 5.2.0

Environment

RHEL 4
MySQL 4.x, 5.x
mysql-ab_jdbc_driver

Description

I have been attempting to get the new 4.1 JDBC Master Slave configuration working with MySQL.

The log from the first broker to start up states:
2006-10-18 09:35:08,558 [main ] INFO DefaultDatabaseLocker - Attempting to acquire the exclusive lock to become the Master broker
2006-10-18 09:35:08,559 [main ] INFO DefaultDatabaseLocker - Becoming the master on dataSource: org.apache.commons.dbcp.BasicDataSource@136d9d8

The 2nd broker to start up has an identical message and both brokers listen for connections.

The 2nd broker should be waiting for the lock and NOT accepting connections, if I understand http://www.activemq.org/site/jdbc-master-slave.html correctly...

Oracle exhibits the expected behavior:
When running the exact same configuration (except using an Oracle datasource), the first broker has the same log message as above, while the 2nd broker halts at the "Attempting to acquire the exclusive lock to become the Master broker" message until I fail the master. Then it becomes the master.

Is this a known issue? I was able to replicate it using both MySql 4 and 5 (trying both the MySQL Connector/J 3.1 and MySQL Connector/J 5.0 drivers)

Comments

[James Strachan]

You're the first one to notice this issue. I wonder how else to create an exclusive table lock in MySQL. I wonder if they are even supported? It might be we need to use different SQL for MySQL maybe?

[James Strachan]

I wonder if some SQL statement like the following works for MysQL...

LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;

[Steven Lotito]

The following should work for MySQL 5.x and the tables that are generated...

SET AUTOCOMMIT = 0;
LOCK TABLE activemq.ACTIVEMQ_LOCK WRITE;

[James Strachan]

Backported to 4.1 trunk via rev: 518161 and 518164

[Kenny MacLeod]

I'm re-opening this issue because it may be that the fix was unnecessary. In MySQL, SELECT FOR UPDATE only works with InnoDB tables. The default table type in MySQL - MyISAM - does not support SELECT FOR UPDATE - it executes the select, but doesn't hold the lock, resulting in Steven's problem. There's no need for specific MySQL behaviour here.

However, when it comes to creating the tables, MySQL will create the table according to the server's default table type, which is MyISAM unless specific in MySQL's startup config. Ideally, ActiveMQ would specify InnoDB in the CREATE TABLE clause.

[Shahram Javey]

The current lock statement with MyISAM tables has led to very unstable databases (MySQL 5.x) in our environment. After a few hours of use, the MySQL database becomes progressively slower and eventually no other process can get any database lock. The only option is to kill the slave broker and to restart the database. Has anyone else observed this problem?

[Shahram Javey]

Assuming that the change of the MySQL tables from MyISAM to InnoDB does fix this problem, I've created a patch to alter the tables to use InnoDB storage engine. I think using ALTER instead of updating the create statements minimizes the code change. I've tested this patch to make sure that it works, but have not yet ran it over a period of time to see if I observe the kind of database slow down that was evident with MyISAM tables.