Web lists-archives.com

Re: Sequence Numbering




On 2015-06-29 7:03 PM, Johnny Withers wrote:
Hello all,

I have a tabled defined:

CREATE TABLE `store_seq` (
   `seq_type_id` smallint(3) unsigned NOT NULL DEFAULT '0',
   `store_id` int(10) unsigned NOT NULL DEFAULT '0',
   `loan_model_id` int(10) unsigned NOT NULL DEFAULT '0',
   `store_bank_id` int(10) unsigned NOT NULL DEFAULT '0',
   `seq_id` int(10) unsigned NOT NULL DEFAULT '0',
   `check_format` varchar(50) DEFAULT NULL,
   UNIQUE KEY `idx_contract`
(`loan_model_id`,`seq_type_id`,`store_id`,`seq_id`,`store_bank_id`),
   KEY `idx_chk`
(`store_bank_id`,`seq_type_id`,`store_id`,`seq_id`,`loan_model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The following SQL is used to generate and initialize the sequence number
for the idx_contract key:

UPDATE store_seq SET seq_id=LAST_INSERT_ID(seq_id+1) WHERE
loan_model_id=224 AND seq_type_id=2 AND store_id=179;

If the return value of the UPDATE stmt is zero, the following stmt is ran:

INSERT INTO store_seq(seq_type_id,store_id,loan_model_id,seq_id)
VALUES(2,179,224,1000)

This is working great, and has been for many years; however, today I
noticed it was not working on a particular MySQL server.

To guarantee such a result, you need a table of sequential numbers and a transaction which marks one of its numbers as used and uses that number in the write to the other table.

PB



The server where I have verified it as working is:
5.5.42-log

The server where it is not working is:
5.5.32-enterprise-commercial-advanced

This same code is used on about 10 other mysql servers where it is working
fine. I have verified the table schema is the same on both mysql servers.
Anyone have any insight as to why it may not be working on that one server?

Thanks,




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql