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

Chandu147

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 ;

Bryan_H

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"

Chandu147

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

Vertica_Curtis

Why ksafe 0? Is this a single node cluster?

Chandu147

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

Chandu147

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 - ;

SruthiA

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

Chandu147

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
;

Chandu147

posted repeatedly, no option to delete

Chandu147

a

SruthiA

@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=>