One of the table on the database uses datatype "BIT". When I use dbGetQuery(conn, "select * from table_name"), the fields of datatype "BIT" always return zero even the value is one. Can you check what's going wrong? Thank you.

It seems like this commit by @hadley removed dbObjectId which causes TSMySQL to break:

Error in getMethod("coerce", c("dbObjectId", "integer")) : 
  no method found for function 'coerce' and signature dbObjectId, integer

Jeff suggests that TSMySQL instead uses:

setAs("TSMySQLConnection", "integer",
 def = function(from) as(slot(from,"Id"), "integer")
)

So that it will work both for the current as well as older versions of RMySQL.

Notice the stray integers in the error message in the session below:

> library(RMySQL)
Loading required package: DBI
> con <- dbConnect(MySQL(), user="...", dbname="...", password="...")
> dbGetQuery(con, 'SELECT count(*) FROM posts WHERE Body ILIKE "%i %i %i"')
Error in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE "1092632251 1924521820 2147483444"' at line 1)
> sessionInfo()
R version 3.1.2 (2014-10-31)
Platform: x86_64-pc-linux-gnu (64-bit)
...
other attached packages:
[1] RMySQL_0.9-3 DBI_0.3.1   

See also r-dbi/DBI#23 (comment).

After breaking my head over why I got '\xe9' instead of 'é' in a certain query after correctly setting the connection character encoding, I think I found the issue. Consider my code:

input.table.query2 <- "select s.record_id, ...
      from table s limit 2000,2000"

mysql <- dbDriver("MySQL")
con <- dbConnect(mysql, user="user", password="pass", dbname="db")
dbGetQuery(con, "SET NAMES utf8")
charset <- dbGetQuery(con, "show variables like 'character_set%'")

resultset <- dbSendQuery(con, input.table.query2)
charset2 <- dbGetQuery(con, "show variables like 'character_set%'")

I put a query in a variable, connect to the database, set the connection character set and dbSendQuery my query. After these statements, the resultset is fetched and values from the resultset are used in string comparisons etc.
In between I request the character set variables for debugging. charset becomes

1   character_set_client    utf8
2   character_set_connection    utf8
3   character_set_database  utf8
4   character_set_filesystem    binary
5   character_set_results   utf8
6   character_set_server    utf8mb4
7   character_set_system    utf8
8   character_sets_dir  /usr/local/mysql-5.5.27-osx10.6-x86_64/share/charsets/

But charset2 is now

1   character_set_client    latin1
2   character_set_connection    latin1
3   character_set_database  utf8
4   character_set_filesystem    binary
5   character_set_results   latin1
6   character_set_server    utf8mb4
7   character_set_system    utf8
8   character_sets_dir  /usr/local/mysql-5.5.27-osx10.6-x86_64/share/charsets/

(Mac OS X 10.8, MySQL 5.5.27, RMySQL 0.9-3, R 2.15.1)

Same setup as with issue #55 except my ~/.my.cnf now is a bit more complicated:

[mysql]
database=algo
host=so1db.cjvo6rncstds.eu-west-1.rds.amazonaws.com
port=3306
user=so1engine
password=fakepassword123456789

and now when I reference the mysql group in dbConnect:

> X <- dbConnect(RMySQL::MySQL(), groups="mysql")
Error: Failed to connect: Access denied for user 'so1engine'@'c-24-12-233-98.hsd1.il.comcast.net' (using password: NO)

Enter a frame number, or 0 to exit

1: dbConnect(RMySQL::MySQL(), groups = "mysql")
2: .valueClassTest(standardGeneric("dbConnect"), "DBIConnection", "dbConnect")
3: is(object, Cl)
4: is(object, Cl)
5: .local(drv, ...)
6: connection_create(host, username, password, dbname, port, unix.socket, client.flag, groups, default.file)

I know the ~/.my.cnf is proper, since I can call mysql and perform queries without issues:

$ mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 79090
Server version: 5.6.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| algo               |
| campaigns          |
| loyalty            |
| masters            |
| reporting          |
| segment            |
+--------------------+
7 rows in set (0.13 sec)

Furthermore, when I try to fully qualify my call to dbConnect, I get an even weirder error, "Error: not compatible with requested type":

> X <- dbConnect(drv =RMySQL::MySQL(),
+        dbname='algo',
+        host='so1db.cjvo6rncstds.eu-west-1.rds.amazonaws.com',
+        port='3306',
+        user='so1engine',
+        password='fakepassword123456789')
Error: not compatible with requested type

Enter a frame number, or 0 to exit

