4

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?

Improve this question

1 Answer 1

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

For me, it said #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
It works for me, here is a way to save this option? Each time I restart mysql I need to re-type the commands

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.