copy_to and MySQL under Windows -- not escaping backslash in temp file name

[nealdryan]

Under Windows 7 64-bit using MySQL 5.1, build RMySQL per directions and it correctly writes out dataframe using "dbWriteTable(con, "doesitwork", df).

Using dplyr both version from CRAN and latest version from GitHub show same problem:

[nealdryan]

Sorry, rest of report below:

Using just RMySQL I can use dbConnect and then dbWriteTable to take a data frame and put it in MySql so the underlying RMySQL bits are working.

Using dplyr both version from CRAN and latest version from GitHub show same problem:

x <- src_mysql("test")  # works
a <- tbl(x, "dxtable" # works
copy_to(x, df, "dftest") # failes with error
Error in mysqlExecStatement(conn, statement, ...) : 
yanndAppDataLocalTempRtmpuo5052file26cc680346b9.csv' not found (Errcode: 22))

note that the temp file it was trying to write was indeed at
c:\Users\Ryannd\AppData\Local\Temp\Rtmpuo....csv
so it looks like somewhere in the copy_to code there is insufficient backslash escaping

[hadley]

Could you please set options("dplyr.show_sql" = TRUE) and show me query starting with "LOAD DATA LOCAL INFILE". It works for me on the mac, so I'm not sure what's going wrong with the path escaping.

[nealdryan]

Here you go:

copy_to(db,a,"test")
START TRANSACTION
CREATE TEMPORARY TABLE test (b integer, c integer)
LOAD DATA LOCAL INFILE 'C:\Users\ryannd\AppData\Local\Temp\RtmpSSPT1B\file1440440e4353.csv' INTO TABLE test
Error in mysqlExecStatement(conn, statement, ...) :
yanndAppDataLocalTempRtmpSSPT1Bfile1440440e4353.csv' not found (Errcode: 22))

When I go into MySQL Workbench and execute the original LOAD DATA command it doesn’t work but when I execute the same command except replacing “\” with “/”, it does works.

Alternatively, replacing each “\” with “\” in the SQL statement also works when submitted from MySQL workbench.

[hadley]

Ok, two more lines of code and I think I'll be able to fix it. What does this code return?

tempfile(fileext = ".csv")
normalizePath(tempfile(fileext = ".csv"), mustWork = F)

[nealdryan]

It returns:

tempfile(fileext = ".csv")
[1] "C:\Users\ryannd\AppData\Local\Temp\RtmpOQfycR\file21e837e291d.csv"
normalizePath(tempfile(fileext = ".csv"), mustWork = F)
[1] "C:\Users\ryannd\AppData\Local\Temp\RtmpOQfycR\file21e827ec7cd5.csv"

[hadley]

I'm pretty sure that should fix it. Let me know if it doesn't work for you.

[nealdryan]

Yup. That fixed it. Many thanks.

From: Hadley Wickham [mailto:notifications@github.com]
Sent: Wednesday, March 19, 2014 4:08 PM
To: hadley/dplyr
Cc: Ryan, Neal
Subject: Re: [dplyr] copy_to and MySQL under Windows -- not escaping backslash in temp file name (#323)

I'm pretty sure that should fix it. Let me know if it doesn't work for you.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/323#issuecomment-38100384.