Error in .jcall(conn@jc, "V", "setAutoCommit", ac): java.sql.SQLRecoverableException: [Vertica][VJ

asethi · October 2020

Hi Team,
I am trying to insert multiple records (range from 100 to 15000) in Vertica table using dbWriteTable function from my R environment and getting below error message:
Error Message
Error in .jcall(conn@jc, "V", "setAutoCommit", ac):
java.sql.SQLRecoverableException: [Vertica]VJDBC IOException while communicating with server: java.io.EOFException.

Please Note
1) This issue is intermittent and not dependent on number of records to be inserted. Usually happening 1 out of 4 times with different Vertica tables.
2) dbWriteTable internally calls dbSendUpdate function, the error message is fetched from there
3) Source code of dbWriteTable - https://github.com/cran/RJDBC/blob/master/R/class.R
4) Source code of dbSendUpdate - https://github.com/cran/RJDBC/blob/master/R/class.R

Additional Information - While executing dbWriteTable, a lock is getting added on the same table on which data is inserted from dataframe. Also, the lock remains there even after the database connection is closed. I have to manually kill the session to remove the lock from the table.
Vertica JDBC driver used - vertica-jdbc-9.3.0-0.jar

Thanks in advance.
Regards,
Ankit

Hibiki · October 2020

Can you provide the sample code to reproduce the issue?

asethi · October 2020

Dear @Hibiki
Please find below the sample code -

Include Library

library(DBI)
library(RJDBC)

Set Vertica Driver

v_driver = RJDBC::JDBC(driverClass = "com.vertica.jdbc.Driver", classPath = "~/vertica-jdbc-9.3.0-0.jar")

Create connection with Vertica Database

connection <-
dbConnect(
v_driver,
"jdbc:vertica://:5433/?searchpath=",
"",
""
)

Create a table in Database

create_query <- "CREATE TABLE SCHEMANAME.test_ioexception(
CDT_ID int,
PRODUCT_ID int,
LOYALTY_SCORE numeric(18,4),
PRODUCT_DESC varchar(50),
BRAND_DESC varchar(50),
VENDOR_DESC varchar(50),
HIERARCHY_LEVEL_1 varchar(50),
HIERARCHY_LEVEL_2 varchar(50),
HIERARCHY_LEVEL_3 varchar(50),
HIERARCHY_LEVEL_4 varchar(50),
ASSORTMENT_CODE varchar(15),
SALES_PY numeric(18,4),
SALES_CY numeric(18,4),
UNITS_CY int,
MARGIN_CY numeric(18,4),
TLI_CY numeric(18,4),
LI_CY numeric(18,4),
SI_CY numeric(18,4),
SWS_PY numeric(18,4),
SWS_CY numeric(18,4),
CSB varchar(512),
ARAB_SALES_CY numeric(18,4),
ASIAN_SALES_CY numeric(18,4),
INDIAN_SALES_CY numeric(18,4),
WESTERN_SALES_CY numeric(18,4),
FAMILY_SALES_CY numeric(18,4),
SINGLE_SALES_CY numeric(18,4),
COUPLE_SALES_CY numeric(18,4),
LOYAL_PLUS_SALES_CY numeric(18,4),
LOYAL_SALES_CY numeric(18,4),
OCC_INACT_SALES_CY numeric(18,4),
STOREWEEKS_PY int,
STOREWEEKS_CY int
);"

dbSendUpdate(connection,create_query)

Insert records in the sample table | execute below query in Vertica to insert records

asethi · October 2020

Create a sample df to be inserted into the table

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 30
tbl_name <- "test_ioexception"

Execute dbWriteTable | First time

DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Second time

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 60
DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Third time

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 120
DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Fourth time

DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Fifth time

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 360
DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Sixth time

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 720
DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

Execute dbWriteTable | Seventh time

df <- dbGetQuery(connection, "SELECT * FROM test_ioexception") # Count 1440
DBI::dbWriteTable(conn = connection, name = tbl_name, value = df, overwrite = FALSE, append = TRUE)

the last execution throws below error message -

Error in .local(conn, statement, ...) :

