ActiveMQ
  1. ActiveMQ
  2. AMQ-992

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

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 4.1.0
    • Fix Version/s: 5.3.0
    • Component/s: None
    • Labels:
      None
    • 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)

      1. ASF.LICENSE.NOT.GRANTED--mysql_obtain_lock.txt
        101 kB
        Steven Lotito
      2. patch.txt
        2 kB
        Shahram Javey

        Activity

        Hide
        Albrecht Militzer added a comment -

        I still seem to run into this problem. I have MySQL 5.1, I have InnoDB-Tables, but Slaves do not start up. The first process becomes master. The others block while trying to become master. They never become slaves. They try to execute

        SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE.

        This waits for the lock to be available and finally ends with a timeout. The next line in the log is:

        Failed to acquire lock. Sleeping for 1000 milli(s) before trying again...

        This keeps going forever.

        1) If I switch to Derby, the problem goes away.

        2) In MySQL, I had some luck with changing the lock statement to

        SELECT * FROM ACTIVEMQ_LOCK LOCK IN SHARE MODE

        but then Failover does not work. Once I kill the master, no slave becomes master.

        Show
        Albrecht Militzer added a comment - I still seem to run into this problem. I have MySQL 5.1, I have InnoDB-Tables, but Slaves do not start up. The first process becomes master. The others block while trying to become master. They never become slaves. They try to execute SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE. This waits for the lock to be available and finally ends with a timeout. The next line in the log is: Failed to acquire lock. Sleeping for 1000 milli(s) before trying again... This keeps going forever. 1) If I switch to Derby, the problem goes away. 2) In MySQL, I had some luck with changing the lock statement to SELECT * FROM ACTIVEMQ_LOCK LOCK IN SHARE MODE but then Failover does not work. Once I kill the master, no slave becomes master.
        Hide
        Gary Tully added a comment -

        resolving this issue as 5.3 uses INNODB for all create table statements.

        Show
        Gary Tully added a comment - resolving this issue as 5.3 uses INNODB for all create table statements.
        Hide
        Shahram Javey added a comment -

        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.

        Show
        Shahram Javey added a comment - 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.
        Hide
        Shahram Javey added a comment - - edited

        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?

        Show
        Shahram Javey added a comment - - edited 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?
        Hide
        Kenny MacLeod added a comment -

        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.

        Show
        Kenny MacLeod added a comment - 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.
        Hide
        james strachan added a comment -

        Backported to 4.1 trunk via rev: 518161 and 518164

        Show
        james strachan added a comment - Backported to 4.1 trunk via rev: 518161 and 518164
        Hide
        Steven Lotito added a comment -

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

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

        Show
        Steven Lotito added a comment - The following should work for MySQL 5.x and the tables that are generated... SET AUTOCOMMIT = 0; LOCK TABLE activemq.ACTIVEMQ_LOCK WRITE;
        Hide
        james strachan added a comment -

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

        LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;

        Show
        james strachan added a comment - I wonder if some SQL statement like the following works for MysQL... LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;
        Hide
        james strachan added a comment -

        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?

        Show
        james strachan added a comment - 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?

          People

          • Assignee:
            Unassigned
            Reporter:
            Steven Lotito
          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development