Get last inserted Id returns 0 if the row already exists

Question

When I use:

START TRANSACTION;
INSERT IGNORE INTO users (Name, ...) VALUES ('user1',..) ON DUPLICATE KEY UPDATE lastSeen=NOW();
SELECT LAST_INSERT_ID();
COMMIT;

When i run this query from my sql client I get the last inserted id as expected.

But what if the user already exists?(table uniquness). So no new id is created. But now when I run the same query from my sql client still i get the id as expected.

But! when I run it from my C# code and use mysql.reader to query the result I receive 0. Why is that??

If instead of SELECT LAST_INSERT_ID() I use SELECT id FROM users ORDER BY id DESC LIMIT 1 I get the right id again.

EDIT!

This is not a duplicate of the suggested topic. In that topic the answer says that the auto-incremented is not the primary key, thats not my case! also as I mark it does return the right id when i use the mysql client! The problem occures only when I run it from my c# code!

My C# code:

using (var conn = new MySqlConnection(connString))
{
    try
    {
        conn.Open();
        var command = conn.CreateCommand();
        command.CommandText = strSQL;
        MySqlDataReader reader;
        string result = "";
        try
        {
            reader = command.ExecuteReader();

            if (reader.Read())
                result = reader[0].ToString();
        }
        catch (Exception ex)
        {
            throw new MySqlException("SQL Error: " + ex.ToString());
        }
        reader.Close();
        return result;
    }
}

Possible duplicate of [MySQL: LAST\_INSERT\_ID() returns 0](http://stackoverflow.com/questions/13880267/mysql-last-insert-id-returns-0) — adv12, May 17 '16 at 13:08
probably when you `ON DUPLICATE KEY UPDATE` **UPDATE** the record mysql does not return **LAST_INSERT_ID** because it was not inserted. — Alex, May 17 '16 at 13:18
and post your c# code. some people here are smart enough to read c# :-) — Alex, May 17 '16 at 13:20
@Alex you guys dont read my question fully. It does return the right id when i run the sql as is! The problem is only from my c# code — omriman12, May 17 '16 at 13:20

score 1 · Accepted Answer · answered May 17 '16 at 13:41

I have found a work around, simple CASE clause does the job:

START TRANSACTION;
INSERT IGNORE INTO users (Name, ...) VALUES ('user1',..) ON DUPLICATE KEY UPDATE lastSeen=NOW();
SELECT CASE WHEN LAST_INSERT_ID()=0 THEN (SELECT id FROM users WHERE Name = 'user1') ELSE LAST_INSERT_ID() END;
COMMIT;

Now in case the user exists, we simply query the id, If it doest not exists, the LAST_INSERT_ID() will give us the right id

score 1 · Answer 2 · edited May 23 '17 at 12:01

From what I read there https://stackoverflow.com/a/15057619/4421474

Your code could be transformed to:

using (var conn = new MySqlConnection(connString))
{
   conn.Open();
   var command = conn.CreateCommand();
   command.CommandText = strSQL; <--  just insert part like "START ... INSERT ... COMMIT;"
   command.ExecuteNonQuery();
   string result = command.LastInsertedId;
   return result;
}

Sorry I am not c# expert so some syntax could be broken.

score 0 · Answer 3 · edited May 23 '17 at 11:45

0

I guess this answer:

Just my 50 cents for this issue, I simply noticed that you won't get a LAST_INSERT_ID greater than 0 if your table has no AUTO_INCREMENT set to an index

that i found here MySQL: LAST_INSERT_ID() returns 0 is the good one.

edited May 23 '17 at 11:45

Community

1
1

answered May 17 '16 at 13:07

Benoît

87
8

Get last inserted Id returns 0 if the row already exists

3 Answers3