Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query table of many DECIMAL columns causes error in terminal #204

Open
renkun-ken opened this issue Jun 13, 2017 · 5 comments
Open

Query table of many DECIMAL columns causes error in terminal #204

renkun-ken opened this issue Jun 13, 2017 · 5 comments
Labels
branch:stable

Comments

@renkun-ken
Copy link

renkun-ken commented Jun 13, 2017 •
edited
Loading

This is a reproducible example of #40 and #196. The problem can be described as: when a table contains many DECIMAL columns, query this table will result in error in R terminal (R and Rscript) but not in RStudio.

First, create a new schema test using

CREATE DATABASE test;

Second, create the following table with many DECIMAL columns:

CREATE TABLE `test`.`test_table` (
  `id` bigint(20) DEFAULT NULL,
  `x1` decimal(10,4) DEFAULT NULL,
  `x2` decimal(10,4) DEFAULT NULL,
  `x3` decimal(10,4) DEFAULT NULL,
  `x4` decimal(10,4) DEFAULT NULL,
  `x5` decimal(10,4) DEFAULT NULL,
  `x6` decimal(10,4) DEFAULT NULL,
  `x7` decimal(10,4) DEFAULT NULL,
  `x8` decimal(10,4) DEFAULT NULL,
  `x9` decimal(10,4) DEFAULT NULL,
  `x10` decimal(10,4) DEFAULT NULL,
  `x11` decimal(10,4) DEFAULT NULL,
  `x12` decimal(10,4) DEFAULT NULL,
  `x13` decimal(10,4) DEFAULT NULL,
  `x14` decimal(10,4) DEFAULT NULL,
  `x15` decimal(10,4) DEFAULT NULL,
  `x16` decimal(10,4) DEFAULT NULL,
  `x17` decimal(10,4) DEFAULT NULL,
  `x18` decimal(10,4) DEFAULT NULL,
  `x19` decimal(10,4) DEFAULT NULL,
  `x20` decimal(10,4) DEFAULT NULL,
  `x21` decimal(10,4) DEFAULT NULL,
  `x22` decimal(10,4) DEFAULT NULL,
  `x23` decimal(10,4) DEFAULT NULL,
  `x24` decimal(10,4) DEFAULT NULL,
  `x25` decimal(10,4) DEFAULT NULL,
  `x26` decimal(10,4) DEFAULT NULL,
  `x27` decimal(10,4) DEFAULT NULL,
  `x28` decimal(10,4) DEFAULT NULL,
  `x29` decimal(10,4) DEFAULT NULL,
  `x30` decimal(10,4) DEFAULT NULL,
  `x31` decimal(10,4) DEFAULT NULL,
  `x32` decimal(10,4) DEFAULT NULL,
  `x33` decimal(10,4) DEFAULT NULL,
  `x34` decimal(10,4) DEFAULT NULL,
  `x35` decimal(10,4) DEFAULT NULL,
  `x36` decimal(10,4) DEFAULT NULL,
  `x37` decimal(10,4) DEFAULT NULL,
  `x38` decimal(10,4) DEFAULT NULL,
  `x39` decimal(10,4) DEFAULT NULL,
  `x40` decimal(10,4) DEFAULT NULL,
  `x41` decimal(10,4) DEFAULT NULL,
  `x42` decimal(10,4) DEFAULT NULL,
  `x43` decimal(10,4) DEFAULT NULL,
  `x44` decimal(10,4) DEFAULT NULL,
  `x45` decimal(10,4) DEFAULT NULL,
  `x46` decimal(10,4) DEFAULT NULL,
  `x47` decimal(10,4) DEFAULT NULL,
  `x48` decimal(10,4) DEFAULT NULL,
  `x49` decimal(10,4) DEFAULT NULL,
  `x50` decimal(10,4) DEFAULT NULL,
  `x51` decimal(10,4) DEFAULT NULL,
  `x52` decimal(10,4) DEFAULT NULL,
  `x53` decimal(10,4) DEFAULT NULL,
  `x54` decimal(10,4) DEFAULT NULL,
  `x55` decimal(10,4) DEFAULT NULL,
  `x56` decimal(10,4) DEFAULT NULL,
  `x57` decimal(10,4) DEFAULT NULL,
  `x58` decimal(10,4) DEFAULT NULL,
  `x59` decimal(10,4) DEFAULT NULL,
  `x60` decimal(10,4) DEFAULT NULL,
  `x61` decimal(10,4) DEFAULT NULL,
  `x62` decimal(10,4) DEFAULT NULL,
  `x63` decimal(10,4) DEFAULT NULL,
  `x64` decimal(10,4) DEFAULT NULL,
  `x65` decimal(10,4) DEFAULT NULL,
  `x66` decimal(10,4) DEFAULT NULL,
  `x67` decimal(10,4) DEFAULT NULL,
  `x68` decimal(10,4) DEFAULT NULL,
  `x69` decimal(10,4) DEFAULT NULL,
  `x70` decimal(10,4) DEFAULT NULL,
  `x71` decimal(10,4) DEFAULT NULL,
  `x72` decimal(10,4) DEFAULT NULL,
  `x73` decimal(10,4) DEFAULT NULL,
  `x74` decimal(10,4) DEFAULT NULL,
  `x75` decimal(10,4) DEFAULT NULL,
  `x76` decimal(10,4) DEFAULT NULL,
  `x77` decimal(10,4) DEFAULT NULL,
  `x78` decimal(10,4) DEFAULT NULL,
  `x79` decimal(10,4) DEFAULT NULL,
  `x80` decimal(10,4) DEFAULT NULL,
  `x81` decimal(10,4) DEFAULT NULL,
  `x82` decimal(10,4) DEFAULT NULL,
  `x83` decimal(10,4) DEFAULT NULL,
  `x84` decimal(10,4) DEFAULT NULL,
  `x85` decimal(10,4) DEFAULT NULL,
  `x86` decimal(10,4) DEFAULT NULL,
  `x87` decimal(10,4) DEFAULT NULL,
  `x88` decimal(10,4) DEFAULT NULL,
  `x89` decimal(10,4) DEFAULT NULL,
  `x90` decimal(10,4) DEFAULT NULL,
  `x91` decimal(10,4) DEFAULT NULL,
  `x92` decimal(10,4) DEFAULT NULL,
  `x93` decimal(10,4) DEFAULT NULL,
  `x94` decimal(10,4) DEFAULT NULL,
  `x95` decimal(10,4) DEFAULT NULL,
  `x96` decimal(10,4) DEFAULT NULL,
  `x97` decimal(10,4) DEFAULT NULL,
  `x98` decimal(10,4) DEFAULT NULL,
  `x99` decimal(10,4) DEFAULT NULL,
  `x100` decimal(10,4) DEFAULT NULL,
  `x101` decimal(10,4) DEFAULT NULL,
  `x102` decimal(10,4) DEFAULT NULL,
  `x103` decimal(10,4) DEFAULT NULL,
  `x104` decimal(10,4) DEFAULT NULL,
  `x105` decimal(10,4) DEFAULT NULL,
  `x106` decimal(10,4) DEFAULT NULL,
  `x107` decimal(10,4) DEFAULT NULL,
  `x108` decimal(10,4) DEFAULT NULL,
  `x109` decimal(10,4) DEFAULT NULL,
  `x110` decimal(10,4) DEFAULT NULL,
  `x111` decimal(10,4) DEFAULT NULL,
  `x112` decimal(10,4) DEFAULT NULL,
  `x113` decimal(10,4) DEFAULT NULL,
  `x114` decimal(10,4) DEFAULT NULL,
  `x115` decimal(10,4) DEFAULT NULL,
  `x116` decimal(10,4) DEFAULT NULL,
  `x117` decimal(10,4) DEFAULT NULL,
  `x118` decimal(10,4) DEFAULT NULL,
  `x119` decimal(10,4) DEFAULT NULL,
  `x120` decimal(10,4) DEFAULT NULL,
  `x121` decimal(10,4) DEFAULT NULL,
  `x122` decimal(10,4) DEFAULT NULL,
  `x123` decimal(10,4) DEFAULT NULL,
  `x124` decimal(10,4) DEFAULT NULL,
  `x125` decimal(10,4) DEFAULT NULL,
  `x126` decimal(10,4) DEFAULT NULL,
  `x127` decimal(10,4) DEFAULT NULL,
  `x128` decimal(10,4) DEFAULT NULL,
  `x129` decimal(10,4) DEFAULT NULL,
  `x130` decimal(10,4) DEFAULT NULL,
  `x131` decimal(10,4) DEFAULT NULL,
  `x132` decimal(10,4) DEFAULT NULL,
  `x133` decimal(10,4) DEFAULT NULL,
  `x134` decimal(10,4) DEFAULT NULL,
  `x135` decimal(10,4) DEFAULT NULL,
  `x136` decimal(10,4) DEFAULT NULL,
  `x137` decimal(10,4) DEFAULT NULL,
  `x138` decimal(10,4) DEFAULT NULL,
  `x139` decimal(10,4) DEFAULT NULL,
  `x140` decimal(10,4) DEFAULT NULL,
  `x141` decimal(10,4) DEFAULT NULL,
  `x142` decimal(10,4) DEFAULT NULL,
  `x143` decimal(10,4) DEFAULT NULL,
  `x144` decimal(10,4) DEFAULT NULL,
  `x145` decimal(10,4) DEFAULT NULL,
  `x146` decimal(10,4) DEFAULT NULL,
  `x147` decimal(10,4) DEFAULT NULL,
  `x148` decimal(10,4) DEFAULT NULL,
  `x149` decimal(10,4) DEFAULT NULL,
  `x150` decimal(10,4) DEFAULT NULL,
  `x151` decimal(10,4) DEFAULT NULL,
  `x152` decimal(10,4) DEFAULT NULL,
  `x153` decimal(10,4) DEFAULT NULL,
  `x154` decimal(10,4) DEFAULT NULL,
  `x155` decimal(10,4) DEFAULT NULL,
  `x156` decimal(10,4) DEFAULT NULL,
  `x157` decimal(10,4) DEFAULT NULL,
  `x158` decimal(10,4) DEFAULT NULL,
  `x159` decimal(10,4) DEFAULT NULL,
  `x160` decimal(10,4) DEFAULT NULL,
  `x161` decimal(10,4) DEFAULT NULL,
  `x162` decimal(10,4) DEFAULT NULL,
  `x163` decimal(10,4) DEFAULT NULL,
  `x164` decimal(10,4) DEFAULT NULL,
  `x165` decimal(10,4) DEFAULT NULL,
  `x166` decimal(10,4) DEFAULT NULL,
  `x167` decimal(10,4) DEFAULT NULL,
  `x168` decimal(10,4) DEFAULT NULL,
  `x169` decimal(10,4) DEFAULT NULL,
  `x170` decimal(10,4) DEFAULT NULL,
  `x171` decimal(10,4) DEFAULT NULL,
  `x172` decimal(10,4) DEFAULT NULL,
  `x173` decimal(10,4) DEFAULT NULL,
  `x174` decimal(10,4) DEFAULT NULL,
  `x175` decimal(10,4) DEFAULT NULL,
  `x176` decimal(10,4) DEFAULT NULL,
  `x177` decimal(10,4) DEFAULT NULL,
  `x178` decimal(10,4) DEFAULT NULL,
  `x179` decimal(10,4) DEFAULT NULL,
  `x180` decimal(10,4) DEFAULT NULL,
  `x181` decimal(10,4) DEFAULT NULL,
  `x182` decimal(10,4) DEFAULT NULL,
  `x183` decimal(10,4) DEFAULT NULL,
  `x184` decimal(10,4) DEFAULT NULL,
  `x185` decimal(10,4) DEFAULT NULL,
  `x186` decimal(10,4) DEFAULT NULL,
  `x187` decimal(10,4) DEFAULT NULL,
  `x188` decimal(10,4) DEFAULT NULL,
  `x189` decimal(10,4) DEFAULT NULL,
  `x190` decimal(10,4) DEFAULT NULL,
  `x191` decimal(10,4) DEFAULT NULL,
  `x192` decimal(10,4) DEFAULT NULL,
  `x193` decimal(10,4) DEFAULT NULL,
  `x194` decimal(10,4) DEFAULT NULL,
  `x195` decimal(10,4) DEFAULT NULL,
  `x196` decimal(10,4) DEFAULT NULL,
  `x197` decimal(10,4) DEFAULT NULL,
  `x198` decimal(10,4) DEFAULT NULL,
  `x199` decimal(10,4) DEFAULT NULL,
  `x200` decimal(10,4) DEFAULT NULL,
  `x201` decimal(10,4) DEFAULT NULL,
  `x202` decimal(10,4) DEFAULT NULL,
  `x203` decimal(10,4) DEFAULT NULL,
  `x204` decimal(10,4) DEFAULT NULL,
  `x205` decimal(10,4) DEFAULT NULL,
  `x206` decimal(10,4) DEFAULT NULL,
  `x207` decimal(10,4) DEFAULT NULL,
  `x208` decimal(10,4) DEFAULT NULL,
  `x209` decimal(10,4) DEFAULT NULL,
  `x210` decimal(10,4) DEFAULT NULL,
  `x211` decimal(10,4) DEFAULT NULL,
  `x212` decimal(10,4) DEFAULT NULL,
  `x213` decimal(10,4) DEFAULT NULL,
  `x214` decimal(10,4) DEFAULT NULL,
  `x215` decimal(10,4) DEFAULT NULL,
  `x216` decimal(10,4) DEFAULT NULL,
  `x217` decimal(10,4) DEFAULT NULL,
  `x218` decimal(10,4) DEFAULT NULL,
  `x219` decimal(10,4) DEFAULT NULL,
  `x220` decimal(10,4) DEFAULT NULL,
  `x221` decimal(10,4) DEFAULT NULL,
  `x222` decimal(10,4) DEFAULT NULL,
  `x223` decimal(10,4) DEFAULT NULL,
  `x224` decimal(10,4) DEFAULT NULL,
  `x225` decimal(10,4) DEFAULT NULL,
  `x226` decimal(10,4) DEFAULT NULL,
  `x227` decimal(10,4) DEFAULT NULL,
  `x228` decimal(10,4) DEFAULT NULL,
  `x229` decimal(10,4) DEFAULT NULL,
  `x230` decimal(10,4) DEFAULT NULL,
  `x231` decimal(10,4) DEFAULT NULL,
  `x232` decimal(10,4) DEFAULT NULL,
  `x233` decimal(10,4) DEFAULT NULL,
  `x234` decimal(10,4) DEFAULT NULL,
  `x235` decimal(10,4) DEFAULT NULL,
  `x236` decimal(10,4) DEFAULT NULL,
  `x237` decimal(10,4) DEFAULT NULL,
  `x238` decimal(10,4) DEFAULT NULL,
  `x239` decimal(10,4) DEFAULT NULL,
  `x240` decimal(10,4) DEFAULT NULL,
  `x241` decimal(10,4) DEFAULT NULL,
  `x242` decimal(10,4) DEFAULT NULL,
  `x243` decimal(10,4) DEFAULT NULL,
  `x244` decimal(10,4) DEFAULT NULL,
  `x245` decimal(10,4) DEFAULT NULL,
  `x246` decimal(10,4) DEFAULT NULL,
  `x247` decimal(10,4) DEFAULT NULL,
  `x248` decimal(10,4) DEFAULT NULL,
  `x249` decimal(10,4) DEFAULT NULL,
  `x250` decimal(10,4) DEFAULT NULL,
  `x251` decimal(10,4) DEFAULT NULL,
  `x252` decimal(10,4) DEFAULT NULL,
  `x253` decimal(10,4) DEFAULT NULL,
  `x254` decimal(10,4) DEFAULT NULL,
  `x255` decimal(10,4) DEFAULT NULL,
  `x256` decimal(10,4) DEFAULT NULL,
  `x257` decimal(10,4) DEFAULT NULL,
  `x258` decimal(10,4) DEFAULT NULL,
  `x259` decimal(10,4) DEFAULT NULL,
  `x260` decimal(10,4) DEFAULT NULL,
  `x261` decimal(10,4) DEFAULT NULL,
  `x262` decimal(10,4) DEFAULT NULL,
  `x263` decimal(10,4) DEFAULT NULL,
  `x264` decimal(10,4) DEFAULT NULL,
  `x265` decimal(10,4) DEFAULT NULL,
  `x266` decimal(10,4) DEFAULT NULL,
  `x267` decimal(10,4) DEFAULT NULL,
  `x268` decimal(10,4) DEFAULT NULL,
  `x269` decimal(10,4) DEFAULT NULL,
  `x270` decimal(10,4) DEFAULT NULL,
  `x271` decimal(10,4) DEFAULT NULL,
  `x272` decimal(10,4) DEFAULT NULL,
  `x273` decimal(10,4) DEFAULT NULL,
  `x274` decimal(10,4) DEFAULT NULL,
  `x275` decimal(10,4) DEFAULT NULL,
  `x276` decimal(10,4) DEFAULT NULL,
  `x277` decimal(10,4) DEFAULT NULL,
  `x278` decimal(10,4) DEFAULT NULL,
  `x279` decimal(10,4) DEFAULT NULL,
  `x280` decimal(10,4) DEFAULT NULL,
  `x281` decimal(10,4) DEFAULT NULL,
  `x282` decimal(10,4) DEFAULT NULL,
  `x283` decimal(10,4) DEFAULT NULL,
  `x284` decimal(10,4) DEFAULT NULL,
  `x285` decimal(10,4) DEFAULT NULL,
  `x286` decimal(10,4) DEFAULT NULL,
  `x287` decimal(10,4) DEFAULT NULL,
  `x288` decimal(10,4) DEFAULT NULL,
  `x289` decimal(10,4) DEFAULT NULL,
  `x290` decimal(10,4) DEFAULT NULL,
  `x291` decimal(10,4) DEFAULT NULL,
  `x292` decimal(10,4) DEFAULT NULL,
  `x293` decimal(10,4) DEFAULT NULL,
  `x294` decimal(10,4) DEFAULT NULL,
  `x295` decimal(10,4) DEFAULT NULL,
  `x296` decimal(10,4) DEFAULT NULL,
  `x297` decimal(10,4) DEFAULT NULL,
  `x298` decimal(10,4) DEFAULT NULL,
  `x299` decimal(10,4) DEFAULT NULL,
  `x300` decimal(10,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then put some random data in the table (user and password need to be supplied):

library(data.table)
library(DBI)
con <- dbConnect(RMySQL::MySQL(), host = "127.0.0.1", user = "<user>", password = "<pass>", dbname = "test")
data <- data.table(id = 1:1000)
cols <- paste0("x", 1:300)
data_cols <- lapply(cols, function(x) rnorm(1000))
data[, (cols) := data_cols]
dbWriteTable(con, "test_table", data, row.names = FALSE, append = TRUE)
dbDisconnect(con)

Last, start an R terminal and query data from the table or create a script like the following and run it via Rscript:

library(DBI)
con <- dbConnect(RMySQL::MySQL(), host = "127.0.0.1", user = "<user>", password = "<pass>", dbname = "test")
data <- dbGetQuery(con, "select * from test_table")
dbDisconnect(con)
print(nrow(data))

Then the error is always reproducible:

Loading required package: methods
Loading required namespace: RMySQL
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

My session info:

R version 3.4.0 (2017-04-21)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.2 LTS

Matrix products: default
BLAS: /usr/lib/openblas-base/libblas.so.3
LAPACK: /usr/lib/libopenblasp-r0.2.18.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] methods   stats     graphics  grDevices utils     datasets  base     

other attached packages:
[1] DBI_0.6-1

loaded via a namespace (and not attached):
[1] compiler_3.4.0 RMySQL_0.10.11
@renkun-ken
Copy link
Author

renkun-ken commented Jun 13, 2017 •
edited
Loading

I capture the error and stacktrace as following:

Available environments had calls:
1: source("R/test.R")
2: withVisible(eval(ei, envir))
3: eval(ei, envir)
4: eval(ei, envir)
5: test.R#6: dbGetQuery(con, "select * from test_table")
6: dbGetQuery(con, "select * from test_table")
7: dbSendQuery(conn, statement, ...)
8: dbSendQuery(conn, statement, ...)
9: .local(conn, statement, ...)
10: new("MySQLResult", Id = rsId)
11: initialize(value, ...)
12: initialize(value, ...)
13: validObject(.Object)
14: stop(msg, ": ", errors, domain = NA)
15: (function (e) 
{
    dump.frames(to.file = TRUE)
    browser()
})()

and I notice that in 9: .local(conn, statement, ...), rsId = c("names", "class") instead of an integer as supposed?

@renkun-ken
Copy link
Author

renkun-ken commented Jun 13, 2017 •
edited
Loading

In the same session, when I create the connection and disconnect, and connect again, then the error disappears magically. But when multiple tables are queried, the error still occurs like the following:

Error in dbFetch(rs, n = -1, ...) : 
  INTEGER() can only be applied to a 'integer', not a 'list'
Calls: load_data -> sys.source -> eval -> eval
Error in .local(dbObj, ...) : 
  INTEGER() can only be applied to a 'integer', not a 'list'
Calls: load_data -> sys.source -> eval -> eval
Execution halted

@krlmlr
Copy link
Member

krlmlr commented Jun 30, 2017

Thanks. Is this RMySQL from CRAN or from GitHub?

@renkun-ken
Copy link
Author

It is CRAN version.

@renkun-ken
Copy link
Author

I switch to RMariaDB and this problem is gone.

@renkun-ken renkun-ken mentioned this issue Sep 26, 2017
Closed
@areedv areedv mentioned this issue Jun 6, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
branch:stable
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@krlmlr @renkun-ken