LAST_INSERT_ID() always returns 0 (RMySQL) - separate connection issue

Question

Original example as found in some post

According to this post the following SQL statements should give me a vector 1, 2, 2, 2, 2 in the end:

require("RMySQL")
con <- dbConnect(
    dbDriver("MySQL"),
    db="your_db",
    user="your_user",
    password="your_pw", 
    host="localhost"
)
> con
<MySQLConnection:(6640,122)> 
> dbSendQuery(con, "DROP TABLE IF EXISTS t;")
<MySQLResult:(6640,122,0)> 
> dbSendQuery(con, "CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);")
<MySQLResult:(6640,122,1)> 
> dbSendQuery(con, "INSERT INTO t VALUES(NULL);")
<MySQLResult:(6640,122,2)> 
> dbGetQuery(con, "SELECT LAST_INSERT_ID() FROM t;")
  LAST_INSERT_ID()
1                0
> dbSendQuery(con, "INSERT INTO t VALUES(NULL),(NULL),(NULL);")
<MySQLResult:(6640,122,3)> 
> dbGetQuery(con, "SELECT LAST_INSERT_ID() FROM t;")
  LAST_INSERT_ID()
1                0
2                0
3                0
4                0

Following suggestions by N.B., Jeff Allen and Quassnoi

Adapted the example to have it resemble real use cases a bit more than the original one:

dbSendQuery(con, "DROP TABLE IF EXISTS t;")
dbSendQuery(con, paste("CREATE TABLE t", 
    "(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, x INT);"))
> dbGetQuery(con, "SELECT CONNECTION_ID();")
  CONNECTION_ID()
1          673490
dbSendQuery(con, "INSERT INTO t SET x=1;")
> dbGetQuery(con, "SELECT CONNECTION_ID();")
  CONNECTION_ID()
1          673491
> dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                0
> dbGetQuery(con, "SELECT CONNECTION_ID();")
  CONNECTION_ID()
1          673493
> dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                0
dbSendQuery(con, "INSERT INTO t SET x=2;")
> dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                0
> dbGetQuery(con, "SELECT * FROM t;")
  i x
1 1 1
2 2 2

Well, it doesn't, really ;-)

I've googled a bit and AFAIU, LAST_INSERT_ID() is "connection-aware" in the sense that the same connection must be used if it is to work properly. However, I thought that by assigning the connection object to con I was making sure that indeed the same connection is used in each of the statements above.

Well, apparently not ;-) Can anyone help me out with some explanations and/or workarounds? Using something like select max(<ID>) from <TABLE> isn't going to cut it, though, as I'm running multiple threads that simultaneously write to the DB, thus messing up ID retrieval if done that way.

Thanks!

Findings as of 2012-04-20

Thanks to Quassnoi I was able to track down the problem a bit more. Seems like RMySQL functions don't really care about the explicit conn argument that much but open new connections in the background every time you connect to the DB. Probably some good reasons for this, too. Yet, does anyone know how to avoid this?
Just contacted Jeffrey Horner (maintainer of the RMySQL package). Seems like this is a Windows problem. Worked for him on Linux :-/

Connection details

As suggested by Jeff

> dbGetInfo(con)
$host
[1] "localhost"

$user
[1] "your_user"

$dbname
[1] "your_db"

$conType
[1] "localhost via TCP/IP"

$serverVersion
[1] "5.5.20"

$protocolVersion
[1] 10

$threadId
[1] 673489

$rsId
$rsId[[1]]
<MySQLResult:(6640,171,3)> 


> dbGetInfo(dbDriver("MySQL"))
$drvName
[1] "MySQL"

$connectionIds
$connectionIds[[1]]
<MySQLConnection:(6640,149)> 

$connectionIds[[2]]
<MySQLConnection:(6640,112)> 

$connectionIds[[3]]
<MySQLConnection:(6640,171)> 


$fetch_default_rec
[1] 500

$managerId
<MySQLDriver:(6640)> 

$length
[1] 16

$num_con
[1] 3

$counter
[1] 179

$clientVersion
[1] "5.5.20"

> dbListConnections(dbDriver("MySQL"))
[[1]]
<MySQLConnection:(6640,149)> 

[[2]]
<MySQLConnection:(6640,112)> 

[[3]]
<MySQLConnection:(6640,171)>

