2

So as of two days ago I installed MySQL version 8.0.17 and setup a database on a local server on my MACOS version 10.14.5. I have a dataframe in RStudio that I want to write to the only table under a database that I have just created but am unable to do so because of an error

I am able to establish a connection to this database and its able to find the table but I get this error when it gets to the DBI::dbWriteTable function:

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

I have seen other problems like this on the site but they are all about 2-7 years old and have been unable to help me. 

# {r database connection}
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "dbname",
                 host = "xxx.x.x.x",
                 port = xxxx,
                 user = "user",
                 password = "password",
)

dbWriteTable(con, name= "table", value= df, append= TRUE, temporary= FALSE)
dbDisconnect(con)

I am not quite sure where to go on with this process. Any advice or Open Source database alternatives would be greatly appreciated.

jholmes
  • 49
  • 1
  • 7
  • Please post the R command `str(mydataframe)` and MySQL's command `SHOW CREATE TABLE mytable`. Variables in these structures must align by types and names in order. You may be attempting to append to an autonumber field or some other variable type mismatch. – Parfait Sep 02 '19 at 15:55
  • This error message usually means that the code tried to execute load local data infile statement in MySQL to import data. This statement is usually disallowed for security reasons. I fyou search for the error message, you will find solutions how to enable this statement or import the data in a different way. – Shadow Sep 02 '19 at 16:34
  • @Parfait The variables all align by name and order exactly. But some of the variables don't exactly align by type. Specifically all the factors from the R dataframe are listed as integers in the MySQL database would that cause a problem? – jholmes Sep 03 '19 at 08:48
  • @Shadow Do you mean look through MySQL for the error code? Would it show up there when I run code in R? – jholmes Sep 03 '19 at 08:59
  • You already have the mysql error. The error message in the question comes from mysql, not from R. – Shadow Sep 03 '19 at 12:22
  • Yes, try converting as factors usually resolve as string types: `df[sapply(df, is.factor)] <- sapply(df[sapply(df, is.factor)], function(x) as.integer(as.character(x)))` – Parfait Sep 03 '19 at 12:24
  • @Parfait I tried changing all my factor variables to integers and character variables respectively and made sure the columns matched up but unfortunately still got the same error. Would the fact that the character variables in the MySQL database are stored as varchar(255) types affect anything? – jholmes Sep 04 '19 at 11:59
  • @Shadow Ok. I will look deeper into information around the error. Hopefully I can find something. Thank you for your input. – jholmes Sep 04 '19 at 12:00
  • 1
    On server run `SET GLOBAL local_infile = true` and possibly restart server. This looks to be due to MySQL 8 issue versus previous versions. – Parfait Sep 04 '19 at 13:03
  • @Parfait if you dynamically change a setting and then restart the server, then you use the change... – Shadow Sep 04 '19 at 15:36
  • 1
    @Parfait the 'SET GLOBAL local_infile = true' command solved my issue It seems that local_infile is set to false as default for security reasons. Thank you so much for your help! – jholmes Sep 05 '19 at 05:58
  • @Shadow Thank you so much for your assistance as well. I made sure to not reset the server because it returns to all default settings that were dynamically changed – jholmes Sep 05 '19 at 06:00
  • I had the same error but mine was due to my hard drive being full – Rodrigo Zepeda Jan 16 '20 at 00:03

1 Answers1

4

Converting @jholmes comment into an answer, this worked for me (I was having difficulty sending any dataframe using dbWriteTable and MySQL 8.0.18): 

# {r database connection}
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "dbname",
                 host = "xxx.x.x.x",
                 port = xxxx,
                 user = "user",
                 password = "password",
)

dbSendQuery(con, "SET GLOBAL local_infile = true;") # <--- Added this
dbWriteTable(con, name= "table", value= df, append= TRUE, temporary= FALSE)
dbDisconnect(con)

In order for the new line to work, I had to grant the RMySQL user the SUPER privilege as well.

It seems like there should be a better way, either by changing something in RMySQL or DBI.

D. Woods
  • 3,004
  • 3
  • 29
  • 37