[R-SIG-Mac] error with RMySQL

[Duncan Murdoch]

On 27/01/2014 10:03 AM, didier peeters wrote:
> Hello,
>
> I can't write data in a MySQL table with RMySQL anymore.  I used to do this until one year ago but since then it's broken, I always get the error : "Erreur dans tolower(avail) : chaîne de charactères multioctets incorrecte 43" (meaning in English : Error in tolower(avail) : incorrect multibyte string",  it can be something else than '43').  So I tried to narrow down to a simple case and I made this script :
>
> #!/usr/bin/Rscript
> library(DBI)
> library(RMySQL)
>
> conn <- dbConnect("MySQL", user="userr", password="passworrd", dbname="dbtest")
>
> res <- dbSendQuery(conn, statement = paste("SELECT * FROM testable"))
> input <- fetch(res, n = -1)
>
> dbWriteTable(conn, "testable2", input, row.names = T, overwrite = FALSE, append = T)
>
> dbDisconnect(conn)
>
> The table content being :
>     id testval
> 1  1   76
> 2  2   47417
>
> The user owns the DB. The fetch works fine but not the dbWriteTable.  The error is probably related to some character coding but I can't figure out what.
>
> I'm using R version 3.0.2 (2013-09-25), RMySQL 0.9-3 and MySQL 5.6.14, Mac OS X 10.9.1 .
> I have the same issue on Rstudio server hosted on a Debian machine.
>
> Thanks for helping !

This probably isn't a MacOS question, other than the fact that MacOS 
defaults to UTF-8 and Windows doesn't.
Since you are asking to append to an existing table, it probably has 
values stored in a Latin1 (or other non-UTF-8) encoding, and that's 
messing things up.

But without a reproducible example, it's hard to guess...

Duncan Murdoch