MySQL/JDBC MasterSlave Configuration

[emb]

Background: Evaluate a MasterSlave system and test failover with persistent messages.

Using apache-activemq-5.5.0-fuse-00-27/conf/activemq-demo.xml as a base and using MySQL as Datastore, both the Master Broker & Slave Broker seem to start. The slave waits for the master to release the table lock before it takes over.

Test 1:

• Start both Master/Slave brokers

• Create a producer adding persistent messages to a queue

• Create a consumer dequeuing the messages

• Issue  'activemq stop' command

Test 2:

• Same as Test 1 but instead pull out a network cable simulating a catastrophic failure.

While Test 1 produces a clean failover, Test 2 doesn't.

In Test 2, the Slave Broker keeps attempting to get a lock on the table, but continuously fail. The reason it fails, is because MySQL server doesn't know that the Master has died. And will wait for WAIT_TIMEOUT before closing the connection and releasing the lock. By default, this is 8 Hours.

How do you achieve a more reliable failover without manipulating the WAIT_TIMEOUT? Do other databases suffer from this same problem? If I am doing any thing wrong, what would it be?

PS: I did not post my configuration files because they just commented/uncommented blocks form activemq-demo.xml

[garytully]

This largely depends on the protocol that underlies the jdbc connection. Typically this is socket based so the tcp/ip keep alive settings can be used such that that aborted client socket is visible to MySQL a little earlier.

The problem with an abortive shutdown is that the broker jdbc connection will not be closed, so it is up to some layer to determine that the underlying connection is stale.

The MySQL wait_timeout[1] is one options, but you may get better mileage out of the tcp/ip stack using keepalive

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_wait_timeout

http://www.gnugk.org/keepalive.html

[emb]

Thanks for your reply. Using TCP keep alive helps with a fast fail over. Although, I am seeing quite a few issues now.

Example:

• The master will never start again. The logs would be empty despite changing to DEBUG in log4j

• The slave that became a master is not capable of inserting messages to the database due to Duplicate entries for Key 1.

Is this the right place to discuss these issues? Since, I am entirely new to this stack, has anyone performed & documented failover tests on Fuse Broker?