MySQL Bugs: #30895: LAST_INSERT_ID() returning incorrect value

Bug #30895	LAST_INSERT_ID() returning incorrect value
Submitted:	7 Sep 2007 14:20	Modified:	23 Sep 2007 11:51
Reporter:	Tanner Ratzlaff	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	insert, LAST_INSERT_ID

Description:
I’m using MySQL v5.0.45 and the query “SELECT LAST_INSERT_ID()” is returning 0 instead of the row id.  The row does get inserted into the table and LAST_INSERT_ID() worked in MySQL v5.0.27.  Also, if an insert was done into another table before the insert into the FILE_INFO table, the row id of that table will be returned and not the one from FILE_INFO.

When I create a table just like FILE_INFO without the foreign key constraint and without the tirgger and insert into it, the LAST_INSERT_ID() works correctly.

How to repeat:
Below is the CREATE statement for the table I’m inserting into and the INSERT statement I’m using. I do have a BEFORE INSERT TRIGGER set up on the FILE_INFO table that sometimes inserts into a different table, but the id from this insert is not being returned.

CREATE TABLE  `prep`.`FILE_INFO` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `INSTITUTION_FKEY` int(10) unsigned NOT NULL,
  `GROUPING_FKEY` int(10) unsigned default NULL,
  `BATCH_FKEY` int(10) unsigned NOT NULL,
  `FILE_NAME` varchar(45) default NULL,
  `YEAR_VALUE` smallint(5) unsigned default NULL,
  `TERM` varchar(45) default NULL,
  `FACULTY` varchar(45) default NULL,
  `OPTIONAL` varchar(45) default NULL,
  `NUMBER` int(10) unsigned NOT NULL default '0',
  `MANUAL1` varchar(45) default NULL,
  `MANUAL2` varchar(45) default NULL,
  `MANUAL3` varchar(45) default NULL,
  `SCAN_BY` varchar(45) default NULL,
  `SCAN_DATE` timestamp NULL default NULL,
  `EDIT_BY` varchar(45) default NULL,
  `EDIT_DATE` timestamp NULL default NULL,
  `ERROR` tinyint(3) unsigned NOT NULL default '0',
  `DONE` tinyint(3) unsigned NOT NULL default '0',
  `MUSTFIX` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `FILE_NAME` (`FILE_NAME`),
  KEY `INSTITUTION_FKEY` (`INSTITUTION_FKEY`),
  KEY `BATCH_FKEY` (`BATCH_FKEY`),
  KEY `TERM` (`TERM`),
  KEY `YEAR_VALUE` (`YEAR_VALUE`),
  KEY `SCAN_DATE` (`SCAN_DATE`),
  KEY `EDIT_DATE` (`EDIT_DATE`),
  CONSTRAINT `FILE_INFO_ibfk_1` FOREIGN KEY (`BATCH_FKEY`) REFERENCES `BATCH` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2053 DEFAULT CHARSET=latin1;

INSERT INTO prep.FILE_INFO (INSTITUTION_FKEY, TERM, YEAR_VALUE, NUMBER, FACULTY, OPTIONAL, BATCH_FKEY, MANUAL1, MANUAL2, MANUAL3, SCAN_BY, SCAN_DATE) 
VALUES ('21','Winter','2007','1','3','TEST1','9000','man1','man2','man3','',NOW())

Can you also provide the definitions of the trigger
and the BATCH table?

CREATE TABLE  `prep`.`BATCH` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `INSTITUTION_FKEY` int(10) unsigned NOT NULL,
  `NAME` varchar(100) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INSTITUTION_FKEY_2` (`INSTITUTION_FKEY`,`NAME`),
  KEY `INSTITUTION_FKEY` (`INSTITUTION_FKEY`)
) ENGINE=InnoDB AUTO_INCREMENT=9021 DEFAULT CHARSET=latin1;
ENGINE=INNODB;

DROP TRIGGER IF EXISTS init_inst_bi;
delimiter //
CREATE TRIGGER init_inst_bi
BEFORE INSERT
ON prep.FILE_INFO
FOR EACH ROW

BEGIN
DECLARE CONTINUE HANDLER FOR 1329 SET @inst_fkey = NULL;
SET @inst_fkey = NULL;

SELECT INST_FKEY INTO @inst_fkey FROM INST_ERRORS WHERE INST_FKEY = NEW.INSTITUTION_FKEY LIMIT 1;

IF (@inst_fkey IS NULL) THEN
  INSERT INTO INST_ERRORS (INST_FKEY, ERROR_FKEY)
  SELECT NEW.INSTITUTION_FKEY, ERROR.ID FROM ERROR
  WHERE ERROR.ISCURRENT = 1 AND ERROR.ISDEFAULT = 1;
END IF;
         
INSERT IGNORE INTO INST_STATUS (INST_FKEY) VALUES (NEW.INSTITUTION_FKEY);

IF (NEW.ID = 0) THEN 
  SET @max_id = NULL;    
  SELECT MAX(ID) INTO @max_id FROM FILE_INFO; 
  IF (@max_id IS NULL) THEN SET @max_id = 0;
  END IF;    
  SET NEW.ID = @max_id+1;
END IF;
SET NEW.FILE_NAME = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%h%i%s'), '_', NEW.ID);
END;
//

I think I found the issue.  In my tirgger I had "SET NEW.ID = @max_id+1;" and this was screwing up the auto_increment/LAST_INSERT_ID().

So, looks like this is not a bug.