First time query often produces error

[renkun-ken]

I use a MySQL database which updates several times in a day. The production system start querying several tables in this database from 9:00 AM every day. But recently the first time query often ends up with the following error, but when I restart the query (no code changed) the process is smooth without error.

Error in validObject(.Object) : 
  invalid class “MySQLResult” object: invalid object for slot "Id" in class "MySQLResult": got class "character", should be or extend class "integer"

Sometimes, the error message is slightly different:

Error in validObject(.Object) : 
  invalid class “MySQLResult” object: invalid object for slot "Id" in class "MySQLResult": got class "list", should be or extend class "integer"

I inspect the table which produces this error, it has hundreds of columns of VARCHAR(e.g. 100, 40, 8, 10, 1), DECIMAL(20, 4), DATETIME, and the primary key is VARCHAR(100).

The traceback is

Error in validObject(.Object) : 
  invalid class “MySQLResult” object: invalid object for slot "Id" in class "MySQLResult": got class "character", should be or extend class "integer"
 
20. stop(msg, ": ", errors, domain = NA)
19. validObject(.Object)
18. initialize(value, ...)
17. initialize(value, ...)
16. new("MySQLResult", Id = rsId)
15. .local(conn, statement, ...)
14. dbSendQuery(conn, statement, ...)
13. dbSendQuery(conn, statement, ...)
12. dbGetQuery(con, "select * from ASHAREFINANCIALINDICATOR")
...

The problem looks very similar to #40.

On another machine, when I query the same table from the database, the error is

$ R --slave --restore -f test-wind.R
Error in validObject(.Object) : 
  invalid class “MySQLResult” object: 1: invalid object for slot "Id" in class "MySQLResult": got class "simpleWarning", should be or extend class "integer"
invalid class “MySQLResult” object: 2: invalid object for slot "Id" in class "MySQLResult": got class "warning", should be or extend class "integer"
invalid class “MySQLResult” object: 3: invalid object for slot "Id" in class "MySQLResult": got class "condition", should be or extend class "integer"
Calls: dbGetQuery ... .local -> new -> initialize -> initialize -> validObject
In addition: There were 50 or more warnings (use warnings() to see the first 50)
Execution halted

$ Rscript test-wind.R
Loading required package: methods
Error in dbFetch(rs, n = -1, ...) : 
  INTEGER() can only be applied to a 'integer', not a 'char'
Calls: dbGetQuery -> dbGetQuery -> dbFetch -> dbFetch -> .Call
In addition: There were 50 or more warnings (use warnings() to see the first 50)
Error in .local(dbObj, ...) : 
  INTEGER() can only be applied to a 'integer', not a 'char'
Calls: dbGetQuery ... dbClearResult -> dbIsValid -> dbIsValid -> .local -> .Call
Execution halted