CODEWITHSUNDEEP
mysqlrrmysql
Neha
Neha

Reputation: 163

RMySQL - dbWriteTable() - The used command is not allowed with this MySQL version

I am trying to read a few excel files into a dataframe and then write to a MySQL database. The following program is able to read the files and create the dataframe but when it tries to write to the db using dbWriteTable command, I get an error message -
Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version

library(readxl)
library(RMySQL)
library(DBI)
mydb = dbConnect(RMySQL::MySQL(), host='<ip>', user='username', password='password', dbname="db",port=3306)
setwd("<directory path>")

file.list <- list.files(pattern='*.xlsx')
print(file.list)

dat = lapply(file.list, function(i){
print(i);
x = read_xlsx(i,sheet=NULL, range=cell_cols("A:D"), col_names=TRUE, skip=1, trim_ws=TRUE, guess_max=1000)
x$file=i
x
})

df = do.call("rbind.data.frame", dat) 

dbWriteTable(mydb, name="table_name", value=df,  append=TRUE ) 

dbDisconnect(mydb)

I checked the definition of the dbWriteTable function and looks like it is using load data local inpath to store the data in the database. As per some other answered questions on Stackoverflow, I understand that the word local could be the cause for concern but since it is already in the function definition, I don't know what I can do. Also, this statement is using "," as separator. But my data has "," in some of the values and that is why I was interested in using the dataframes hoping that it would preserve the source structure. But now I am not so sure. Is there any other way/function do write the dataframe to the MySQL tables?

Upvotes: 2

Views: 2332

Answers (2)

nickitalyano
nickitalyano

Reputation: 113

You may need to change dbWriteTable(mydb, name="table_name", value=df, append=TRUE ) to

dbWriteTable(mydb, name="table_name", value=df,field.types = c(artist="varchar(50)", song.title="varchar(50)"), row.names=FALSE, append=TRUE)

That way, you specify the field types in R and append data to your MySQL table.

Source:Unknown column in field list error Rmysql

Upvotes: 2

Ben
Ben

Reputation: 93

I solved this on my system by adding the following line to the my.cnf file on the server (you may need to use root and vi to edit!). In my this is just below the '[mysqld]' line

local-infile=1

Then restart the sever. Good luck!

Upvotes: 3

Related Questions