copy_to with in_schema fails with DBI 1.0.0

[tidyverse]

copy_to with in_schema fails with DBI 1.0.0

[hadley]

I'm going to close this issue — it seems like there might actually be a few different related issues, and the one reprex seems to be ok. Please feel free to create a new issue with a reprex, or if you can't easily create a reprex including the output of traceback() will help me track the problem down.

@krlmlr commented on Jun 7, 2018, 12:14 PM UTC:

Thanks. Does it work if you use DBI::Id(schema = "TEMP", name = "MTCARS") instead of in_schema(...) ? We need to think of a way to make dbplyr comatible with DBI >= 1.0.0 again.

@OssiLehtinen commented on Jun 7, 2018, 12:27 PM UTC:

Unfortunately, doesn't seem to work either.

copy_to(con, mtcars, name=DBI::Id(schema = "TEMP", name = "MTCARS"))
#> Error: is_string(x = name) is not TRUE

[neillrobson]

I believe that I am having a related issue. (If not, I can create a new issue as well.)

For me, the error is:

copied <- copy_to(conn, coc_data, in_schema("sandbox", "coc_data"))
#> Error in result_create(conn@ptr, statement, is_statement) : 
#>   You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLEsandbox.coc_data' at line 1 [1064]

For context, coc_data is a data.frame and conn was defined as follows:

conn <- dbConnect(MariaDB(), host = "localhost", port = "3306", user = "root", db = "devint")

(minimum reproducible example, so I left out a root password.)

The MySQL instance running on my local machine was given the commands CREATE DATABASE devint; CREATE DATABASE sandbox; before anything else occurred.

Surprisingly, when I try to pull up the created table manually (using tbl), it looks like progress was actually made:

taken <- tbl(conn, in_schema("sandbox", "coc_data"))
taken
#> Source:   table<sandbox.coc_data> [?? x 9]
#> Database: mysql 5.7.18 [root@localhost:/devint]
#> ... with 9 variables: id <int>, pull_request_count <int>, url <chr>, web_url <chr>,
#>   has_coc <chr>, time_created <chr>, location <chr>, taken_from <chr>, remarks <chr>

The issue looks like a simple whitespace issue (see TABLEsandbox.coc_data), but I don't know how I could solve the problem.

EDIT: So, it looks like if I just add leading whitespace to the first argument of in_schema, the query works without error:

copied <- copy_to(conn, coc_data, in_schema(" sandbox", "coc_data"))

So, it is a workaround, but the underlying issue still remains.

I have the same issue than @OssiLehtinen on a Windows 32 bit workstation connected to a Oracle DB.

copy_to(con, cars, name=in_schema("TEST", "CARS"))
#> Error: Can't unquote TEST.CARS

We tried also adding a whitespace before, without success. We tried several other workaround.

Something interesting : on windows 64 bit workstation , it works perfectly connected to the same Oracle DB.

Here se sessionInfo() of the Windows 32 bit Workstation with the bug :

R version 3.5.1 (2018-07-02)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows 7 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=French_France.1252  LC_CTYPE=French_France.1252    LC_MONETARY=French_France.1252
[4] LC_NUMERIC=C                   LC_TIME=French_France.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] dplyr_0.7.8  dbplyr_1.2.2 odbc_1.1.6   tidyr_0.8.2 

loaded via a namespace (and not attached):
[1] Rcpp_1.0.0       rstudioapi_0.8   bindr_0.1.1      magrittr_1.5     hms_0.4.2        tidyselect_0.2.5
[7] bit_1.1-14       R6_2.3.0         rlang_0.3.0.1    blob_1.1.1       tools_3.5.1      DBI_1.0.0       
[13] bit64_0.9-7      assertthat_0.2.0 tibble_1.4.2     crayon_1.3.4     bindrcpp_0.2.2   purrr_0.2.5     
[19] glue_1.3.0       stringi_1.2.4    compiler_3.5.1   pillar_1.3.0     pkgconfig_2.0.2 

