1

I'm working on a MySQL server on which I have CREATE TEMPORARY privileges but no 'regular' CREATE privileges. Furthermore, I'm working with RMySQL.

I'm experiencing the following strange behavior which to me either indicates that (a) there is a bug in the RMySQL package or (b) I'm completely missing something else.

When I try to write a data.frame to a temporary table like so:

df <- data.frame(IDX=c(1:10), IDY=c(10:1))
dbWriteTable(con, "Mapping", df, row.names=FALSE, overwrite=TRUE, temporary=TRUE)

I get an error could not run statement: CREATE command denied to user indicating that the temporary=TRUE is not added to the CREATE statement correctly.

Why I try to work around this by first adding the temporary table myself and then adding the data manually, I run into another issue:

dbSendQuery(con, "CREATE TEMPORARY TABLE Mapping (IDX INT, IDY INT, PRIMARY KEY (IDX, IDY));")
out <- dbGetQuery(con, "SHOW COLUMNS FROM Mapping;"); out
         Field    Type Null Key Default Extra
1    ContestID int(11)   NO PRI       0      
2 SubmissionID int(11)   NO PRI       0      
sql <- sqlAppendTable(con, "Mapping", mapping, row.names=FALSE)
rs <- dbExecute(con, sql)

I get an could not run statement: Got a packet bigger than 'max_allowed_packet' bytes error.

Can anyone help? Is there another way to get this working?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Chris
  • 1,479
  • 2
  • 15
  • 19
  • Where in the RMySQL [docs](https://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf) is there a *temporary* arg in `dbWriteTable()`? As for last issue, simply update your MySQL [settings](https://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size) and then restart server. – Parfait Jul 08 '17 at 20:04

0 Answers0