We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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 ;

Tagged:

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

  • Vertica_CurtisVertica_Curtis Employee

    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 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 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
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...