go mysql LAST_INSERT_ID() returns 0

Question

I have this MySQL database where I need to add records with a go program and need to retrieve the id of the last added record, to add the id to another table.

When i run insert INSERT INTO table1 values("test",1); SELECT LAST_INSERT_ID() in MySQL Workbench, it returns the last id, which is auto incremented, with no issues.

If I run my go code however, it always prints 0. The code:


    _, err := db_client.DBClient.Query("insert into table1 values(?,?)", name, 1)
    var id string
    err = db_client.DBClient.QueryRow("SELECT LAST_INSERT_ID()").Scan(&id)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println("id: ", id)

I tried this variation to try to narrow down the problem scope further: err = db_client.DBClient.QueryRow("SELECT id from table1 where name=\"pleasejustwork\";").Scan(&id), which works perfectly fine; go returns the actual id.

Why is it not working with the LAST_INSERT_ID()?

I'm a newbie in go so please do not go hard on me if i'm making stupid go mistakes that lead to this error :D

Thank you in advance.

*Why is it not working with the LAST_INSERT_ID()?* 99% that some intermediate queries are sent to MySQL hiddenly, or 2nd query is executed in another connection. Execute both queries in single transaction. — Akina, Dec 20 '21 at 11:47
ok, but how would you solve this problem? I need the id of the query, and I think I can not put 2 queries in one function call — mcklmo, Dec 20 '21 at 11:50
Im just learning sql. Could you explain the difference between a transaction (Tx), which you're suggesting to look into, and a database handle (DB), which I was using so far? — mcklmo, Dec 20 '21 at 12:08
Using `Query` and discarding, i.e. assigning to `_`, the primary return value, will cause a resource leak. For queries that don't return any rows you SHOULD use the `Exec` method. Also the `Exec` method returns an `sql.Result` value which, if implemented by the 3rd party driver you are using, will provide you the last inserted id through the `LastInsertedId` method. — mkopriva, Dec 20 '21 at 12:22
Also note that the DB handle in Go is a *pool* of connections and it's quite possible that the two separate queries were executed using two different connections from the pool. And, as far as mysql is concerned, the `ID` that was generated by AUTO_INCREMENT is maintained in the server on a **per-connection basis**. This means that the value returned by the function LAST_INSERT_ID is the first AUTO_INCREMENT value generated for most recent insert statement executed using the **same** connection. — mkopriva, Dec 20 '21 at 12:30

O. Jones · Accepted Answer · 2021-12-20T12:33:52.057

The MySQL protocol returns LAST_INSERT_ID() values in its response to INSERT statements. And, the golang driver exposes that returned value. So, you don't need the extra round trip to get it. These ID values are usually unsigned 64-bit integers.

Try something like this.

    res, err := db_client.DBClient.Exec("insert into table1 values(?,?)", name, 1)
     if err != nil {
        panic (err.Error())
    }
    id, err := res.LastInsertId()
    if err != nil {
        panic (err.Error())
    }
    fmt.Println("id: ", id)

I confess I'm not sure why your code didn't work. Whenever you successfully issue a single-row INSERT statement, the next statement on the same database connection always has access to a useful LAST_INSERT_ID() value. This is true whether or not you use explicit transactions.

But if your INSERT is not successful, you must treat the last insert ID value as unpredictable. (That's a technical term for "garbage", trash, rubbish, basura, etc.)

go mysql LAST_INSERT_ID() returns 0

1 Answers1