Here the windows 64 bit workstation sessionInfo():

R version 3.5.1 (2018-07-02)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=French_France.1252  LC_CTYPE=French_France.1252   
[3] LC_MONETARY=French_France.1252 LC_NUMERIC=C                  
[5] LC_TIME=French_France.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] dplyr_0.7.8  dbplyr_1.2.1 odbc_1.1.5  

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.0       utf8_1.1.3       crayon_1.3.4     assertthat_0.2.0
 [5] R6_2.2.2         DBI_1.0.0        magrittr_1.5     pillar_1.2.1    
 [9] rlang_0.3.0.1    rstudioapi_0.7   blob_1.1.1       bindrcpp_0.2.2  
[13] tools_3.5.1      bit64_0.9-7      glue_1.3.0       purrr_0.2.4     
[17] bit_1.1-13       hms_0.4.2        yaml_2.1.18      compiler_3.5.1  
[21] pkgconfig_2.0.1  tidyselect_0.2.4 bindr_0.1.1      tibble_1.4.2  

[alexhallam]

I am having the same issue

df1e4 <- rnorm(n = 1e4) %>% as.tibble() 
dplyr::copy_to(con_azure, df = df1e4, DBI::Id(schema = "my_schema", name = "test"))

Error: is_string(x = name) is not TRUE

[hadley]

Can someone please provide a reprex using recent versions of dplyr/dbplyr/DBI?

[cecile-nhu]

copy_to(vertica_conn, data, DBI::Id(schema = "NEWS_ROS", name = "test"))
#> Error in copy_to(vertica_conn, data, DBI::Id(schema = "NEWS_ROS", name = "test")): could not find function "copy_to"

After that, I tried this:

dplyr::copy_to(vertica_conn, data, DBI::Id(schema = "NEWS_ROS", 
    name = "test"))
#> Error in dplyr::copy_to(vertica_conn, data, DBI::Id(schema = "NEWS_ROS", : object 'vertica_conn' not found

vertica_conn is completely fine as I tried collecting a small table from it with no problems after running these commands.

[hadley]

@cecile-nhu you can read about what a reprex is at https://reprex.tidyverse.org

[hadley]

@edgararuiz could you please try and make a reprex for this too? You can use schema with SQLite

[edgararuiz-zz]

I'm not able to recreate an error. It works against SQLite

library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbSendQuery(con, "ATTACH DATABASE ':memory:' AS test;")
#> <SQLiteResult>
#>   SQL  ATTACH DATABASE ':memory:' AS test;
#>   ROWS Fetched: 0 [complete]
#>        Changed: 0
copy_to(con, mtcars, in_schema("test", "mtcars"), temporary = FALSE)
#> Warning: Closing open result set, pending rows
head(tbl(con, in_schema("test", "mtcars")))
#> # Source:   lazy query [?? x 11]
#> # Database: sqlite 3.22.0 [:memory:]
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> 4  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1
#> 5  18.7     8   360   175  3.15  3.44  17.0     0     0     3     2
#> 6  18.1     6   225   105  2.76  3.46  20.2     1     0     3     1
dbDisconnect(con)
packageVersion("DBI")
#> [1] '1.0.0.9001'

^{Created on 2019-01-10 by the reprex package (v0.2.1)}

[alexhallam]

I can run the RSQLite::SQLite() reprex above also, but I am having issues with my company database. This is not much of a reprex, but if I share more I would be exposing credentials.

library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbSendQuery(con, "ATTACH DATABASE ':memory:' AS test;")
#> <SQLiteResult>
#>   SQL  ATTACH DATABASE ':memory:' AS test;
#>   ROWS Fetched: 0 [complete]
#>        Changed: 0
copy_to(con, mtcars, in_schema("test", "mtcars"), temporary = FALSE)
#> Warning: Closing open result set, pending rows
head(tbl(con, in_schema("test", "mtcars")))
#> # Source:   lazy query [?? x 11]
#> # Database: sqlite 3.22.0 [:memory:]
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> 4  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1
#> 5  18.7     8   360   175  3.15  3.44  17.0     0     0     3     2
#> 6  18.1     6   225   105  2.76  3.46  20.2     1     0     3     1
dbDisconnect(con)
packageVersion("DBI")
#> [1] '1.0.0'

