R MYSQL Write table deletes ccertain columns

I have the following table in MySQL with the following columns

 tbl= ID(Integer(11)) Keyword(Varchar(55)) Estimate(DECIMAL(10,4)) 
       Error(DECIMAL(10,4))  CreateDate(DateTime)

Of these variables ID and CreateDate are system generated namely I need not generate the same while writing to the table from R

I am writing the following dataframe to this table

 DF<- Keyword   Estimate  Error
        a        23         2
        b        21         6
        c        20         5

I am using the following code to write this using DBWriteTable command(R SQ lite Package)

  dbWriteTable(conn, value = DF, name='tbl', overwrite=T, append=F, 
  field.type=list(Keyword='varchar(100)', Estimated='DECIMAL(10,4)', 
  Error='Decimal(10,4)'),row.names=FALSE)

Now this causes two of the columns namely the ID and Date columns to vanish in the final mysql table.

I am not sure why this is happening. I have not placed a command to drop the column I request someone to take a look and guide me.

Solution

When you call dbWriteTable with overwrite=TRUE you are telling MySQL to drop the table and recreate a new one. In your case, it is creating a new table using the only three columns it knows about, namely what you passed to it. One option would be to add those other two columns to your data frame:

DF$ID <- NA
DF$CreateDate <- NA

Then, make your current call. I expect the NA values will map to NULL in MySQL, which would be consistent with the behavior of doing an INSERT and not specifying values for those columns. There is a potential problem here because MySQL may not know that ID is an auto increment column. So I don't like this solution very much.

The alternative, and in the spirit of what you have in mind, would be to make the following call:

dbWriteTable(conn, value = DF, name='tbl', overwrite=FALSE, append=TRUE, 
    field.type=list(Keyword='varchar(100)', Estimated='DECIMAL(10,4)', 
    Error='Decimal(10,4)'),row.names=FALSE)

Now you can just append to the already existing table. Still, you haven't specified non NULL values for the ID and CreateDate columns. So this option requires that CreateDate is nullable. If ID is auto increment, then I expect MySQL would be able to assign an id automatically with this option.