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 trans_adv.store_id=N; 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,...) VALUES(fnNextStoreSeqNum(10),10,....); 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 ; -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