execute JDBC update query failed in dbSendUpdate ([Vertica]JDBC Null pointer exception.)

Error in .jcall(conn@jc, "V", "setAutoCommit", ac) :

java.sql.SQLRecoverableException: [Vertica]VJDBC IOException while communicating with server

This also creates a lock in database as below entry

v_ca_dev_node0001,v_ca_dev_node0002,v_ca_dev_node0003,v_ca_dev_node0004,v_ca_dev_node0005,v_ca_dev_node0006,v_ca_dev_node0007 Table:MCK_ASSORTMENT_UC_ZONE.test_ioexception 54043195880934894 45035996283585037 Txn: a000000096c20d 'INSERT INTO test_ioexception VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' I TRANSACTION 2020-10-27 15:14:18 2020-10-27 15:14:18

Hibiki · October 2020

Thank you for sharing the reproducer. I tried to insert the data repeatedly but I could not hit the same error. The current number of rows was 23040.

Which Vertica version are you using now? I know the JDBC version is 9.3.0.
Which Java runtime and version are you using now? I used OpenJDK 11.0.8.
Which R version, DBI version, RJDBC version are you using now? I used R 3.5.0, DBI 1.1.0, RJDBC 0.2-8.
On which server are you executing this script, on Vertica node or any other remote server?
Can you see any error or warning message related to this transaction in vertica.log files?
Can you run the following query and share the result?

SELECT TIME, node_name, retrans, packet_count, retrans_per_second
FROM (
    SELECT A .time, A .node_name, A .time_interval, A .retrans, A .packet_count AS packet_count,
           ((A .retrans / (A .time_interval / '00:00:01' :: INTERVAL)))::numeric(18, 2) AS retrans_per_second
    FROM (
        SELECT time::TIMESTAMP AS TIME, node_name,
               (retrans - lag(retrans, 1, NULL::INT) OVER (PARTITION BY node_name ORDER BY time::TIMESTAMP)) AS retrans,
               (time::TIMESTAMP - lag(time::TIMESTAMP, 1, NULL::TIMESTAMP) OVER (PARTITION BY node_name ORDER BY time::TIMESTAMP)) AS time_interval,
               (packet_sent - lag(packet_sent, 1, NULL::INT) OVER (PARTITION BY node_name ORDER BY time::TIMESTAMP)) AS packet_count
        FROM dc_spread_monitor) A
    WHERE A .retrans > 0 OR A .packet_count > 0
    ORDER BY A .time, A .node_name) B
WHERE retrans_per_second > 10
ORDER BY 3 DESC
LIMIT 50;

asethi · October 2020

Please find below the answers-
1) Which Vertica version are you using now? I know the JDBC version is 9.3.0.
Ans - Vertica Analytic Database v9.2.1-18
2) Which Java runtime and version are you using now? I used OpenJDK 11.0.8.
Ans -
java -version
openjdk version "1.8.0_272"
OpenJDK Runtime Environment (build 1.8.0_272-8u272-b10-0ubuntu1~16.04-b10)
OpenJDK 64-Bit Server VM (build 25.272-b10, mixed mode)

3) Which R version, DBI version, RJDBC version are you using now? I used R 3.5.0, DBI 1.1.0, RJDBC 0.2-8.
R version - R version 3.6.0
DBI Version - 1.1.0
RJDBC - 0.2-8
4) On which server are you executing this script, on Vertica node or any other remote server?
Ans - Remote Server
Can you see any error or warning message related to this transaction in vertica.log files?
Can you run the following query and share the result? -
We have asked our DB Admin team to check for error message and query result. We will get back to you soon.

Hibiki · November 2020

I tried to reproduce the issue but could not. I tried with the following software and packages.

Vertica 9.2.1-18
R 3.6.0
openjdk 1.8.0_262
DBI 1.1.0
RJDBC 0.2-9
JDBC driver 9.3.0-0

I guess the environmental issue has happened on your cluster. Please look into your system and network.

asethi · November 2020

Thanks Hibiki for the information. There was some environmental issue, it has been now resolved. Thank you very much for the help.

Hibiki · November 2020

Thank you for letting me know. I'm glad to hear you have solved the issue.