LAST_INSERT_ID () always returns 0 (RMySQL) - a separate connection problem

Question

Original example found in some post

According to this post, the following SQL statements should give me the vector 1, 2, 2, 2, 2 at 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 

The following offers NB , Jeff Allen and Quassnoi

I applied an example to resemble real use cases a little more than the original:

 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;") ix 1 1 1 2 2 2 

Well, that’s not so ,-)

I searched a little Google, and AFAIU, LAST_INSERT_ID() is a "connection notification" in the sense that the same connection should be used if it should work correctly. However, I thought that by assigning the connection object to con , I made sure that the connection was really the same used in each of the statements above.

Well, apparently not ;-) Can someone help me with some explanations and / or workarounds? Using something like select max(<ID>) from <TABLE> not going to shorten it, though, since I run several threads that write to the database at the same time, this messes up the identity lookup if that is the case.

Thanks!

Conclusions from 2012-04-20

• Thanks to Quassnoi, I managed to find the problem a little more. It seems that the RMySQL functions do not really care about the explicit argument conn , which is many, but opens new connections in the background every time you connect to the database. There are probably good reasons for this. However, does anyone know how to avoid this?
• Just contacted Jeffrey Horner (a supporter of the RMySQL package). This seems to be a problem with Windows. Worked for him on Linux: - /

Connection Information

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)> 

Answer 1

I found a working solution here . It is also mentioned in stephan mc's answer, but as a second option. The first did not work for me, so I decided that it could be worth emphasizing.

In any case, the trick is to run dbClearResult() between 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 

Answer 2

You are inserting NULL values ​​in the primary key column. Since you cannot have two rows with the same PK, you probably are not actually inserting any real data (this is also probably the error you want to catch). Try:

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

Doing this should give you two different values ​​for last_insert_id.

Edit: misunderstood. Learn more about LAST_INSERT_ID here . Revised answer: if you do not specify a value in the AUTO_INCREMENT column, you should get the return value LAST_INSERT_ID . In this case, try:

 INSERT INTO t DEFAULT VALUES 

Answer 3

We found a very interesting solution:

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

If this does not work, use dbClearResult(res) before sending the last id request. It worked out for us.