Mailing-List: contact activemq-dev-help@geronimo.apache.org; run by ezmlm
Precedence: bulk
Reply-To: activemq-dev@geronimo.apache.org
Received-SPF: pass (herse.apache.org: domain of james.strachan@gmail.com
 designates 64.233.184.229 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=uLjGRprzmDrEOtHfKxFISZdXmZQNWPnjb2TMyH3oJjTXvz34jNPpZhSG6J9tWTA9yxwUVnjybLLGc/lTByxzbNlywoPjwpirFouA/83x+I3tATwB7ZnoRcEaDh7D9rSXpA9IxMKYJjTGIC/0fHGWMBDU2eE2ed4huoSPWi1jt04=
Message-ID: <ec6e67fd0611280647h57135100g3cf3c0f972c40858@mail.gmail.com>
Date: Tue, 28 Nov 2006 14:47:49 +0000
From: "James Strachan" <james.strachan@gmail.com>
To: activemq-dev@geronimo.apache.org
Subject: Re: (AMQ 992) DefaultDatabaseLocker and mysql
In-Reply-To: <7482369.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <7482369.post@talk.nabble.com>

On 11/21/06, slotito <steven.lotito@gmail.com> wrote:
>
> Hi,
>
> I am trying to get MySQL to work in the Master/Slave JDBC configuration -
> the default sql does not lock the table properly.
>
> I added a MysqlJDBCAdapter that extends
> org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter so I could
> override the lockCreateStatement.
>
> I then edited the resources so that the mysql-ab_jdbc_driver would use the
> MysqlJDBCAdapter instead of the DefaultJDBCAdapter.
>
> The new locking statement is: LOCK TABLE ACTIVEMQ_LOCK WRITE  (which works
> in MySQL 5.x at least)
>
> Unfortunately, once I run it with the new code, it gets stuck in the
> following block (from org.apache.activemq.store.jdbc.DefaultDatabaseLocker)
>
>         PreparedStatement statement =
> connection.prepareStatement(statements.getLockCreateStatement());
>         while (true) {
>             try {
>                 log.info("Attempting to acquire the exclusive lock to become
> the Master broker");
>                 boolean answer = statement.execute();
>                 if (answer) {
>                     break;
>                 }
>             }
>             catch (Exception e) {
>                 if (stopping) {
>                     throw new Exception("Cannot start broker as being asked
> to shut down. Interupted attempt to acquire lock: " + e, e);
>                 }
>                 log.error("Failed to acquire lock: " + e, e);
>             }
>             log.debug("Sleeping for " + sleepTime + " milli(s) before trying
> again to get the lock...");
>             Thread.sleep(sleepTime);
>         }
>
> Unlike the original SQL ("SELECT* FROM ACTIVEMQ_LOCK FOR UPDATE"), the lock
> table command for Mysql doesn't return a ResultSet.  This means that
> "answer" above will always be false, even though the table is successfully
> locked, and it basically goes into an infinite loop "Attempting to
> acquire...".
>
> (see
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#execute(java.lang.String))
>
> I see three ways to get this working, but I might be missing something.
> I've never contributed anything to this project so I'd like to get some
> advice from you guys.
>
> 1) I'm pretty sure it is possible to allow multiple queries in a statement
> through the use of a connection string parameter (?allowMultiQueries=true)
> in the mysql driver - i.e. it would allow "LOCK TABLE ACTIVEMQ_LOCK WRITE;
> SELECT * FROM ACTIVEMQ_LOCK" which would return a ResultSet and set "answer"
> to true.  This would just be a documentation effort, basically making sure
> anyone using the Master/Slave JDBC configuration with MySQL knows to set
> that connection parameter.
>
> 2) I could create a MysqlDatabaseLocker and the necessary resource/config
> changes, then override that method and remove the "answer" part.  This seems
> like overkill since none of the other databases seem to require their own at
> this point.
>
> 3) I could remove the boolean answer from the DefaultDatabaseLocker.  Is it
> currently necessary?  If there's anything kind of database access error, it
> will just throw a SQLException anyway.  Other connections will wait for the
> lock to become available before they return from the statement.execute(),
> right?
>
> Any thoughts on this?

Thanks for this great contribution Steve!

Firstly - I've raised a JIRA to track this issue...
http://issues.apache.org/activemq/browse/AMQ-1074

I agree with 3), I think we should ignore the check for a result set
on the SQL statement. I've made this change in trunk now which should
help. I've also committed your patch to trunk - providing a
MySqlJDBCAdapter which uses the SQL you so kindly submitted for the
exclusive lock. I tried it on my machine with MySQL Connector/J 5.0.4
and it seemed to work great.

I wonder could you see if it works for you? If so we can close the issue.

-- 

James
-------
http://radio.weblogs.com/0112098/