dbWriteTable fails with "Unknown column", event if creating table from scratch.

[s-fleck]

RMariaDB version: latest CRAN and ‘1.0.6.9000’ from github

I am getting the following error with dbWriteTable():

Error in result_create(conn@ptr, statement, is_statement) : 
  Unknown column '$1' in 'field list' [1054]

• The column names are exactly identical with the table in the database. In fact, i also get the error if I created a new table with dbWriteTable().
• The name of the table, as well as the column names are all lowercase names that just contain letters
• RMySQL does not have any issues with the same query
• DBI::dbSendQuery(conn, paste0("INSERT INTO logging_test VALUES (1, 2, 3, 4, 5)")) works

The data.frame I want to submit:

structure(list(level = c(600L, 600L), timestamp = structure(c(1547661557.72618, 
1547661557.72618), class = c("POSIXct", "POSIXt")), logger = c("root", 
"root"), caller = c("nope()", "nope()"), msg = c("ohno", "ohno"
)), class = "data.frame", row.names = c(NA, -2L))

possibly related SO

[krlmlr]

row names

Did you mean column names?

Unfortunately I can't replicate the problem:

library(DBI)

data <- structure(
  list(
    level = c(600L, 600L), timestamp = structure(c(
      1547661557.72618,
      1547661557.72618
    ), class = c("POSIXct", "POSIXt")),
    logger = c(
      "root",
      "root"
    ),
    caller = c("nope()", "nope()"), msg = c("ohno", "ohno")
  ),
  class = "data.frame", row.names = c(NA, -2L)
)

con <- dbConnect(RMariaDB::MariaDB(), dbname = "test")
dbWriteTable(con, "test", data, temporary = TRUE)
dbReadTable(con, "test")
#>   level           timestamp logger caller  msg
#> 1   600 2019-01-16 18:59:17   root nope() ohno
#> 2   600 2019-01-16 18:59:17   root nope() ohno

^{Created on 2019-01-26 by the reprex package (v0.2.1)}

[s-fleck]

Yes sorry, I meant column names (fixed in post). I also have no idea what caused the problem, everything worked fine and then it suddenly appeared, on travis (MySQL 5.7?) as well as locally. Currently I don't have a local MySQL install, but I'll try to set up one again so that maybe I can provide some more details that way :/.

[detroyejr]

I'm get the same error message on MySQL 5.6.34. It's proving hard to reproduce since I only run into this issue in production and not during testing. I've tried to narrow down the different conditions between the two instances. Here's what I've come up with.

For testing, I created a server with docker for Windows (if you do the same, be sure to modify the volume mount).

docker run \
  --name=mysql1 \
  -d \
  -e MYSQL_DATABASE=docker \
  -e MYSQL_USER=test \
  -e MYSQL_PASSWORD=mypassword \
  -e MYSQL_ROOT_PASSWORD=mypassword \
  -v C://Users/detro/Documents/mysql-test:/var/lib/mysql \
  -p 3306:3306 \
  mysql:5.6.34

On this test server, I can execute the following lines of code without errors.

# Connect to our test server.
conn <- RMariaDB::dbConnect(
    drv = RMariaDB::MariaDB(),
    user="root",
    password="mypassword",
    host = "localhost",
    dbname="docker",
    port=3306
)

# Sample Data.
sql_data <- structure(list(
    source = c("20273398", "20273398", "20273398", "20273398"),
    target = c("794538512", "367474028", "454340464", "36803580"),
    timestamp = c("2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31")
), class = "data.frame", row.names = c(NA, 4L))

DBI::dbWriteTable(
    conn = conn,
    name = "network_graph",
    value = sql_data,
    append = TRUE,
    overwrite = FALSE,
    row.names = FALSE
)

But, there's something different with the production server that causes this code to fail whenever I try to write to a new table or append to an existing table.

I've tried to isolate where the code fails below:

# Connect to our test server.
conn <- RMariaDB::dbConnect(
    drv = RMariaDB::MariaDB(),
    user="root",
    password="mypassword",
    host = "localhost",
    dbname="docker",
    port=3306
)

