Hi
I am running R2.15.2 64-bit on Windows 7, using RODBC 1.3-6, MySQL5.5.20,
MySQL Connector 5.5.2 - these are the latest 64-bit versions AFAIK.
sqlQuery and sqlSave work fin as expected, but in a long session with a few
sqlSave() calls, I get an error, for example:
Error in sqlSave(channel = channel, dat = USArrests[, 1, drop = FALSE], :
HY000 1461 [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.20]Can't create more
than max_prepared_stmt_count statements (current value: 16384)
[RODBC] ERROR: Could not SQLPrepare 'INSERT INTO `usarrests` ( `murder` )
VALUES ( ? )'
In my setup the MySQL global variable max_prepared_stmt_count has the
default setting of 16K. If I reset the variable higher, I can run a while
longer, but this is not a permanent solution.
Digging around for a solution, I see that the following may cast some
light:
show global status like 'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_close | 0 |
| Com_stmt_execute | 49931 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 36 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 36 |
| Com_stmt_send_long_data | 0 |
+-------------------------+-------+
If I understand right, the number of Com_stmt_close should be 'close to or
equal to' Com_stmt_execute, but is not. Rolling back to all 32-bit R2.13.2
etc does work, all entries in the table above remaining at zero,
The number Com_stmt_execute increases with each row written using
sqlSave(), but does not increase if I use sqlQuery()
#This causes Com_stmt_execute to increase 50:
sqlQuery(channel=channel,query="DROP TABLE IF EXISTS USArrests")
sqlSave(channel=channel, dat=USArrests[,1,drop=FALSE],rownames=FALSE)
#This causes no change in Com_stmt_execute :
sqlQuery(channel=channel,query="INSERT INTO USArrests (murder) values (1)")
This behaviour did not occur with R2.13.2 & RODBC 1.3-3 32-bit.
I could just revert one thing at a time to narrow it down but if anyone can
offer a shortcut I'd be delighted.
Thanks
Giles Heywood
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
