Oct 20th, 2010, 04:28 AM
Restoring corrupted sequence tables
We have developed an application that uses spring-batch with two MySQL 5.1 servers (master+slave) and created the metadata schema as recommended here, including three sequence tables that uses the MyISAM storage engine (BATCH_STEP_EXECUTION_SEQ, BATCH_JOB_EXECUTION_SEQ and BATCH_JOB_SEQ).
But the operations people do not like the use of the MyISAM storage engine, since they can become corrupted and require manual attention. With a number of things that may need to be done (see for example here), and with the complexity of two servers trying to synchronize on something that is corrupt on one of the instances, it may not be obvious how to fix this!
My suggestion is to have a procedure for this case involving these steps: drop the affected table, re-create it and populate it with the correct sequence number again. This should be fairly simple.
The question is: How would this affect or interact with batchjobs that are running or starting while this is done?
Obviously, jobs trying to start when the tables are gone (or missing the sequence record, or is corrupt) will fail.
But what about running jobs trying to launch the next step - then I guess the step will fail?
Do we need to stop the appservers completely and thereby make sure that no batchjobs are running?
That would affect online users as well!
When re-creating the tables, do I risk that a sequence number has been taken, which is not yet used (but will be later).
What is the default value cacheSize in MySQLMaxValueIncrementer - just 1? Otherwise this must be taken into account as well when finding the values for the sequence tables!
Any advice will be appreciated!
Tags for this Thread