R: Why does dbWriteTable fail when table exists despite 'append = TRUE'

Question

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

Seems like a bug that would be better reported to the package maintainer. — hadley, Feb 05 '14 at 14:08
@hadley - I was hoping that it wasn't but given that it was previously working and now isn't perhaps an update has unsettled things. I just did a complete re-install of R and the RMySQL package (in desperation) but I still have the problem. — getting-there, Feb 05 '14 at 23:01
Not that this is all that helpful, but I can confirm that the bug also exists for the PostgreSQL connector as well. I'll try to look into it. My way around like you, @getting-there, writing to the public schema and then running two additional queries from R where I moved the table into the desired schema and deleted it in public. Wasteful, but got the job done. — Michael Discenza, Jul 30 '14 at 22:09
Do you have an auto-incrementing primary key in the existing table? If yes, have you looked at: http://stackoverflow.com/questions/26449666/how-do-i-write-data-from-r-to-postgresql-tables-with-an-autoincrementing-primary/26451425?noredirect=1#comment64226381_26451425 and http://stackoverflow.com/questions/38427757/making-change-to-function-in-r-package-and-installing-on-ubuntu — vagabond, Nov 03 '16 at 15:36
if you have an answer, please add it and then mark as accepted — cwallenpoole, Jan 13 '17 at 16:28
@cwallenpoole I have edited the question and moved the answer to an answer. — Shawn, Feb 14 '17 at 17:00
@getting-there I would mark the answer with a checkbox below. In the future you can answer your own question by posting it as an answer. This makes it easier for those searching with the same problem to find a solution. — Shawn, Feb 14 '17 at 17:01

score 5 · Accepted Answer · answered Feb 14 '17 at 16:59

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.

Also, one more case is that the table name should be in small case - it took me some time to find it out . Obviously by googling only. Src: https://github.com/r-dbi/RMySQL/issues/82 — surpavan, May 29 '19 at 19:08

score 1 · Answer 2 · answered Nov 29 '19 at 09:43

Just use this function to insert a row in your table

 insert_row <- function(jconn,table_name,col_data,data){
  dbSendUpdate(jconn,paste0("INSERT INTO ",table_name," (",paste0(col_data,collapse=','),") VALUES ('",paste0(data,collapse="','"),"');"))
  print('data inserted')}

R: Why does dbWriteTable fail when table exists despite 'append = TRUE'

2 Answers2