Trying to use R with MySQL: The used command is not allowed with this MySQL version

问题: I am trying import a data set into a table in MySQL. I am using MySQL 8.0.11 and version 1.1.453 of R Studio. I am using the RMySQL package. The line in question is: db...

问题:

I am trying import a data set into a table in MySQL. I am using MySQL 8.0.11 and version 1.1.453 of R Studio. I am using the RMySQL package.

The line in question is:

dbWriteTable(ajppDb, value = EvanData, row.names = FALSE, name = "summaryfile2015", append = TRUE ) 

and I get this response:

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

This line worked for me in version 5.7 of MySQL but not for the newer version so I'm not sure what has changed or what I am doing wrong. Does anyone know how I can import my data using R?


回答1:

The following steps should fix the dbWritetable() error in R:

  1. Login MySQL terminal by typing "MySQL -u user -p*" (followed by user password if you set one).

  2. Type "SET GLOBAL local_infile = true;" in the MySQL terminal command.

  3. Lastly, type "SHOW GLOBAL VARIABLES LIKE 'local_infile';" into the terminal and check the command line output for the ON status.

I'm not sure why the database function fails from MySQL 5.6 to 8.0, however, "local_infile" enables user access to data loads from local sources--- this solution should work for all database interference stacks (R, Python, etc)!

  • 发表于 2019-03-18 19:37
  • 阅读 ( 309 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除