RMYSQL Writetable error

I have the following R dataframe

 Sl NO  Name    Marks
 1        A      15
 2        B      20
 3        C      25

I have a mysql table as follows. (Score.table)

   No   CandidateName   Score
   1       AA           1
   2       BB           2
   3       CC           3

I have written my dataframe to Score.table using this code

    username='username'
    password='userpass'
    dbname='cdb'
    hostname='***.***.***.***'

    cdbconnection = dbConnect(MySQL(), user=username, password=userpass, 
    dbname=dbname, host=hostname)

Next we write the dataframe to the table as follows

     score.table<-'score.table'

    dbWriteTable(cdbconn, score.table, dataframe, append =F, overwrite=T).

The code runs and I get TRUE as the output. However, when I check the SQL table, the new values haven't overwritten the existing values. I request someone to help me. The code works. I have reinstalled the RMySQL package again and rerun and the results are the same.

Solution

That updates are not happening indicates that the RMySQL package cannot successfully map any of the rows from your data frame to already existing records in the table. So this would imply that your call to dbWriteTable has a problem. Two potential problems I see are that you did not assign values for field.types or row.names. Consider making the following call:

score.table <- 'score.table'
dbWriteTable(cdbconn, score.table, dataframe,
             field.types=list(`Sl NO`="int", Name="varchar(55)", Marks="int"),
             row.names=FALSE)

If you omit field.types, then the package will try to infer what the types are. I am not expert with this package, so I don't know how robust this inference is, but most likely you would want to specify explicit types for complex update queries.

The bigger problem might actually be not specifying a value for row.names. It can default to TRUE, in which case the package will actually send an extra column during the update. This can cause problems, for example if your target table has three columns, and the data frame also has three columns, then you are trying to update with four columns.