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