You don't use `SELECT LAST_INSERT_ID() FROM t`, there is no need to specify the "FROM" clause. The function `LAST_INSERT_ID()` will give you the last auto_increment generated by MySQL, therefore no table should be specified. Try with `SELECT LAST_INSERT_ID();`. Also, with your last query - `LAST_INSERT_ID` won't produce meaningful results (specifically, you won't get the proper value for the 3rd insert). — N.B., Apr 20 '12 at 12:49
@N.B.: thanks for answering. Simply copied the statements from the post, but even when I drop the `FROM` part: same picture :-/ — Rappster, Apr 20 '12 at 13:03
Scope out the dbGetInfo() and dbListConnections(). They should tell you some more useful info. — Jeff, Apr 20 '12 at 14:28
@Jeff: thanks, but I'm not sure if I fully understand what this info tells me exactly. "dbListConnections returns a list of all currently open connections on driver drv. Drivers that implement single connections would return the one single connection object." -> Is there a way to use a MySQL driver that implements single connections? — Rappster, Apr 20 '12 at 14:38

score 2 · Accepted Answer · answered Nov 05 '14 at 18:03

I found a working solution here. It's also mentioned in stephan mc's reply, but as the second option. The first one didn't work for me, so I figured this might be worth highlighting more.

Anyways, the trick is to run dbClearResult() between the INSERT and SELECT LAST_INSERT_ID():

> library("RMySQL")
> con <- dbConnect(MySQL())
> dbSendQuery(con, "DROP TABLE IF EXISTS t;")
> dbSendQuery(con, "CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);")
> res <- dbSendQuery(con, "INSERT INTO t VALUES (NULL);")

# doesn't work:
> dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                0

# works:
> dbClearResult(rs)
> dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                1

Jeff Allen · Answer 2 · 2012-04-20T13:21:48.143

0

You're inserting NULL values into the Primary Key column. Since you can't have two rows with the same PK, you're probably not actually inserting any real data (which is also probably an error you want to catch). Try:

dbSendQuery(con, "INSERT INTO t VALUES(5);")

Executing that should give you two different values for last_insert_id.

Edit: misunderstood. See here for the details on LAST_INSERT_ID. Revised answer: if you don't specify a value in an AUTO_INCREMENT column, then you should get a LAST_INSERT_ID value returned. In that case, try:

INSERT INTO t DEFAULT VALUES

edited Apr 20 '12 at 13:21

answered Apr 20 '12 at 13:09

Jeff Allen

17,277
8
49
70

Inserting `NULL` into an `AUTO_INCREMENT` column is exactly what makes it auto-increment. If your provide an explicit value, it will have no effect on `LAST_INSERT_ID()`. – Quassnoi Apr 20 '12 at 13:13
I adapted the second part of the example a little to make it more "real-life" like: one ID/UID column (`i`), on "actual data" column (`x`) and I inserted actual values for column `x`. Still no effect on the `LAST_INSERT_ID()`. This a bug? – Rappster Apr 20 '12 at 13:19
Strange... not sure what's going on there! – Jeff Allen Apr 20 '12 at 13:23
@JeffAllen: thanks for answering ;-) Not sure what you mean by `INSERT INTO t DEFAULT VALUES`. That a valid statement? Cecause I get an error running it for the "original" table t (i.e. only one column `i`). – Rappster Apr 20 '12 at 13:27
@Rappster: this is a valid statement in `SQL Server` but not in `MySQL`. As for the `MySQL` part, your original statements are alright, the problem (most probably) is that the connection switches. You may check it by issuing `SELECT CONNECTION_ID()` and see if the value persists. – Quassnoi Apr 20 '12 at 13:35
It was a last-ditch effort to insert data into your value-less table. Now that you have a table with two columns, you should be able to test it with more reasonable INSERT statements like you have. Nothing new above. – Jeff Allen Apr 20 '12 at 13:35
@Quassnoi: JACKPOT! Thanks, man! Feels good to at least know where things are going wrong. Any idea how to tweak the `dbSendQuery()`/`dbGetQuery()` functions to make them use a persistent connection? – Rappster Apr 20 '12 at 13:46

score 0 · Answer 3 · edited May 28 '14 at 06:25

0

We found a very interesting solution:

res <- dbSendQuery(con, "INSERT INTO t VALUES (5);")
res <- dbSendQuery(con, "SELECT LAST_INSERT_ID();")
fetch(res)

If it does not work, use a dbClearResult(res) before sending the last id request. For us it worked out.

edited May 28 '14 at 06:25

Gwenc37

2,064
7
18
22

answered May 28 '14 at 06:01

stephan mc

65
1
5

LAST_INSERT_ID() always returns 0 (RMySQL) - separate connection issue

Original example as found in some post

Following suggestions by N.B., Jeff Allen and Quassnoi

Findings as of 2012-04-20

Connection details

3 Answers3

Linked