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

[Prof Brian Ripley]

Both your issues are with the RMySQL package, and the R posting guide asks 
that you contact the maintainer (who does read this list).

I think you cannot expect a response on this list unless the maintainer 
tells you that he is unable to help, AND that we have a means to reproduce 
the problems.

See  http://www.r-project.org/posting-guide.html


On Tue, 23 May 2006, Mark Van De Vyver wrote:

> 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)
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595