Syntax error with `copy_to` for MariaDB database backend

[colearendt]

I'm not 100% certain how to trace this any further. It looks like copy_to has trouble w/ SQL syntax for the MariaDB / MySQL connection. Using latest MariaDB docker image and the odbc package w/ the RStudio Pro drivers.

> tb <- copy_to(dbcon, testdata, "test2") %>% show_query()
Error: <SQL> 'ANALYZE `test2`'
  nanodbc/nanodbc.cpp:1587: 42000: [RStudio][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`test2`' at line 1 
> tb <- copy_to(dbcon, testdata, "test2") %>% show_query()
Error: <SQL> 'CREATE TEMPORARY TABLE `test2` (
  `fld_factor` TEXT,
  `fld_datetime` TEXT,
  `fld_date` TEXT,
  `fld_time` TEXT,
  `fld_binary` INTEGER,
  `fld_integer` INTEGER,
  `fld_double` DOUBLE,
  `fld_character` TEXT,
  `fld_logical` INTEGER
)
'
  nanodbc/nanodbc.cpp:1587: 42S01: [RStudio][MySQL] Table 'test2' already exists 

[hadley]

I can't reproduce this:

library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RMariaDB::MariaDB())
copy_to(con, mtcars)

^{Created on 2019-02-08 by the reprex package (v0.2.1.9000)}

But carefully reading your error messages suggests that it's actually a problem with db_analyze().

(show_query() isn't supposed to work with copy_to())

[colearendt]

Interesting. Sorry for the broken reprex. I presumed show_query() was valid since it reproduced the behavior. I am using odbc::odbc() for the driver (which is technically a MySQL driver, I guess. Not sure if that would cause problems). Would this potentially be a problem with the odbc package or the driver itself? I'm not sure where dependencies would exist.

cfg <- config::get(file = "~/rstudio/dbplyr/conn.yml")

library(odbc)
#> Warning: package 'odbc' was built under R version 3.4.4
con <- do.call(DBI::dbConnect, cfg$mysql)

DBI::dbGetInfo(con)
#> $dbname
#> [1] "mysql"
#> 
#> $dbms.name
#> [1] "MySQL"
#> 
#> $db.version
#> [1] "05.05.0005-10.3.12-MariaDB-1:10.3.12+maria~bionic"
#> 
#> $username
#> [1] "root"
#> 
#> $host
#> [1] ""
#> 
#> $port
#> [1] ""
#> 
#> $sourcename
#> [1] ""
#> 
#> $servername
#> [1] "localhost via TCP/IP"
#> 
#> $drivername
#> [1] "libmysqlodbc_sbu.dylib"
#> 
#> $odbc.version
#> [1] "03.52"
#> 
#> $driver.version
#> [1] "1.0.9.1007"
#> 
#> $odbcdriver.version
#> [1] "03.80"
#> 
#> $supports.transactions
#> [1] TRUE
#> 
#> attr(,"class")
#> [1] "MySQL"       "driver_info" "list"

dplyr::copy_to(con, mtcars)
#> Error: <SQL> 'ANALYZE `mtcars`'
#>   nanodbc/nanodbc.cpp:1587: 42000: [RStudio][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`mtcars`' at line 1

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

[colearendt]

FWIW, this works through the RMariaDB package for me as well.

con2 <- RMariaDB::dbConnect(RMariaDB::MariaDB(), ...)

DBI::dbGetInfo(con2)
#> $host
#> [1] "127.0.0.1"
#> 
#> $user
#> [1] "root"
#> 
#> $dbname
#> [1] "mysql"
#> 
#> $conType
#> [1] "127.0.0.1 via TCP/IP"
#> 
#> $serverVersion
#> [1] "5.5.5-10.3.12-MariaDB-1:10.3.12+maria~bionic"
#> 
#> $protocolVersion
#> [1] 10
#> 
#> $threadId
#> [1] 19
#> 
#> $client
#> [1] "5.7.20"

dplyr::copy_to(con2, mtcars)

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

[hadley]

Oh this is because we haven't defined the overrides for the class name that odbc produces when connecting to a MySQL database. You can figure that out from class(con), and then you just need to edit backend-mysql.R to copy all the S3 methods so that they also exist for that class.