# My Company DB
con_azure <- DBI::dbConnect(odbc::odbc(), Driver = "SQL Server Native Client 11.0", 
                            Server = Sys.getenv("server"), 
                            Database = Sys.getenv("database"), UID = Sys.getenv("uid"), PWD = Sys.getenv("pwd"))
dbGetQuery(con_azure, "SELECT 'this is a test'")
#>                 
#> 1 this is a test
copy_to(con_azure, mtcars, in_schema(Sys.getenv("schema"), "mtcars"), temporary = FALSE)
#> Error: <SQL> 'CREATE TABLE "schemaR_PROFILE=C:\\Users\\AlexH\\AppData\\Local\\Temp\\8\\Rtmp2Hpdhb\\file3d68128b33ac.mtcars" (
#>   "mpg" FLOAT,
#>   "cyl" FLOAT,
#>   "disp" FLOAT,
#>   "hp" FLOAT,
#>   "drat" FLOAT,
#>   "wt" FLOAT,
#>   "qsec" FLOAT,
#>   "vs" FLOAT,
#>   "am" FLOAT,
#>   "gear" FLOAT,
#>   "carb" FLOAT
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][SQL Server Native Client 11.0][SQL Server]111212;Operation cannot be performed within a transaction.
packageVersion("DBI")
#> [1] '1.0.0'

^{Created on 2019-01-10 by the reprex package (v0.2.1)}

[hadley]

@alexhallam can you please confirm that the name of your schema is schemaR_PROFILE=C:\\Users\\AlexH\\AppData\\Local\\Temp\\8\\Rtmp2Hpdhb\\file3d68128b33ac ?

[alexhallam]

hmm, that is odd. reprex puts that in my clipboard, but this is what I see in my console

Error: <SQL> 'CREATE TABLE "schema.mtcars" (
  "mpg" FLOAT,
  "cyl" FLOAT,
  "disp" FLOAT,
  "hp" FLOAT,
  "drat" FLOAT,
  "wt" FLOAT,
  "qsec" FLOAT,
  "vs" FLOAT,
  "am" FLOAT,
  "gear" FLOAT,
  "carb" FLOAT
)
'
  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][SQL Server Native Client 11.0][SQL Server]111212;Operation cannot be performed within a transaction.

[hadley]

Ok, it must be getting the env var from somewhere else.

Are you sure the problem only occurs with in_schema()? That error suggests the problem comes from the transaction that db_copy_to uses.

[alexhallam]

Thanks for pointing that out. I could be misinterpreting this error. Maybe it is not in in_schema() issue. I will try a few other things on my own. This is the most amount of help I can be for now. Maybe someone else can provide more insight into the issues that they have been having.

[ilarischeinin]

