I'm facing an issue after DB restore from Old version to new version

Chandu147Chandu147 Vertica Customer
edited February 2022 in General Discussion

I see below error while running SSIS job to process data from OLTP to Vertica DB. I have upgraded to new vertica version and restored DB from Old to New version. SSIS job started running and found below error.
(Migrated from Centos : vertica-8.1.1-0.x86_64 to Amazon Linux2 : vertica-10.1.0-3.x86_64)

ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
Error while running this step!

Couldn't execute SQL: DROP TABLE IF EXISTS tempANALYZE_CONSTRAINTS;
CREATE LOCAL TEMPORARY TABLE tempANALYZE_CONSTRAINTS
(
"Schema Name" varchar(128),
"Table Name" varchar(128),
"Column Names" varchar(128),
"Constraint Name" varchar(128),
"Constraint Type" varchar(128),
"Column Values" varchar(128)
)
ON COMMIT PRESERVE ROWS
ORDER BY "Table Name"
SEGMENTED BY hash("Table Name") ALL NODES KSAFE 0
;

also below error in my log file
[Vertica]VJDBC ERROR: Cannot plan query because no super projections are safe

at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.lang.Thread.run(Unknown Source)

Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't execute SQL: DROP TABLE IF EXISTS tempANALYZE_CONSTRAINTS;
CREATE LOCAL TEMPORARY TABLE tempANALYZE_CONSTRAINTS
(
"Schema Name" varchar(128),
"Table Name" varchar(128),
"Column Names" varchar(128),
"Constraint Name" varchar(128),
"Constraint Type" varchar(128),
"Column Values" varchar(128)
)
ON COMMIT PRESERVE ROWS
ORDER BY "Table Name"
SEGMENTED BY hash("Table Name") ALL NODES KSAFE 0 ;

Best Answers

  • Bryan_HBryan_H Vertica Employee Administrator
    Answer ✓

    Does the SSIS job define the SQL that is shown here? Or is it coming from a driver or SSIS itself? If you have access to the SQL, it should work if you change the final clause to "ALL NODES KSAFE 1"

  • Chandu147Chandu147 Vertica Customer
    Answer ✓

    Thank so much Bryan, this seems to be working. I don't see any error and the job is successful.

Answers

  • Why ksafe 0? Is this a single node cluster?

  • Chandu147Chandu147 Vertica Customer

    Thank you for the quick response. This is a cluster setup with 3 nodes.

  • Chandu147Chandu147 Vertica Customer

    I'm still facing this issue on my production instances as below even after setting KSAFE 1. Any help is appriciated.

    • Execute SQL script.0 - at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
    • Execute SQL script.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    • Execute SQL script.0 - at java.lang.Thread.run(Unknown Source)
    • Execute SQL script.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    • Execute SQL script.0 - Couldn't execute SQL: DROP TABLE IF EXISTS tempANALYZE_CONSTRAINTS;
    • Execute SQL script.0 - CREATE LOCAL TEMPORARY TABLE tempANALYZE_CONSTRAINTS
      [Vertica]VJDBC ERROR: Inappropriate usage of meta-function ("analyze_constraints")
    • Execute SQL script.0 -
    • Execute SQL script.0 -
    • Execute SQL script.0 - (
    • Execute SQL script.0 - "Schema Name" varchar(128),
    • Execute SQL script.0 - "Table Name" varchar(128),
    • Execute SQL script.0 - "Column Names" varchar(128),
    • Execute SQL script.0 - "Constraint Name" varchar(128),
    • Execute SQL script.0 - "Constraint Type" varchar(128),
    • Execute SQL script.0 - "Column Values" varchar(128)
    • Execute SQL script.0 - )
    • Execute SQL script.0 - ON COMMIT PRESERVE ROWS
    • Execute SQL script.0 - ORDER BY "Table Name"
    • Execute SQL script.0 - SEGMENTED BY hash("Table Name") ALL NODES KSAFE 1
    • Execute SQL script.0 - ;
  • SruthiASruthiA Vertica Employee Administrator

    ANALYZE_CONSTRAINTS is a function name.. You cannot use it for table name.. Please use different table name

    Execute SQL script.0 - Couldn't execute SQL: DROP TABLE IF EXISTS tempANALYZE_CONSTRAINTS;
    Execute SQL script.0 - CREATE LOCAL TEMPORARY TABLE tempANALYZE_CONSTRAINTS

  • Chandu147Chandu147 Vertica Customer

    I still see the issue even after renaming the table to "tempAnalyzeConstraints" from "tempANALYZE_CONSTRAINTS". Any other issue here?

    ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    Error while running this step!

    Couldn't execute SQL: DROP TABLE IF EXISTS tempAnalyzeConstraints;
    CREATE LOCAL TEMPORARY TABLE tempAnalyzeConstraints
    (
    "Schema Name" varchar(128),
    "Table Name" varchar(128),
    "Column Names" varchar(128),
    "Constraint Name" varchar(128),
    "Constraint Type" varchar(128),
    "Column Values" varchar(128)
    )
    ON COMMIT PRESERVE ROWS
    ORDER BY "Table Name"
    SEGMENTED BY hash("Table Name") ALL NODES KSAFE 1
    ;

  • Chandu147Chandu147 Vertica Customer
    edited April 2022

    posted repeatedly, no option to delete

  • Chandu147Chandu147 Vertica Customer
    edited April 2022

    a

  • SruthiASruthiA Vertica Employee Administrator

    @Chandu147 : I just tried to run it and CREATE TABLE works fine. Could you please try it via vsql?

    dbadmin=> CREATE LOCAL TEMPORARY TABLE tempAnalyzeConstraints
    dbadmin-> (
    dbadmin(> "Schema Name" varchar(128),
    dbadmin(> "Table Name" varchar(128),
    dbadmin(> "Column Names" varchar(128),
    dbadmin(> "Constraint Name" varchar(128),
    dbadmin(> "Constraint Type" varchar(128),
    dbadmin(> "Column Values" varchar(128)
    dbadmin(> )
    dbadmin-> ON COMMIT PRESERVE ROWS
    dbadmin-> ORDER BY "Table Name"
    dbadmin-> SEGMENTED BY hash("Table Name") ALL NODES KSAFE 1;
    CREATE TABLE
    dbadmin=>

Leave a Comment

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