Web lists-archives.com

Sequence Numbering




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
johnny@xxxxxxxxxxxxx