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

Question

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?

Answer 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)!