[Vertica][VJDBC](3007) ERROR: DDL statement interfered with this statement

vincent_wu

Hi,
I'm loading data into Vertica6.1.3 with copy command. There are 15 processes loading data into vertica simultaneously. Some times we facing the error as below:
com.hp.bto.dw.abc.executor.db.exception.SqlScriptExecuteException: java.sql.SQLException: [Vertica][VJDBC](3007) ERROR: DDL statement interfered with this statement

one copy command as below:

COPY dws.HEADER_PLHD_1_SRC FROM LOCAL

'/DataWarehouse/ppm-init-new/1.txt'

DELIMITER '|' RECORD TERMINATOR '#'

ESCAPE AS '\'

EXCEPTIONS /DataWarehouse/ExternalSources/1_EXCEPTION.log'

REJECTED DATA '/DataWarehouse/ExternalSources/_REJECT.txt'

SKIP 1 ENFORCELENGTH

 

really appreciate for your answer and comments.

Jim_Krieger

This is most likely a catalog issues with too many DDL statements running the cluster at a time.

• How large is your catalog? 
• What % of memory is is consuming ? 
• How many tables/projections? 
• Is the repeatable or sporadic ? 

Possible solutions

• Decrease the concurrent DDL statements
• Reduce the size of catalog and % in memory?
• Place /catalog on /data file system and maximize disks on that raid set
• Upgrade to 7.1 - it helps
  

vincent_wu

thanks Jim.
the catalog is totally about 200M, and the memory consume is about 40%.
There are 16GB totally and all the data volume we are loading is about 5GB.
there are about 100 tables in database and there is only one supper projection for each of them
Yes, we can repeat the issue. run the copy for 10 times and it would hit the issue 4-6 times.

as you mention solution, upgrade to 7.1.
 I wonder is there a patch or a hotfix if I don't want to upgrade to 7.1

kesten_broughto

I am seeing this on vertica 7.1  Creating and then populating about 40 tables, truncated to a 500 row limit on insert on each.  Nothing particularly strange about the schema.  Widest table is 10 columns.  Mostly linked uids.

[Deleted User]

Hi Vincent and Kesten,

Good question.  I guess the immediate follow-up for an error message that says "DDL statement interfered with this statement" would be, what DDL statements were running at the same time as this statement?  Could you check through your Vertica logs and check to see what, if any, other statements were running at the same time as the statement that errored out?

Thanks,
Adam

rcaro

After performing " ALTER TABLE Dim_Product DROP COLUMN PresentationCode " (as superuser), now the table has 0 rows, all privileges on the table were revoked and can't even perform a simple INSERT command into the table.

The error shown when trying to insert a new row into this table is:
" [Code: 3007, SQL State: 55000] [Vertica]VJDBC ERROR: DDL statement interfered with this statement"

I have already tried shutting down and turning back up the database and even the server.

Vertica documentation mentions the error but not the resolution in this particular case.

asaunders

Has anyone come up with a good way to diagnose this? I just got this error today, and I checked the query_requests table and there were no other DDL statements on that particular table during the time of this error.

Jim_Knicely

@asaunders - Which version of Vertica are you running?

asaunders

@Jim_Knicely Sorry, I did not see your response until now. The Vertica cluster in question is on version "Vertica Analytic Database v8.1.1-7"

Jim_Knicely

@asaunders - Can you try a MERGE statement into that table? Do you get the same error?

There is a fix in Vertica 9.1 that may be pertinent to your issue:

See (VER-60535):
https://my.vertica.com/docs/ReleaseNotes/9.1.x/Vertica_9.1.x_Release_Notes.htm