Closing active connections using RMySQL
mysql, r
Solution
a. `dbListConnections( dbDriver( drv = "MySQL"))`
b. `dbDisconnect( dbListConnections( dbDriver( drv = "MySQL"))[[index of MySQLConnection you want to close]])`. To close all: `lapply( dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)`
Yes, you could just rewrite the table, of course you would lose all data. Or you can specify `dbWriteTable(, ..., overwrite = TRUE`).
I would also play with the other options, like `row.names`, `header`, `field.types`, `quote`, `sep`, `eol`. I've had a lot of weird behavior in RMySQL as well. I can't remember specifics, but it seems like I've had no error message when I had done something wrong, like forget to set row.names. HTH
Problem
As per my question earlier today, I suspect I have an issue with unclosed connections that is blocking data from being injected into my MySQL database. Data is being allowed into tables that are not currently being used (hence I suspect many open connections preventing uploading into that particular table). I am using RMySQL on Ubuntu servers to upload data onto a MySQL database. I'm looking for a way to a) determine if connections are open b) close them if they are. The command `exec sp_who` and `exec sp_who2` from the SQL command line returns an SQL code error. Another note: I am able to connect, complete the uploading process, and end the R process successfully, and there is no data on the server (checked via the SQL command line) when I try only that table. (By the way,: If all else fails, would simply deleting the table and creating a new one with the same name fix it? It would be quite a pain, but doable.)