Skip to main content

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

  • November 4, 2014
  • 9 replies
  • 22 views
vincent_wu
Forum|alt.badge.img+1
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.

    9 replies

    Jim_Krieger
    • Jim_Krieger
    • Participating Frequently
    • November 4, 2014
    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
    Forum|alt.badge.img+1
    • vincent_wu
    • Author
    • Participating Frequently
    • November 11, 2014
    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
    Forum|alt.badge.img
    • kesten_broughto
    • Participating Frequently
    • December 2, 2014
    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.
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • December 2, 2014
    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
    • rcaro
    • New Participant
    • August 4, 2017

    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
    • New Participant
    • May 30, 2018

    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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • May 30, 2018

    @asaunders - Which version of Vertica are you running?

    • asaunders
    • New Participant
    • June 26, 2018

    @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
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • July 3, 2018

    @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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement