CODEWITHSUNDEEP
mysqlrdbirmysql
jholmes
jholmes

Reputation: 49

How to fix "Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version" error in R?

So as of two days ago I installed MySQL version 8.0.17 and setup a database on a local server on my MACOS version 10.14.5. I have a dataframe in RStudio that I want to write to the only table under a database that I have just created but am unable to do so because of an error

I am able to establish a connection to this database and its able to find the table but I get this error when it gets to the DBI::dbWriteTable function:

Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version

I have seen other problems like this on the site but they are all about 2-7 years old and have been unable to help me. 

# {r database connection}
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "dbname",
                 host = "xxx.x.x.x",
                 port = xxxx,
                 user = "user",
                 password = "password",
)

dbWriteTable(con, name= "table", value= df, append= TRUE, temporary= FALSE)
dbDisconnect(con)

I am not quite sure where to go on with this process. Any advice or Open Source database alternatives would be greatly appreciated.

Upvotes: 2

Views: 6470

Answers (1)

D. Woods
D. Woods

Reputation: 3073

Converting @jholmes comment into an answer, this worked for me (I was having difficulty sending any dataframe using dbWriteTable and MySQL 8.0.18): 

# {r database connection}
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "dbname",
                 host = "xxx.x.x.x",
                 port = xxxx,
                 user = "user",
                 password = "password",
)

dbSendQuery(con, "SET GLOBAL local_infile = true;") # <--- Added this
dbWriteTable(con, name= "table", value= df, append= TRUE, temporary= FALSE)
dbDisconnect(con)

In order for the new line to work, I had to grant the RMySQL user the SUPER privilege as well.

It seems like there should be a better way, either by changing something in RMySQL or DBI.

Upvotes: 4

Related Questions