I should add: By working I mean:
The first time the UPDATE stmt runs, it will return a zero value for the seq_id. The insert stmt will initialize it to 1000 The second time the UPDATE stmt runs, it returns 1001. The third time, 1002, etc. When it doesn't work, the second time the code is ran you get a duplicate key entry for an attempt to insert 1000 again. On Mon, Jun 29, 2015 at 7:03 PM, Johnny Withers <joh...@pixelated.net> 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. > > 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, > > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