1: dbConnect(drv = RMySQL::MySQL(), dbname = "algo", host = "so1db.cjvo6rncstds.eu-west-1.rds.amazonaws.com", port = "3306", user = "so1engine", password = "
2: .valueClassTest(standardGeneric("dbConnect"), "DBIConnection", "dbConnect")
3: is(object, Cl)
4: is(object, Cl)
5: .local(drv, ...)
6: connection_create(host, username, password, dbname, port, unix.socket, client.flag, groups, default.file)

This NOTE only seems to appear on Debian (so far).

safe.write: possible error in write.table(escape(value[from:to, , drop
= FALSE]), file = conb, append = TRUE, quote = FALSE, sep = "\t", na
= "\\N", row.names = FALSE, col.names = FALSE, eol = "\n", ...): ...
used in a situation where it does not exist
dbFetch,MySQLResult-missing: possible error in mysqlFetch(res, n = 0,
...): ... used in a situation where it does not exist
fetch,MySQLResult-missing: possible error in mysqlFetch(res, n = 0,
...): ... used in a situation where it does not exist

@hadley I think this was introduced recently?

In file included from ./MyBinding.h:6:
./MyTypes.h:19:10: warning: enumeration values 'MYSQL_TYPE_TIMESTAMP2',
      'MYSQL_TYPE_DATETIME2', and 'MYSQL_TYPE_TIME2' not handled in switch
      [-Wswitch]
  switch(type) {
         ^
./MyTypes.h:61:1: warning: control may reach end of non-void function
      [-Wreturn-type]
}
^
2 warnings generated.

I'm failing to get correct (Japanese) characters when writing a dataframe to MySQL using dbWriteTable. For example if I read the following lines from a CSV file into R, the characters display correctly, but when written to a MySQL table they appear as series of ???. The text lines..

"00043","201411","こんにゃく","おかずその他","普及",2,2
"00043","201411","こんにゃく","おかずその他","通年",1,1
"00043","201411","こんにゃく","その他こんにゃく","-",1,1
"00043","201411","こんにゃく","つき白滝","中質",4,3
"00043","201411","こんにゃく","つき白滝","普及",4,1

The R code..

library(RMySQL)

DF <- read.csv('fpo.csv', stringsAsFactors=FALSE)
dbase <- 'wnp_2_3_new'
con <- dbConnect(MySQL(), user='root', password=pw, dbname=dbase)
rs <- dbGetQuery(con, 'set character set utf8')
dbWriteTable(con, 'fpo_out', overwrite=TRUE, DF)
dbDisconnect(con)

After upgrading RMySQL 0.10 from 0.9-3, I started getting many warnings saying:

1: In .local(conn, statement, ...) :
  Unsigned INTEGER in col 0 imported as numeric

While it can be useful to know that the typecasting from MySQL to R is happening, it's also annoying to continually get these warnings all the time. My database uses unsigned integers as primary keys in every table, so I get this with every query. I can think of some possible ways to address this:

1. add an option to suppress typecasting warnings
2. allow the user to specify how to cast MySQL types to R types

To fix this, I will probably change the MySQL types in the database to use just integers, but other people might have this problem too. Thanks.

If it's useful, here's all of sessionInfo():

R version 3.1.2 (2014-10-31)
Platform: x86_64-unknown-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RMySQL_0.10 DBI_0.3.1  

The NEWS file does not show up on CRAN. It should be here according to convention:
http://cran.r-project.org/web/packages/RMySQL/NEWS
I wanted to quickly check what was in v0.10.1, but had to go to GitHub for that.

After loading RMySQL I get errors excuting dbConnect for postgres:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="mydb", user="myuser", password="mypass", host="myhost")
dbListTables(con)

library(RMySQL)
con <- dbConnect(drv, dbname="mydb", user="myuser", password="mypass", host="myhost")

## Error in as.integer(from) : 
##  cannot coerce type 'S4' to vector of type 'integer' 

Here's my (abbreviated) output (I started from a fresh R session):

> devtools::install_github("RcppCore/Rcpp")
[snip]
* DONE (Rcpp)
> devtools::install_github("rstats-db/DBI")
[snip]
* DONE (DBI)
> devtools::install_github("rstats-db/RMySQL")
[snip]
* DONE (RMySQL)
> library(DBI)
> X <- dbConnect(RMySQL::MySQL(), groups="rs-dbi")
Error: Failed to connect: Access denied for user 'root'@'localhost' (using password: NO)

Enter a frame number, or 0 to exit

1: dbConnect(RMySQL::MySQL(), groups = "rs-dbi")
2: .valueClassTest(standardGeneric("dbConnect"), "DBIConnection", "dbConnect")
3: is(object, Cl)
4: is(object, Cl)
5: .local(drv, ...)
6: connection_create(host, username, password, dbname, port, unix.socket, client.flag, groups, default.file)

My ~/.my.cnf file looks like

[rs-dbi]
database=test
user=root
password=alpine

And when I try and access mysql via CLI:

$ mysql --user=root --password=alpine test
ERROR 1049 (42000): Unknown database 'test'

MariaDB 10.0

> con
<MySQLConnection:0,2>
> dbWriteTable(con, 'mtcars', mtcars)
Error in .local(conn, statement, ...) : 
sdbi1ab87df67117' not found (Errcode: 22)

