2

I don't know that this is an RSQLite/RMySQL conflict, but that's my best guess so far.

I use RMySQL to get data from a MySQL database, then I close that connection with dbDisconnect(). Then I use these data to in a record linking process using the RLBigDataLinkage() function in the RecordLinkage package. This function uses an SQLite database and the RSQLite package because there are two many possible matches to keep all of them in memory. As far as I can tell from the documentation, there is no way to explicitly close the SQLite connection.

Then I use these matches to pull a larger dataset from my MySQL database, again with RMySQL. I can connect to the database, but when I query, I get the following error:

Error in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: MySQL server has gone away)

This happens even with trivial queries.

> dbGetQuery(db, "SELECT * FROM ann_id_info LIMIT 5")
Error in mysqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: MySQL server has gone away)

I can still query from the MySQL command line, though.

Is there a way I can disconnect an "unknown" RSQLite connection? Thanks!

Richard Herron
  • 9,760
  • 12
  • 69
  • 116
  • I have noticed the same sort of conflict when using sqldf and RMySQL, but I haven't used RecordLinkage -- can you provide a simple example to reproduce the error? IIRC, we need to find a way to specify a path for the SQLite database instead of using a default. – Jeffrey Breen May 07 '11 at 14:49
  • @Jeffrey -- I wrote independent sample code, and it worked! And pored over my original code again and found that I had a subtlety bad query, which I fixed and it worked! So I'm happy now, but really wondering why my trivial query above fails after running the bad query? I am voting to close until I can produce a more repeatable problem. – Richard Herron May 07 '11 at 18:33
  • @Jeffrey, The development version of sqldf (available on the sqldf web site) supports both MySQL and SQLite. See if that solves your conflict. – G. Grothendieck May 08 '11 at 01:13
  • 4
    @G. Grothendieck -- Thanks, I will take a look. I have always resolved the conflict in the past by adding an explicit `drv='SQLite'` to my `sqldf()` calls. – Jeffrey Breen May 10 '11 at 14:18

0 Answers0