R: Why does dbWriteTable fail when table exists despite 'append = TRUE'
mysql, r
Solution
This solution was listed by the author in the question and has been moved here.
It appears the bug/feature only occurs when a full table path is used such as `myDB.temp_table` compared to simply `temp_table`
> dbWriteTable(conn=open_connection, name='myDB.temp_table', value=summary_data_final, overwrite=FALSE, append=TRUE, row.names=0)
Error in mysqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not run statement: Table 'temp_table' already exists)
[1] FALSE
Warning message:
In mysqlWriteTable(conn, name, value, ...) :
could not create table: aborting mysqlWriteTable
> dbWriteTable(conn=open_connection, name='temp_table', value=summary_data_final, overwrite=FALSE, append=TRUE, row.names=0)
[1] TRUE
I haven't found this documented anywhere and am surprised that I haven't come across it before.
Problem
I'm trying to append new data to a MySQL table that already exists using the `dbWriteTable` method. I've used it in the past without issue however it is now failing because the table already exists. This is despite using `overwrite=FALSE, append=TRUE,` The code: ``` full_sum_table <- 'mydb.summary' dbWriteTable(conn=open_connection, name=full_sum_table, value=summary_data_final, overwrite=FALSE, append=TRUE, row.names=0) Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: Table 'summary' already exists) [1] FALSE Warning message: In mysqlWriteTable(conn, name, value, ...) : could not create table: aborting mysqlWriteTable ``` The first call of this code works fine, but subsequent calls fail. Any ideas would be greatly appreciated. Thanks Environment: ``` R version 3.0.2 Packages: DBI (I was using RMySQL but it is not available for 3.0.2) MySQL v5.6.14 OS: Windows Server 7 ```