I just encountered a number of unexpected and sometimes dangerous side-effects caused by the fact that dbWriteTable will silently clone a connection if there are any results open on the connection. This is due to the fact MySQL will associate quite a bit of information with its session.

• Session settings will be ignored. Particularly, setting sql_mode = 'TRADITIONAL' to avoid silent data truncations will have no effect on the cloned connection.
• Temporary tables are out of sync. I must confess I still haven't fully understood all that seems to be happening here, but the fact is that simply appending to an existing temporary table isn't as easy as it should be. In some cases, the data will not show up in the temporary, and in some cases a new non-temporary table will created instead.

As a consequence, I suggest adding a flag to the connection object, indicating whether or not it may be cloned automatically. If set to true, current behavior could be maintained, but if set to false, then attempts to clone the connection would result in errors. Perhaps there is some way to avoid cloning in more situations, e.g. if there exists a result but it has already been completed.

RMySQL seems to use text for Date (and POSIXct) columns. It should use MySQL date type and datetime or timestamp type and convert correctly in both directions.

library(RMySQL)

t1 <- data.frame(d = as.Date("2001-01-30"))
con <- dbConnect("MySQL", "test")
dbWriteTable(con, "t1", t1, row.names = FALSE)
dbGetQuery(con, "describe t1")

which produces:

  Field Type Null Key Default Extra
1     d text  YES        <NA>

but d should be of MySQL date type, not text.

packageVersion("RMySQL")
[1] ‘0.9.3’
packageVersion("DBI")
[1] ‘0.3.1’
R.version.string
[1] "R version 3.1.1 Patched (2014-08-21 r66456)"

Replicated on Fedora 20 and Mac OS X 10.9.5 (Mavericks):

RMySQL::MySQL()
RPostgreSQL::PostgreSQL()
Error in as.integer(from) : 
  cannot coerce type 'S4' to vector of type 'integer'

This issue appears to be new to RMySQL 0.10, it is not present in RMySQL 0.9.3.

Hi @hadley,

The following code fails with the aforementioned error:

test <- data.frame(n = c(1, NA))
dbWriteTable(conn = conn, name = "test", value = test, overwrite = T)

Thanks and best regards.

-- Alain

The package compiles and installs on 64 bit Windows. However, I have been fruitlessly trying to install it on 32 bit for the last 5 days following almost every post about it on stackoverflow. A major issue is that it gives a different error every time. The last time, it spewed
Warning: running command 'sh ./configure.win' had status 127
ERROR: configuration failed for package 'RMySQL'
Previous to that errors reported missing cygwin DLL with the same R and Rtools version.
I would like to know if it is a hopeless situation for 32 bit OS and stop wasting my time with it.

As I wanted to try out #6, I followed the README and used devtools to install the latest version e9ced2d from GitHub. Or at least tried to…

x86_64-pc-linux-gnu-g++ -I/usr/lib64/R/include -DNDEBUG -I/usr/include/mysql -DNDEBUG  -I"/home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include"   -fpic  -march=amdfam10 -O2 -ggdb -pipe  -c MyConnection.cpp -o MyConnection.o
In file included from MyConnection.cpp:1:0:
MyConnection.h: In constructor ‘MyConnection::MyConnection(std::string, std::string, std::string, std::string, unsigned int, std::string, long unsigned int, std::string, std::string)’:
MyConnection.h:43:62: error: too many arguments to function ‘void Rcpp::stop(const string&)’
       Rcpp::stop("Failed to connect: %s", mysql_error(pConn_));
                                                              ^
In file included from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/RcppCommon.h:131:0,
                 from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp.h:27,
                 from MyConnection.h:4,
                 from MyConnection.cpp:1:
