1

I have a BIGINT field as an auto increment and primary key on a MySQL Innodb table, running MySQL Community Server 5.6.11.

After calling a basic INSERT statement, and then calling SELECT LAST_INSERT_ID(), I'm always returned 0, even though the INSERT statement was sucessful.

Any ideas why this might be happening.

UPDATE: Here is my table definition

CREATE TABLE `Booking` (
  `BookingId` bigint(20) NOT NULL AUTO_INCREMENT,
  `HotelId` int(11) NOT NULL,
  `AgentId` int(11) NOT NULL DEFAULT '0',
  `BookedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastChangedBy` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`BookingId`)
 ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is my INSERT statement

INSERT INTO Booking
(
    HotelId,
    AgentId,        
    BookedOn,
    LastChangedBy
)
VALUES
(
    iHotelId,
    iAgentId,       
    NOW(),
    0
);

SELECT LAST_INSERT_ID() AS BookingId;
neildt
  • 5,101
  • 10
  • 56
  • 107
  • 1
    What's your table defn? What's your insert statement? – O. Jones Jul 05 '13 at 15:07
  • I've updated my question with both table definition and the Insert Statement – neildt Jul 05 '13 at 15:12
  • It's working for me in my Mysql local Version.... give us more details (I have to remove the `on delete no action on update no action`) – jcho360 Jul 05 '13 at 15:26
  • I don't have a 5.6 server to work with, sorry. If you remove `ON DELETE NO ACTION ON UPDATE NO ACTION` from your table definition does it work? It does for me on 5.5. How do you know your insert worked? – O. Jones Jul 05 '13 at 15:27
  • @jcho360 What version of MySQL are you running ? – neildt Jul 05 '13 at 15:30
  • @OllieJones ON DELETE NO ACTION ON UPDATE NO ACTION only causes a error when you create the table. My issue is the INSERT statement and the fact SELECT LAST_INSERT_ID() AS BookingId always returns 0 – neildt Jul 05 '13 at 15:31
  • mysql Ver 14.14 Distrib 5.5.9, for osx10.6 (i386) using EditLine wrapper – jcho360 Jul 05 '13 at 15:31
  • can you show us more code? – jcho360 Jul 05 '13 at 15:32
  • @jcho360 Why do you need more code ? I'm running these two MySQL statements on MySQL Workbench. There is nothing else to show ? – neildt Jul 05 '13 at 15:36
  • My question stands. Does the presence of `ON DELETE NO ACTION ON UPDATE NO ACTION` in your DDL interfere actually with the operation of `LAST_INSERT_ID()`? I'm well aware that it's not supposed to interfere. My question is, does it interfere? – O. Jones Jul 05 '13 at 15:37
  • Welcome to StackOverflow. You're asking a good question, one that has our participants stumped, because at least some of us cannot reproduce your problem. Questions like "can you show more code" and "what if you try this" are designed to help figure out what is different between your environment and ours. How are the variables in the VALUES clause of your INSERT statement set? – O. Jones Jul 05 '13 at 15:45
  • @OllieJones Thanks for your response and helpful comment. ON DELETE NO ACTION ON UPDATE NO ACTION was included by mistake, because of a foreign key constraint for some of the fields not related to the PK, and therefore I didn't think it was relevant. If I remove this and create table with no foreign key it is fine and works. But, if I add in the foreign it doesn't work. – neildt Jul 05 '13 at 16:07
  • So am I best re-posting a new question with all the foreign keys ? – neildt Jul 05 '13 at 16:36

0 Answers0