Re: Sequence Numbering
Found the problem here, someone had created on insert and on update triggers on this table that were interferring with the process. This logic works wonderfully when you need sequence numbers that are also transaction safe. The key is you need a unique index on all the columns involved. I think myisam tables support a multi colum auto increment primary key. This works the same when you need the same in innodb. On Jun 30, 2015 8:37 AM, "Bob Eby" wrote: > > If the return value of the UPDATE stmt is zero, the following stmt is > ran: > > By return value I assume you mean "rows affected" ? > > > This is working great, and has been for many years; however, > > today I noticed it was not working on a particular MySQL server. > > By "not working" what exactly is the failure mode? > > Not getting 0 rows affected? 0 rows affected not triggering > subsequent INSERT? Something else? > > Note: It would make me uneasy to use LAST_INSERT_ID() > where there is no AUTO_INCREMENT column myself... > > Good luck, > Robert >
Re: Sequence Numbering
> If the return value of the UPDATE stmt is zero, the following stmt is ran: By return value I assume you mean "rows affected" ? > This is working great, and has been for many years; however, > today I noticed it was not working on a particular MySQL server. By "not working" what exactly is the failure mode? Not getting 0 rows affected? 0 rows affected not triggering subsequent INSERT? Something else? Note: It would make me uneasy to use LAST_INSERT_ID() where there is no AUTO_INCREMENT column myself... Good luck, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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
Re: Sequence Numbering
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 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
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 joh...@pixelated.net