/home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp/exceptions.h:195:17: note: declared here
     inline void stop(const std::string& message) { 
                 ^
In file included from MyBinding.h:6:0,
                 from MyResult.h:7,
                 from MyConnection.cpp:2:
MyTypes.h: In function ‘MyFieldType variableType(Rcpp::RObject)’:
MyTypes.h:120:70: error: too many arguments to function ‘void Rcpp::stop(const string&)’
   Rcpp::stop("Unsupported column type %s", Rf_type2char(TYPEOF(type)));
                                                                      ^
In file included from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/RcppCommon.h:131:0,
                 from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp.h:27,
                 from MyConnection.h:4,
                 from MyConnection.cpp:1:
/home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp/exceptions.h:195:17: note: declared here
     inline void stop(const std::string& message) { 
                 ^
In file included from MyResult.h:7:0,
                 from MyConnection.cpp:2:
MyBinding.h: In member function ‘void MyBinding::initBinding(Rcpp::List)’:
MyBinding.h:32:78: error: too many arguments to function ‘void Rcpp::stop(const string&)’
       Rcpp::stop("Number of params don't match (%i vs %i)", p_, params.size());
                                                                              ^
In file included from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/RcppCommon.h:131:0,
                 from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp.h:27,
                 from MyConnection.h:4,
                 from MyConnection.cpp:1:
/home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp/exceptions.h:195:17: note: declared here
     inline void stop(const std::string& message) { 
                 ^
In file included from MyConnection.cpp:2:0:
MyResult.h: In member function ‘void MyResult::throwError()’:
MyResult.h:214:5: error: too many arguments to function ‘void Rcpp::stop(const string&)’
     );
     ^
In file included from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/RcppCommon.h:131:0,
                 from /home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp.h:27,
                 from MyConnection.h:4,
                 from MyConnection.cpp:1:
/home/mvg/R/x86_64-pc-linux-gnu-library/3.1/Rcpp/include/Rcpp/exceptions.h:195:17: note: declared here
     inline void stop(const std::string& message) { 
                 ^
MyConnection.cpp: In member function ‘void MyConnection::setCurrentResult(MyResult*)’:
MyConnection.cpp:10:7: error: ‘warning’ is not a member of ‘Rcpp’
       Rcpp::warning("Cancelling previous query");
       ^
/usr/lib64/R/etc/Makeconf:140: recipe for target 'MyConnection.o' failed
make: *** [MyConnection.o] Error 1
ERROR: compilation failed for package ‘RMySQL’

This is for R 3.1.2 on Gentoo Linux.

I'm not sure if this is standard behaviour, but when using dbWriteTable or dbRemoveTable, I get the error:

Error in is(object, Cl) : corrupt resultSet, missing fieldDescription

To give you something reproducible

• I started a local mysql server (mysql-server version 5.5.41-0ubuntu0.14.04.1), -u root, -p alpine and added testTable to the mysql db:

$ mysql --user=root --password=alpine mysql
> CREATE TABLE testTable (id text);

• inside R with RMySQL version 0.10.2 and DBI version 0.3.1.9008:

X <- DBI::dbConnect(drv=RMySQL::MySQL(), user = "root", password = "alpine", dbname="mysql")
DBI::dbExistsTable(conn = X, name = "testTable") # TRUE
DBI::dbWriteTable(conn = X, name = "testTable", value = data.frame(id = "1"), row.names = FALSE, append = TRUE) # throws error above
DBI::dbReadTable(conn = X, name = "testTable") # drops data.frame in "value", above
DBI::dbRemoveTable(conn = X, name = "testTable") # throws error above
DBI::dbExistsTable(conn = X, name = "testTable") # FALSE

From the sqlalchemy docs:

The encoding used for Unicode has traditionally been 'utf8'. However, for MySQL versions 5.5.3 on forward, a new MySQL-specific encoding 'utf8mb4' has been introduced. The rationale for this new encoding is due to the fact that MySQL’s utf-8 encoding only supports codepoints up to three bytes instead of four. Therefore, when communicating with a MySQL database that includes codepoints more than three bytes in size, this new charset is preferred, if supported by both the database as well as the client DBAPI

Queries that run without any issues in version "0.9-3' now do not work in "0.10.1".

In one of my tests, I removed columns one by one and things worked properly when I got down to 203 columns. I thought perhaps there was a problem with the last one I removed, but that is not the case. If I remove another one instead, the same issue occurs. This is the message:

Error in validObject(.Object) : 
  invalid class “MySQLResult” object: invalid object for slot "Id" in class "MySQLResult": got class "list", should be or extend class "integer"
Calls: dbSendQuery ... .local -> new -> initialize -> initialize -> validObject
In addition: There were 50 or more warnings (use warnings() to see the first 50)
Execution halted

In another situation (involving a JOIN of 2 tables), if I list out all the columns (270 in total), the query works, but if I write it as SELECT table1., table2. FROM ... , then it fails. The message is:

Error in is(object, Cl) : 
  INTEGER() can only be applied to a 'integer', not a 'char'
Calls: fetch -> .valueClassTest -> is -> is -> .Call
Execution halted

To repeat, the queries are all valid and work perfectly in MySQL (issuing direct commands) and in the older version of this library (RMySQL 0.9-3) which I have installed on another machine.

I have placed a test schema for you here:
https://heartsys.org/share/foo.tar.gz

Simply run "SELECT * FROM foo.tbl" to reproduce issue #1.

P.S. This is the version of R I am running:
R version 3.1.2 (2014-10-31) -- "Pumpkin Helmet"
Copyright (C) 2014 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

I'm having issues installing RMySQL on OS X. See this gist and this stack Overflow question for details:

• http://stackoverflow.com/questions/16487855/error-installing-rmysql-on-os-x
• https://gist.github.com/yoni/5555916

The error boils down to this:

Error : .onLoad failed in loadNamespace() for 'RMySQL', details:
  call: dyn.load(file, DLLpath = DLLpath, ...)
  error: unable to load shared object '/Library/Frameworks/R.framework/Versions/2.15/Resources/library/RMySQL/libs/i386/RMySQL.so':
  dlopen(/Library/Frameworks/R.framework/Versions/2.15/Resources/library/RMySQL/libs/i386/RMySQL.so, 6): Symbol not found: _mysql_affected_rows
  Referenced from: /Library/Frameworks/R.framework/Versions/2.15/Resources/library/RMySQL/libs/i386/RMySQL.so
  Expected in: flat namespace
 in /Library/Frameworks/R.framework/Versions/2.15/Resources/library/RMySQL/libs/i386/RMySQL.so
Error: loading failed
Execution halted
ERROR: loading failed

Do you have any idea what might be going wrong?

Thanks!

Why not just use mysql_config, if it is available?

Hi,

I've encountered a problem while compiling RMySQL on a rpm based system (Fedora 21, x64).

For some reason, I had to install the i686 version of MySQL/Mariadb some time ago, in addition to the 64bit version. Later, I remove the i686 packages.

When compiling RMySQL (64), the process ended with an error as it could't find libmysqlclient. It comes out that "configure" relies on the existence of /usr/lib/mysql or /usr/lib64/mysql to choose between the 32 ou 64 bit version to link to. Unfortunately, removing a package doesn't (always?) remove the relevant directory structure. In my case, /usr/lib/mysql was still present, even if empty.

To solve this issue, I deleted the (empty ) /usr/lib/mysql, and RMySQL compiled successfully.

Maybe the "configure" file should be modified in order not to rely on the existence of a given directory ? An (unperfect) fix is to test "lib64" before "lib" in "configure"

Hi,

I am using the development version of RMySQL (maybe I'm not supposed to).

I don't know if this is a dplyr issue or an RMySQL issue, but I get an error when I try to connect a dplyr tbl to an RMySQL table.

The frustrating thing is that I can't give a reproducible example because I can't supply the database.

I am running using a local implementation of MySQL.

> sql_src <- src_mysql("test")

> sql_src
src:  mysql 5.6.22 [root@localhost:/test]
tbls: grid, image, image_sub, intensity

> sql_image <- tbl(sql_src, from = "image")
Error: select is not a character vector

Looking through the reverse dependency log, I see something similar.

https://github.com/rstats-db/RMySQL/blob/79039c7661b3920a6c8ffa62f54581055b7fad2b/revdep/summary.md

Section dplyr:

Loading required package: RMySQL
Loading required package: DBI
Error: select is not a character vector

I am happy to try to be more specific, but I am at a loss for now.

Thanks.

It seems the transition from 0.9.3 to 0.10.0 has dropped dbGetQuery from the package NAMESPACE, with no reflection in the documentation. Can anyone explain the change in the API?

Refactoring of the C code has introduced a bug on windows (gcc 4.6.3). Here is the full log from the win builder.

gcc  -I"D:/RCompile/recent/R/include"         -I../windows/mariadb-5.5/include    -I"d:/RCompile/r-compiling/local/local320/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c RMySQL-init.c -o RMySQL-init.o
gcc  -I"D:/RCompile/recent/R/include"         -I../windows/mariadb-5.5/include    -I"d:/RCompile/r-compiling/local/local320/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c connection.c -o connection.o
connection.c: In function 'is_validHandle':
connection.c:488:8: warning: variable 'mgr_id' set but not used [-Wunused-but-set-variable]
gcc  -I"D:/RCompile/recent/R/include"         -I../windows/mariadb-5.5/include    -I"d:/RCompile/r-compiling/local/local320/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c db-apply.c -o db-apply.o
db-apply.c: In function 'check_groupEvents':
db-apply.c:437:5: error: expected ')' before 'ERROR'
db-apply.c:439:5: error: expected ';' before '}' token
db-apply.c:474:3: error: expected declaration or statement at end of input
db-apply.c:474:3: warning: control reaches end of non-void function [-Wreturn-type]
make: *** [db-apply.o] Error 1

Currently the character set is selected using "SET character set utf8" and mysql_options(pConn_, MYSQL_SET_CHARSET_NAME, "UTF8");

This should be set in one place

I have a codebase that was successfully making SSL connections (the only kind of connection allowed by the db server) using RMySQL 0.9-3. After the package was updated to 0.10, the exact same code (and configuration file) was producing : Failed to connect to database: Error: Access denied for user 'myusername'@'my.ip.address' (using password: YES)

It seems as though the new version (0.10) may be handling SSL connections differently, possibly not valid SSL connections?

dbWriteTable(..., append=TRUE) unfortunately does not work with temporary tables. I guess this is because it checks if the table exists, and this check ignores temporary tables.

What happens is, that a proper table is created, even if there is already a temporary table with the same name (assuming the user has rights to create real tables).

This is really unfortunate, because temporary tables could be a workaround of the lack of ability to bind to a data frame, or create a prepared query.....

Here is the code to reproduce it, sorry, it is not self-contained, you would need a DB connection, obviously:

dbSendQuery(con, "CREATE TEMPORARY TABLE tmp (foobar TEXT);")
dbWriteTable(con, "tmp", data.frame(foobar=letters[1:5]), 
                     row.names=FALSE, append=TRUE)

The solution is probably to check for temporary tables, too, via

show temporary tables;

Since dbWriteTable passes its data through a temporary file, the character set used for writing depends on the current locale of the R session, whereas the one used for reading depends on the MySQL configuration. These two do not neccessarily agree.

Quoting from the docs:

SET NAMES and the setting of character_set_client do not affect interpretation of input.

It would be nice if R could write that data with a specific, well-defined and sufficiently universal character set, probably UTF-8, and also include a matching CHARACTER SET clause to the LOAD DATA LOCAL INFILE command passed to the server.

As an alternative, one could of course consider loading data without a temporary file, using a prepared insert statement instead. Doing so would likely solve this issue here as well as issue #1.

The error can be reproduced with the following script:

dbcon <- dbConnect(MySQL(), user="", password="", dbname="", host="localhost") 
df <- data.frame(A=c(1,2,3), B=c("not offending", "offending\nline", "not offending"), C=c("1", "2", "3"))
dbWriteTable(dbcon, "tmp", df)
df_from_db <- dbReadTable(dbcon, "tmp")
dbDisconnect(dbcon)

I was still working today at noon, now it throw out this error:
Error in as.integer(from) :
cannot coerce type 'S4' to vector of type 'integer'

I'm trying to install the latest version from github and I get the following:

> devtools::install_github("rstats-db/RMySQL")
Downloading github repo rstats-db/RMySQL@master
Installing RMySQL
"C:/PROGRA~1/R/R-31~1.2/bin/x64/R" --vanilla CMD INSTALL  \
  "C:/Users/jsetlik/AppData/Local/Temp/Rtmp2fDJf5/devtools117823cf7f3d/rstats-db-RMySQL-e9ced2d"  \
  --library="C:/Users/jsetlik/Documents/R/win-library/3.1" --install-tests 

* installing *source* package 'RMySQL' ...
Warning in .write_description(db, file.path(outDir, "DESCRIPTION")) :
  Unknown encoding with non-ASCII data: converting to ASCII
** libs

*** arch - i386
rm -f RMySQL-init.o
"C:/PROGRA~1/R/R-31~1.2/bin/i386/Rscript.exe" "../tools/winlibs.R"
gcc -m32 -I"C:/PROGRA~1/R/R-31~1.2/include" -DNDEBUG -I../windows/libmariadbclient-2.1.0/include   -I"C:/Users/jsetlik/Documents/R/win-library/3.1/Rcpp/include" -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O3 -Wall  -std=gnu99 -mtune=core2 -c RMySQL-init.c -o RMySQL-init.o
g++ -m32 -shared -s -static-libgcc -o RMySQL.dll tmp.def RMySQL-init.o -L../windows/libmariadbclient-2.1.0/lib/i386 -lmariadbclient -lssl -lcrypto -lgdi32 -lz -lws2_32 -Ld:/RCompile/CRANpkg/extralibs64/local/lib/i386 -Ld:/RCompile/CRANpkg/extralibs64/local/lib -LC:/PROGRA~1/R/R-31~1.2/bin/i386 -lR
installing to C:/Users/jsetlik/Documents/R/win-library/3.1/RMySQL/libs/i386

*** arch - x64
rm -f RMySQL-init.o
"C:/PROGRA~1/R/R-31~1.2/bin/x64/Rscript.exe" "../tools/winlibs.R"
gcc -m64 -I"C:/PROGRA~1/R/R-31~1.2/include" -DNDEBUG -I../windows/libmariadbclient-2.1.0/include   -I"C:/Users/jsetlik/Documents/R/win-library/3.1/Rcpp/include" -I"d:/RCompile/CRANpkg/extralibs64/local/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c RMySQL-init.c -o RMySQL-init.o
g++ -m64 -shared -s -static-libgcc -o RMySQL.dll tmp.def RMySQL-init.o -L../windows/libmariadbclient-2.1.0/lib/x64 -lmariadbclient -lssl -lcrypto -lgdi32 -lz -lws2_32 -Ld:/RCompile/CRANpkg/extralibs64/local/lib/x64 -Ld:/RCompile/CRANpkg/extralibs64/local/lib -LC:/PROGRA~1/R/R-31~1.2/bin/x64 -lR
installing to C:/Users/jsetlik/Documents/R/win-library/3.1/RMySQL/libs/x64
** R
** tests
** preparing package for lazy loading
Error in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]) : 
  there is no package called 'SQL'
ERROR: lazy loading failed for package 'RMySQL'
* removing 'C:/Users/jsetlik/Documents/R/win-library/3.1/RMySQL'

I tried to install RMySQL. Apparently, installation works, but dbConnect() still doesn't exists, so I guess that the package has not been installed.

I'm pasting here the output of install.packages and the output of version.

install.packages("RMySQL")
Installing package(s) into ‘/usr/local/lib/R/site-library’
(as ‘lib’ is unspecified)
provo con l'URL 'http://mirrors.nic.cz/R/src/contrib/RMySQL_0.9-3.tar.gz'
Content type 'application/octet-stream' length 165363 bytes (161 Kb)

URL aperto

downloaded 161 Kb

• installing source package ‘RMySQL’ ...
  ** package ‘RMySQL’ successfully unpacked and MD5 sums checked
  checking for gcc... gcc
  checking for C compiler default output file name... a.out
  checking whether the C compiler works... yes
  checking whether we are cross compiling... no
  checking for suffix of executables...
  checking for suffix of object files... o
  checking whether we are using the GNU C compiler... yes
  checking whether gcc accepts -g... yes
  checking for gcc option to accept ANSI C... none needed
  checking how to run the C preprocessor... gcc -E
  checking for compress in -lz... yes
  checking for getopt_long in -lc... yes
  checking for mysql_init in -lmysqlclient... yes
  checking for egrep... grep -E
  checking for ANSI C header files... yes
  checking for sys/types.h... yes
  checking for sys/stat.h... yes
  checking for stdlib.h... yes
  checking for string.h... yes
  checking for memory.h... yes
  checking for strings.h... yes
  checking for inttypes.h... yes
  checking for stdint.h... yes
  checking for unistd.h... yes
  checking mysql.h usability... no
  checking mysql.h presence... no
  checking for mysql.h... no
  checking /usr/local/include/mysql/mysql.h usability... no
  checking /usr/local/include/mysql/mysql.h presence... no
  checking for /usr/local/include/mysql/mysql.h... no
  checking /usr/include/mysql/mysql.h usability... yes
  checking /usr/include/mysql/mysql.h presence... yes
  checking for /usr/include/mysql/mysql.h... yes
  configure: creating ./config.status
  config.status: creating src/Makevars
  ** libs
  gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/usr/include/mysql -fpic -O2 -pipe -g -c RS-DBI.c -o RS-DBI.o
  gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/usr/include/mysql -fpic -O2 -pipe -g -c RS-MySQL.c -o RS-MySQL.o
  gcc -std=gnu99 -shared -o RMySQL.so RS-DBI.o RS-MySQL.o -lmysqlclient -lz -L/usr/lib/R/lib -lR
  installing to /usr/local/lib/R/site-library/RMySQL/libs
  ** R
  ** inst
  ** preparing package for lazy loading
  Creating a generic function for ‘format’ from package ‘base’ in package ‘RMySQL’
  Creating a generic function for ‘print’ from package ‘base’ in package ‘RMySQL’
  ** help
  *** installing help indices
  ** building package indices
  ** installing vignettes
  ** testing if installed package can be loaded
• DONE (RMySQL)

The downloaded source packages are in
‘/tmp/Rtmp6tpzBI/downloaded_packages’

version
_
platform x86_64-pc-linux-gnu
arch x86_64
os linux-gnu
system x86_64, linux-gnu
status
major 2
minor 15.1
year 2012
month 06
day 22
svn rev 59600
language R
version.string R version 2.15.1 (2012-06-22)
nickname Roasted Marshmallows

For an extensive description of the problem, see:
http://stackoverflow.com/questions/27823462/rmysql-dbwritetable-runs-into-problems-with-file-path-on-windows-7-x64

I can confirm the issue on Win 8.1, R 3.1.2 and RMySQL 0.10.

I guess that changing line 134 in table.R from:

fn <- normalizePath(tempfile("rsdbi"), mustWork = FALSE)
to:
fn <- normalizePath(tempfile("rsdbi"), winslash = "/", mustWork = FALSE)

will be the solution, but am at the moment unfortunately not at a computer where I can test this myself.

I've getting weird behavior with dates in CASE statements - perhaps it is MySQL but its seems to be the driver and is new since the 0.9.x branch. In certain cases when I return a date from a CASE statement the date is coerced to posix and mangled. It seems to occur if there is an ELSE clause in the CASE, but can't make any sense of why this would occur. (Just tested with a fresh pull of the master branch). I initially ran into this returning date fields from a table, but this reproduces the problem:

x = dbSendQuery(source_conn, "select date('1968-10-22') as foo, 
      (case when TRUE then  date('1968-10-22') else NULL end) as biff, 
      (case when TRUE then date('1968-10-22') end) as bap, 
      (case when TRUE then date('1968-10-22') else date('1968-10-23') end) as baz;")
dbColumnInfo(x)

  name    type
1  foo    Date
2 biff POSIXct
3  bap    Date
4  baz POSIXct

dbFetch(x)

         foo              biff        bap               baz
1 1968-10-22 -1-11-30 00:20:08 1968-10-22 -1-11-30 00:20:08

directly from MySQL

mysql> select date('1968-10-22') as foo, 
          (case when TRUE then  date('1968-10-22') else NULL end) as biff, 
          (case when TRUE then date('1968-10-22') end) as bap, 
          (case when TRUE then date('1968-10-22') else date('1968-10-23') end) as baz;
+------------+------------+------------+------------+
| foo        | biff       | bap        | baz        |
+------------+------------+------------+------------+
| 1968-10-22 | 1968-10-22 | 1968-10-22 | 1968-10-22 |
+------------+------------+------------+------------+
1 rows in set (0.03 sec)

mysql> 

Some users are experiencing some odd behavior with RPostgreSQL after the loading of RMySQL.

For example:

library(RPostgreSQL)
library(dplyr)
src_postgres(yourInformationhere)

Works, but

library(RMySQL)
library(RPostgreSQL)
library(dplyr)
src_postgres(yourInformationhere)

Does not appear to work as expected. I'm not sure what other debugging information to provide. If there is a guide for that, please be so kind as to point me in that direction. I can see that your group has produced RPostgres and it does appear to work alongside RMySQL without issue, but it is still surprising/undesirable that the presence of RMySQL would cause another package to fail to operate.

See: http://stackoverflow.com/questions/16403460/dbconnect-with-r-3-0-on-ubuntu-12-04-x64-error-in-as-integerfrom-cannot-co/27798926#27798926

e.g. host and port connected to, and name of database

The BDR says:

Authors@R field should be a call to person(), or combine such calls.

Not sure what is wrong.

We are using Percona Server, the an enhanced, drop-in MySQL replacement.

Trying to install RMySQL results in the following error:

File mysql.h not found. Please install mysql development library, e.g: libmysqlclient-dev (deb) or mariadb-devel (rpm).

It seems that mysql.h is in /usr/include and the libmysqlclient so files are in /usr/lib.

Setting MYSQL_INC and PKG_LIBS like this:

Sys.setenv(MYSQL_INC = "/usr/include");
Sys.setenv(PKG_LIBS = "/usr/lib");

solves the problem for us, but wouldn't it be better to use mysql_config.

# mysql_config
Usage: /usr/bin/mysql_config [OPTIONS]
Options:
        --cflags         [-I/usr/include -g -fstack-protector --param=ssp-buffer-size=4 -Wformat-security -fPIC -g -static-libgcc -fno-omit-frame-pointer -DPERCONA_INNODB_VERSION=rel33.0  -fPIC -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DMY_PTHREAD_FASTMUTEX=1]
        --include        [-I/usr/include]
        --libs           [-L/usr/lib -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl]
        --libs_r         [-L/usr/lib -lmysqlclient_r -lpthread -lm -lrt -lssl -lcrypto -ldl]
        --plugindir      [/usr/lib/mysql/plugin]
        --socket         [/var/run/mysqld/mysqld.sock]
        --port           [0]
        --version        [5.5.35]
        --libmysqld-libs [-L/usr/lib -lmysqld]
        --variable=VAR   VAR is one of:
                pkgincludedir [/usr/include]
                pkglibdir     [/usr/lib]
                plugindir     [/usr/lib/mysql/plugin]

It seems to me that it could make the configure script much simpler.

The registry-checking code in zzz.R seems to assume that every element of

utils::readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)

will be a list. However, it looks like this is not necessarily the case (I downloaded MySQL Server 5.6 on Windows XP today and the first element is a vector). This creates the issue identified here.

I have a bit(1) variable in a MySQL database table with some 0 and some 1 values. Reading this into R however gives only 0 values. Tried both RMySQL_0.8-0 and RMySQL_0.9-3 (from GitHub), same result.

MySQL installed with 'brew install mysql --client-only --universal'
R session info:

R version 3.1.2 (2014-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] dplyr_0.3.0.2 RMySQL_0.8-0  DBI_0.3.1    

loaded via a namespace (and not attached):
[1] assertthat_0.1 magrittr_1.0.1 parallel_3.1.2 Rcpp_0.11.3    tools_3.1.2   

I've started to use both packages RMySQL and doparallel to make some calculations from data stored in my database.

My basic workflow is:

handler1 <- connect to database
fetch a list of items to process (using handler1)
disconnect handler1 from database

foreach (i:nrow(items) %dopar%
  handler2 <- connect to database
  fetch a list of subitems (using handler2)
  make some inserts
  disconnect handler2 from database
end

Using doparallel, I've seen several segfaults, so I assume that concurrency doesn't work very well with RMySQL. I've tried then to use %do% to try without parallelization, and I sometimes got a "too many opened connections" error.

Also, I don't understand which is the best way to initialize the connection to the database. In all connections, should I use:

dbConnect(MySQL(),
     user="me", password="password",
     dbname="my_db", host="localhost")

or reuse the driver instance?

foo <- dbDriver("MySQL")
dbConnect(foo,
     user="me", password="password",
     dbname="my_db", host="localhost")

In this case, can initialize it at the very beginning and use it inside the threads?