I have two servers, both running 5.0.77-log, one is setup as a master, the
other as a replication slave.
The database contains a table that holds records of loans for financial
lending stores. This table has an ID column this is defined as auto
increment. There is another column called "store_seq_num" that holds the
sequence number for each loan done in each store. This column needs to work
like the auto-increment field; however, it's value is dependent upon which
store created the loan. Currently there is a UDF called fnNextStoreSeqNum
that returns the next sequence number for the new loan for the given store.
It does this by executing:
SELECT MAX(store_seq_num)+1 AS NextId FROM trans_adv WHERE
It uses the store_seq_num key and explain says "Select tables optimized
away." in the extra column.
The INSERT statement for this table looks something like this:
INSERT INTO trans_adv(store_id,store_seq_num,...)
The problem comes in on the replication server. Sometimes the sequence
numbers do not match the master. The root cause of the problem seems to be
when two loans are created in the same store at the same time (same second
-- or even 2 seconds apart sometimes). The master duplicates the sequence
number and the slave writes the correct sequence numbers. This seems to
happen when the server is under heavy load (600+ queries per sec). I hvae a
feeling it's due to the loan being created in a single transaction;
therefore the sequence number for the "first" loan really didn't exist to
any other connections until COMMIT was issued.
Is there a better way to do these sequence numbers? Should the key be
defined as UNIQUE? If it is defined as UNIQUE how can this key be added to
the existing table that has duplicate sequence numbers?
A partial create table statement is below for the trans_adv table.
CREATE TABLE `trans_adv` (
`id` int(10) unsigned NOT NULL auto_increment,
`store_id` int(10) unsigned NOT NULL default '0',
`store_seq_num` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `store_key` (`store_id`),
KEY `store_seq_num_key` (`company_id`,`store_id`,`store_seq_num`),
) ENGINE=InnoDB AUTO_INCREMENT=3049363 DEFAULT CHARSET=latin1