search

s-u / RJDBC

R interface to databases using JDBC
Other
52 stars 47 forks source link

JDBC works on Oracle Java and doesn't work on OpenJDK #86

Closed ozhyrenkov closed 3 years ago

ozhyrenkov commented 3 years ago

I have a rocker-based docker container with rstudio and extra libs. There is an OpenJDK installed, rJava and RJDBC libs:

$ java -version 
openjdk version "11.0.9.1" 2020-11-04
OpenJDK Runtime Environment (build 11.0.9.1+1-Ubuntu-0ubuntu1.20.04)
OpenJDK 64-Bit Server VM (build 11.0.9.1+1-Ubuntu-0ubuntu1.20.04, mixed mode, sharing)
$ javac -version 
javac 11.0.9.1

Here is sessinoInfo()

> sessionInfo()
R version 4.0.0 (2020-04-24)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.04 LTS

Matrix products: default
BLAS/LAPACK: /usr/lib/x86_64-linux-gnu/openblas-openmp/libopenblasp-r0.3.8.so

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

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

other attached packages:
 [1] rstudioapi_0.13       RJDBC_0.2-8           rJava_0.9-13          aws.ec2metadata_0.2.0 aws.signature_0.6.0  
 [6] aws.s3_0.3.22         telegram.bot_2.4.0    RMySQL_0.10.21        stringi_1.5.3         forcats_0.5.0        
[11] stringr_1.4.0         purrr_0.3.4           readr_1.4.0           tibble_3.0.5          ggplot2_3.3.3        
[16] tidyverse_1.3.0       qdapRegex_0.7.2       tidyr_1.1.2           tictoc_1.0            urltools_1.7.3       
[21] magrittr_2.0.1        digest_0.6.27         dbplyr_2.0.0          dplyr_1.0.3           jsonlite_1.7.2       
[26] httr_1.4.2            RPostgreSQL_0.6-2     DBI_1.1.1            

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.6        lubridate_1.7.9.2 assertthat_0.2.1  mime_0.9          R6_2.5.0          cellranger_1.1.0 
 [7] backports_1.2.1   reprex_0.3.0      pillar_1.4.7      rlang_0.4.10      curl_4.3          readxl_1.3.1     
[13] RCurl_1.98-1.2    triebeard_0.3.0   munsell_0.5.0     tinytex_0.29      broom_0.7.3       compiler_4.0.0   
[19] modelr_0.1.8      xfun_0.20         pkgconfig_2.0.3   base64enc_0.1-3   tidyselect_1.1.0  fansi_0.4.2      
[25] crayon_1.3.4      withr_2.4.0       bitops_1.0-6      grid_4.0.0        gtable_0.3.0      lifecycle_0.2.0  
[31] scales_1.1.1      cli_2.2.0         fs_1.5.0          xml2_1.3.2        ellipsis_0.3.1    generics_0.1.0   
[37] vctrs_0.3.6       tools_4.0.0       glue_1.4.2        hms_1.0.0         colorspace_2.0-0  rvest_0.3.6      
[43] haven_2.3.1

I am trying to establish a connection to presto cluster:

library(RJDBC)
library(rstudioapi)
jdbc_driver_link <- 'https://repo1.maven.org/maven2/io/prestosql/presto-jdbc/331/presto-jdbc-331.jar'
driver <- JDBC('io.prestosql.jdbc.PrestoDriver', '/var/opt/presto-jdbc.jar')
conn <- dbConnect(driver, "jdbc:presto://my-presto-cluster:443", "login", askForPassword('Password'))
test <- dbGetQuery(conn, 'select * from catalog.schema.table limit 10')

It shows the error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set",  : 
  Unable to retrieve JDBC result set
  JDBC ERROR: Error executing query
  Statement: select * from hive.odyn_ua_bi.popularsearches_data limit 10

I tried to run the same from same network locally and it went successfully, the local machine system setup:

$ java -version
java version "13.0.2" 2020-01-14
Java(TM) SE Runtime Environment (build 13.0.2+8)
Java HotSpot(TM) 64-Bit Server VM (build 13.0.2+8, mixed mode, sharing)                                                                                                                                $ javac -version
javac 13.0.2

And the sessionInfo():

sessionInfo()
R version 4.0.0 (2020-04-24)
Platform: x86_64-apple-darwin17.0 (64-bit)
Running under: macOS Catalina 10.15.7

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

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

other attached packages:
[1] rstudioapi_0.13   RJDBC_0.2-8       rJava_0.9-12      RPostgreSQL_0.6-2 DBI_1.1.0        

loaded via a namespace (and not attached):
[1] httr_1.4.2     compiler_4.0.0 magrittr_1.5   R6_2.4.1       tools_4.0.0    xml2_1.3.2     rvest_0.3.5

It feels problem is in Oracle vs OpenJDK java versions, however might be something else, as far as local machine in on macos. Could you please advise something to figure this out?

s-u commented 3 years ago

Unfortunately errors in JDBC are entirely driver-specific, RJDBC only passes them through, so you are probably better off asking on the Presto mailing list. Also the driver doesn't seem to be very helpful "Error executing query" is a rather useless error to report. Since RJDBC 0.2-9 you can get the full exception object in case it provides any more useful details - a simple example using MariaDB:

> (r <- tryCatch(dbSendQuery(c, "SELECT * FROM foobar"), error=function(e) e))
<JDBC.result.error in dbSendQuery(c, "SELECT * FROM foobar"): Unable to retrieve JDBC result set
  JDBC ERROR: (conn=8) Table 'test.foobar' doesn't exist
  Statement: SELECT * FROM foobar>
> r$jex
[1] "Java-Object{java.sql.SQLSyntaxErrorException: (conn=8) Table 'test.foobar' doesn't exist}"
> r$jex$printStackTrace()
java.sql.SQLSyntaxErrorException: (conn=8) Table 'test.foobar' doesn't exist
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)
    at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:612)
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'test.foobar' doesn't exist
    at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:192)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:260)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
    ... 1 more
Caused by: java.sql.SQLException: Table 'test.foobar' doesn't exist
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1681)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1543)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1506)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:254)
    ... 2 more
ozhyrenkov commented 3 years ago

ok, thank you) So I'll go to presto team with full StackTrace. Let's close the issue, will post here updates in case I get anything out there.