dbWriteTable: Error occur if table name is "READ" (reserved word)

Question

I am trying to upload a tibble to mysql server, using function from DBI package in R.

however, I bump into a error if the table name is "READ" or "READs".

# an simple table
> x <- tibble(x=rep("a",5))

> dbWriteTable(DB, "READ", x, overwrite = TRUE, row.names = FALSE)
Error in .local(conn, statement, ...) : 
  could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'READ 
( `x` text 
)' at line 1

Successfully upload if change to other names,

> dbWriteTable(DB, "READa", x, overwrite = TRUE, row.names = FALSE)
[1] TRUE

I don't understand what is the syntax error here...

Could someone explain it, please?

READ is a MySQL reserved keyword. Do [backticks](https://stackoverflow.com/a/23446378/13513328) help? — Waldi, Aug 03 '20 at 14:23
Not work. Instead, it create a empty table named "READ". `> dbWriteTable(DB, "\`READ\`", x, overwrite = TRUE, row.names = FALSE) Error in .local(conn, statement, ...) : could not run statement: Table 'TEST.\`READ\`' doesn't exist` I think your point about reserved word is right. — PWbio, Aug 03 '20 at 14:33
With *backtick* you can use reserved words as name for tables or columns. do `> dbCreateTable(con, "\`READ\`", ...` first and check the result. — Majid Hajibaba, Aug 03 '20 at 15:11
@majidhajibaba Now it works. I create table first `dbCreateTable(DB, "READ", x)` and then append data to it `dbWriteTable(DB, "READ", x, append = TRUE, row.names = FALSE)`. If I create table name with _backtick_ through `dbCreateTable`, the table will be named exactly **\`READ\`**. — PWbio, Aug 03 '20 at 16:17

score 0 · Answer 1 · answered Aug 04 '20 at 05:29

After searching around, I think this question is duplicated, but I found not concluded answer. So I will post what I tried to solve the problem.

As discussed in the comments, "READ" is a reserved word in MySQL. And, backtick does not solve the error in function dbWriteTable. Interestingly, "READ" does not prompt an error using dbCreateTable.

So the trick is to create table with dbCreateTable and then append data with dbWriteTable.

Here is the code I tried:

dbCreateTable(DB, "READ", table,
               fields = c(Sample_PatientID = "varchar(20)",
                          Sample_Type = "tinyint",
                          Sample_Analytic = "varchar(20)",
                          GeneID = "integer",
                          Value = "decimal(17,10)"))

dbWriteTable(DB, "READ", table, append = TRUE, row.names = FALSE)

# fields is for defining datatype, as field.types in dbWriteTable.

dbWriteTable: Error occur if table name is "READ" (reserved word)

1 Answers1