LAST_INSERT_ID()始终返回0(RMySQL)-单独的连接问题 [英] LAST_INSERT_ID() always returns 0 (RMySQL) - separate connection issue

问题描述

根据此帖子以下SQL声明应该给我一个矢量 1, 2, 2, 2, 2最后:

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

根据 NB ， Quassnoi

对示例进行了修改，使其比实际用例更原始:

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

我已经搜索了一下，并且AFAIU，LAST_INSERT_ID()是可感知连接的"，这意味着必须使用相同的连接才能正常工作.但是，我认为通过将连接对象分配给con，我可以确保在上述每个语句中确实使用了相同的连接 .

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.

好吧，显然不是;-)有人可以帮我一些解释和/或解决方法吗? 但是，使用select max(<ID>) from <TABLE>之类的东西并不会减少它，因为我正在运行同时写入数据库的多个线程，因此如果这样做的话会搞乱ID检索.

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.

谢谢！

• 由于 Quassnoi ，我能够找到更多的问题了.似乎RMySQL函数并不真正关心显式的conn参数，但是每次您连接到DB时都会在后台打开新连接.大概也有一些很好的理由.但是，有人知道如何避免这种情况吗?
• 只需联系Jeffrey Horner( RMySQL 的维护者包裹).似乎这是Windows问题.在Linux上为他工作:-/

• 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 :-/

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

推荐答案

我找到了可行的解决方案

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.

无论如何，诀窍是在INSERT和SELECT LAST_INSERT_ID()之间运行dbClearResult():

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

这篇关于LAST_INSERT_ID()始终返回0(RMySQL)-单独的连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！