You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@activemq.apache.org by slotito <st...@gmail.com> on 2006/11/22 00:23:06 UTC
(AMQ 992) DefaultDatabaseLocker and mysql
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?
Steve
--
View this message in context: http://www.nabble.com/%28AMQ-992%29-DefaultDatabaseLocker-and-mysql-tf2682498.html#a7482369
Sent from the ActiveMQ - Dev mailing list archive at Nabble.com.
Re: (AMQ 992) DefaultDatabaseLocker and mysql
Posted by James Strachan <ja...@gmail.com>.
On 11/21/06, slotito <st...@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/