It seems there are a few related issues (#257, #323, r-dbi/odbc/issues/265), and for at least some of them the underlying problem is not in dbplyr, but in DBI. But below is a reprex of a problem I get with dbplyr and PostgreSQL that does work with DBI directly. The reprex requires docker as that's used to run the PostgreSQL server.

The problem is using copy_to() to write non-temporary tables to explicit schemas.

Run the server:

docker pull postgres:12.0
docker run \
  --detach \
  --env POSTGRES_PASSWORD=reprex \
  --name reprex \
  --publish 5432:5432 \
  --rm \
  postgres:12.0

The R reprex:

con <-
  DBI::dbConnect(
    RPostgres::Postgres(),
    host = "127.0.0.1",
    dbname = "postgres",
    user = "postgres",
    password = "reprex"
  )

x <- data.frame(i = 1:10)

DBI::dbExecute(con, "CREATE SCHEMA foobar;")

# works; goes to schema "public"
DBI::dbWriteTable(con, "x_dbi", x)

# works; goes to schema "public":
dplyr::copy_to(con, x, "x_dplyr", temporary = FALSE)

# works; goes to schema "foobar":
DBI::dbWriteTable(con, DBI::Id(schema = "foobar", table = "x_dbi"), x)

# doesn't work:
dplyr::copy_to(con, x, dbplyr::in_schema("foobar", "x_dplyr"), temporary = FALSE)
# Error: Can't unquote foobar.x_dplyr

Clean up:

docker stop reprex
docker rmi postgres:12.0

My session info:

─ Session info ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.1 (2019-07-05)
 os       macOS Catalina 10.15        
 system   x86_64, darwin15.6.0        
 ui       X11                         
 language (EN)                        
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       Europe/Helsinki             
 date     2019-10-31                  

─ Packages ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version date       lib source        
 assertthat    0.2.1   2019-03-21 [1] CRAN (R 3.6.0)
 backports     1.1.5   2019-10-02 [1] CRAN (R 3.6.0)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.6.0)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.6.0)
 blob          1.2.0   2019-07-09 [1] CRAN (R 3.6.0)
 callr         3.3.2   2019-09-22 [1] CRAN (R 3.6.0)
 cli           1.1.0   2019-03-19 [1] CRAN (R 3.6.0)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.6.0)
 DBI           1.0.0   2018-05-02 [1] CRAN (R 3.6.0)
 dbplyr        1.4.2   2019-06-17 [1] CRAN (R 3.6.0)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.6.0)
 devtools      2.2.1   2019-09-24 [1] CRAN (R 3.6.0)
 digest        0.6.21  2019-09-20 [1] CRAN (R 3.6.0)
 dplyr         0.8.3   2019-07-04 [1] CRAN (R 3.6.0)
 ellipsis      0.3.0   2019-09-20 [1] CRAN (R 3.6.0)
 fs            1.3.1   2019-05-06 [1] CRAN (R 3.6.0)
 glue          1.3.1   2019-03-12 [1] CRAN (R 3.6.0)
 hms           0.5.2   2019-10-30 [1] CRAN (R 3.6.1)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.6.0)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.6.0)
 pillar        1.4.2   2019-06-29 [1] CRAN (R 3.6.0)
 pkgbuild      1.0.6   2019-10-09 [1] CRAN (R 3.6.0)
 pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 3.6.0)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.6.0)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.6.0)
 processx      3.4.1   2019-07-18 [1] CRAN (R 3.6.0)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.6.0)
 purrr         0.3.3   2019-10-18 [1] CRAN (R 3.6.0)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.6.0)
 Rcpp          1.0.2   2019-07-25 [1] CRAN (R 3.6.0)
 remotes       2.1.0   2019-06-24 [1] CRAN (R 3.6.0)
 rlang         0.4.0   2019-06-25 [1] CRAN (R 3.6.0)
 RPostgres     1.1.1   2018-05-06 [1] CRAN (R 3.6.0)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.6.0)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.6.0)
 testthat      2.2.1   2019-07-25 [1] CRAN (R 3.6.0)
 tibble        2.1.3   2019-06-06 [1] CRAN (R 3.6.0)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.6.0)
 usethis       1.5.1   2019-07-04 [1] CRAN (R 3.6.0)
 vctrs         0.2.0   2019-07-05 [1] CRAN (R 3.6.0)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.6.0)
 zeallot       0.1.0   2018-01-28 [1] CRAN (R 3.6.0)

[1] /Library/Frameworks/R.framework/Versions/3.6/Resources/library

[ilarischeinin]

And now I of course see that:

The copy_to() command defaults to creating and populating temporary tables. So when used with in_schema(), the most likely result is that the command will be ignored, and a table called “[schema].[table]” is created.

As in_schema()'s return value has class "ident" (and "ident_q"), maybe copy_to() could print a warning message whenever a user tries to use that in combination with temporary = FALSE?