# Sample of the data I want to append.
sql_data <- structure(list(
    source = c("20273398", "20273398", "20273398", "20273398"),
    target = c("794538512", "367474028", "454340464", "36803580"),
    timestamp = c("2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31")
), class = "data.frame", row.names = c(NA, 4L))

# Params value used in RMariaDB:::dbSend.
params <- list(
    c("20273398", "20273398", "20273398", "20273398"),
    c("794538512", "367474028", "454340464", "36803580"),
    c("2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31", "2019-02-06 14:59:31")
)

is_statement <- TRUE

The function fails at this line.

# Query executed by MySQL on docker. Works fine and appends the data or create the table.
query1 <- new("SQL", .Data = "INSERT INTO `network_graph`\n  (`source`, `target`, `timestamp`)\nVALUES\n  (?, ?, ?)")

# Query executed by the production server.
query2 <- new("SQL", .Data = "INSERT INTO `network_graph`\n  (`source`, `target`, `timestamp`)\nVALUES\n  ($1, $2, $3)")

rs <- new("MariaDBResult", sql = query1, ptr = RMariaDB:::result_create(conn@ptr, 
                                                                        query1, is_statement), bigint = conn@bigint)

rs <- new("MariaDBResult", sql = query2, ptr = RMariaDB:::result_create(conn@ptr, 
                                                                        query2, is_statement), bigint = conn@bigint)
#> Warning in RMariaDB:::result_create(conn@ptr, query2, is_statement):
#> Cancelling previous query
#> Error in RMariaDB:::result_create(conn@ptr, query2, is_statement): Unknown column '$1' in 'field list' [1054]

So, it seems the wrong placeholders are being produced or the values are not being substituted properly.

Just as a test I tried changing ($1, $2, $3) to (?, ?, ?) during debugging and the query was submitted successfully to the production server.

DBI:::sqlAppendTableTemplate

These two queries are different because of this function.

The value fields is created at line 81. It's the same for query 1 and query 2.

fields <- new("SQL", .Data = c("`source`", "`target`", "`timestamp`"))

Query 1 and 2 diverge at line 83 because of the pattern and prefix parameters.

Query 1

The pattern parameter is set to "" so these placeholders are produced:

# The suffix is created.
suffix <- rep("", length(fields))

placeholders <- list(source = new("SQL", .Data = "?"), target = new("SQL", .Data = "?"), 
    timestamp = new("SQL", .Data = "?"))

Query 2

The pattern parameter is set to 1 (prefix is "$") so these placeholders are produced:

suffix <- as.character(seq_along(fields))

placeholders <- list(source = new("SQL", .Data = "$1"), target = new("SQL", .Data = "$2"), 
    timestamp = new("SQL", .Data = "$3"))

Since there's a lot of C++ code sprinkled in here, I'm not very effective at debugging beyond this point. I haven't quite figured out what determines the values for pattern, prefix, and suffix.

Hopefully, this is at least a little bit helpful! Let me know if you have any suggestions or any clarifications I can make.

Edit

One more quick note. Just tested dbWriteTable using the driver from RMySQL and the issue was not present. So, it seems to be isolated to RMariaDB.

^{Created on 2019-02-07 by the reprex package (v0.2.1)}

[krlmlr]

Thanks. Just to double-check: what versions of DBI and RMariaDB are you using when connecting to the test and to the production server?

[detroyejr]

RMariaDB: 1.0.6 and DBI: 1.0.0.

[krlmlr]

Thanks. Seems like RPostgres hijacks the dbAppendTable() implementation for "DBIConnection", which is used by RMariaDB. (And the S4 class system in R doesn't even prohibit this or warn about it.)

Does the production server load/attach RPostgres?

Thanks @detroyejr for the very detailed and helpful analysis, I had to reread multiple times.

[krlmlr]

I have pushed a fix to RPostgres to GitHub, can you please double-check?

[s-fleck]

I will also try to check but it will take a few days till I get around to it (i have to setup both mariadb and postgres at my private pc at home).

edit: i verified that my problem also came from RPostgres, I was not able to check with the github version yet.

[s-fleck]

Updating Rpostgres to the latest version on github (1.1.1.9002) fixes the issue for me, thanks

[s-fleck]

Closed by r-dbi/RPostgres@f79b7c2 / r-dbi/RPostgres@fc93f1c

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.