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

asethiasethi Vertica Customer

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

Answers

  • HibikiHibiki Vertica Employee Employee

    Can you provide the sample code to reproduce the issue?

  • asethiasethi Vertica Customer

    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

  • asethiasethi Vertica Customer

    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

  • HibikiHibiki Vertica Employee Employee

    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;
    
  • asethiasethi Vertica Customer

    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.

  • HibikiHibiki Vertica Employee Employee
    edited 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.

  • asethiasethi Vertica Customer

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

  • HibikiHibiki Vertica Employee Employee

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file