[R-sig-DB] Repeated errors with dbReadTable and one particular table

[Mark Van De Vyver]

Hi
Thanks for the effort put into developing the DBI and RMySQL packages.

My set up:
Windows xp (latest patches)
MySQL 5.0.18- nt (and 5.0.19-nt) TCP-ip connections
R 2.2.1
DBI built with R 2.2.1
RMySQL 0.5-7

I have come across a problem when I use dbReadTable() to access one of
my database tables.
There are many (10,000+) tables on this DB but I have on always seen
the same problem with this table and no other.  The relevant code has
been run on three quite different occasions. E.g after a full disk
defrag and chkdsk /f, and after creating the tables from 'scratch'
i.e. from a 'load file'.

I can run 'SHOW TABLE STATUS FROM' and 'SELECT * FROM' against this
table using the MySQL command line tool - no errors are indicated.  I
can also access/view the whole table using the MySQL QueryBrowser and
Navicat's query tool.  Using Navicat's tools I have run their "check
tables" ( normal and extended ) both return an OK indicator.

This makes me suspect there may be something happening in dbReadTables
or the RMySQL package?

This does not seem to be 'R-session' or 'MySQL-connection' related,
given that if I just skip the table in question I can process all
remaining tables - without quiting R, without reestablishing the
connection to the MySQL server.  Further, the table is at the smaller
end of the spectrum, 147 rows, ave row length = 90 bytes.
The data is such that I can't post this table on the web, but can send
this table to a DBI or RMySQL developer if they wish.

The R command used and it's output are below.

Regards
Mark

dbReadTable( dbcon, tbli, row.names="tid" )

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 '6706e2_aggtradesquotes' at
line 1)