could not run statement: The used command is not allowed with this MySQL version

[mislav0207]

Hi,

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

[mislav0207]

I have found that that I should add an argument --local-infile=1, but I don't know how to do that in dbWriteTable function?

[krlmlr]

Thanks. Have you installed RMySQL from CRAN or from GitHub?

[MathieuGug]

I have the same issue, from the CRAN and GitHub install.

[psychelzh]

Here is the same issue.

conn <- DBI::dbConnect(RMySQL::MySQL(), dbname = "mydb", username = "root", password = "passwd")
DBI::dbWriteTable(conn, name = "test", value = cars)
#> Error in .local(conn, statement, ...) : 
#>  could not run statement: The used command is not allowed with this MySQL version

MySQL installation info:

Sessioninfo:

R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=Chinese (Simplified)_China.936  LC_CTYPE=Chinese (Simplified)_China.936   
[3] LC_MONETARY=Chinese (Simplified)_China.936 LC_NUMERIC=C                              
[5] LC_TIME=Chinese (Simplified)_China.936    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RevoUtils_11.0.0     RevoUtilsMath_11.0.0

loaded via a namespace (and not attached):
[1] compiler_3.5.0 tools_3.5.0    yaml_2.1.19   

[psychelzh]

I could confirm that the table to be written was actually created, only with no records of data but the field names.

library(DBI)
con <- dbConnect(RMySQL::MySQL(), user = "root", password = "passwd", dbname = "mydb")
dbListTables(con)
#> [1] "mtcars" "test"
dbReadTable(con, "test")
#> [1] speed dist 
#> <0 行> (或0-长度的row.names)

[mttzr]

I'm running into the exact same issue.

[krlmlr]

Can you try editing the .cnf file as suggested in https://stackoverflow.com/a/20722521/946850? The --local-infile switch is for the mysql command line application only, but it seems that you can pass this option through the configuration file.

[fdetsch]

In my case, the solution provided by @nickitalyano here solved the issue.