[R-SIG-Mac] error with RMySQL

[didier peeters]

Sorry, you're right.  But UTF-8 is the default on my mac as well as on my database.
I was hoping that somebody already had that problem. 


Le 27 janv. 2014 à 16:23, Duncan Murdoch <murdoch.duncan at gmail.com> a écrit :

> 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