Re: Sequence Numbering
On 2015-06-29 7:03 PM, Johnny Withers wrote:
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`
) 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)
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.
The server where I have verified it as working is:
The server where it is not working is:
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?
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql