Error in RMySQL package

Question

I am using RMySQL package to write (append) data in current table. I am using R, version 3.3.2.

My code looks like this:

library(RMySQL)
df_final <- some_data
m<-dbDriver("MySQL")
mydb <- dbConnect(m, user='odvjet12_mislav',
                  password='my_pass', 
                  host='91.234.46.219', 
                  dbname='odvjet12_fina_pn')

dbWriteTable(mydb, value = df_final, name = "fina_pn", append = TRUE, row.names = FALSE)

This code works fine for some time, but in last ten days, it always return an error:

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

I don't understand how it is possible for code to work for some time and now, it returns an error?

I kindly ask for feedback on this issue.

Best,

Mislav Šagovac

Answer provided http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile — Saurabh Chauhan, Jan 21 '17 at 12:43
I am using RMySQL package and not python and SQL syntax. How can I add `--local-infile=1` part in my function? — Mislav, Jan 21 '17 at 12:50
I think you need to provide `--local-infile=1` command while you open MySQL from terminal as suggested [here] (http://stackoverflow.com/questions/16285864/how-can-i-correct-mysql-load-error) — Saurabh Chauhan, Jan 21 '17 at 13:01
As I understand, I shouldn't use RMySQL package, but run everything from the terminal? — Mislav, Jan 21 '17 at 13:08
In your terminal type: `mysql --local-infile=1 -u username -p` (for ubuntu user) — Saurabh Chauhan, Jan 21 '17 at 13:18
I returns `mysql is not recognized as an internal or external command, operable program or batch file`. I don't have server locally. — Mislav, Jan 21 '17 at 13:21
Before, it works properly, when I was using code above. I have to say that my code above create a new table (I checked it through php admin), but it doesn't enter data in the table. — Mislav, Jan 21 '17 at 13:23
Possible duplicate of [How can I correct MySQL Load Error](http://stackoverflow.com/questions/16285864/how-can-i-correct-mysql-load-error) — EdChum, Jan 21 '17 at 21:27

score 0 · Answer 1 · answered Sep 07 '18 at 20:56

You could also use dbGetQuery from the RMySQL package and iterate over the rows, which was my solution when I reached a similar error for a dataframe I wanted to write to a MySQL DB:

mydb = dbConnect(MySQL(), user='user', password='password', dbname='databasename', host='hostname')
for(i in 1:nrow(df)){
dbGetQuery(mydb,paste0("INSERT INTO MYTABLE (COL1,COL2) VALUES(",df$col1[i],",",df$col2[i],")"))
}

Error in RMySQL package

1 Answers1