[jira] [Commented] (DERBY-4160) getMetaData().getIndexInfo crashes with "ERROR X0Y68: Column 'PARAM1' already exists."

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4160?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13903280#comment-13903280 ] 

Knut Anders Hatlen commented on DERBY-4160:
-------------------------------------------

On 15/Apr/09 I commented:

{quote}
If the lock timeout in the nested transaction happens after addSPSParams() has been called, the rows will be there when we retry the operation in the user transaction since we commit the nested transaction instead of aborting it. I think we can't abort the nested transaction because it will also abort the parent transaction, but perhaps it is possible to use savepoints to make sure that all the changes made by the nested transaction are rolled back before we retry.
{quote}

In the attached repro, it looks like that's indeed what's happening. There's a lock conflict that prevents the nested transaction from completing the creation of the SPS immediately, so it is retried in the parent transaction. But the nested transaction has already added rows for some of the parameters, so the retry in the parent transaction fails because it tries to insert duplicates.

As already mentioned, aborting/rolling back the nested transaction before retrying will also cause the parent transaction to be aborted, so we cannot do that. It was suggested that we use savepoints instead. Unfortunately, savepoints are not supported in nested transactions, so that won't work either.

One possible workaround might be to drop the added rows before retrying. I'm experimenting with adding

{code}
        dropAllColumnDescriptors(uuid, tc);
{code}

to the beginning of DataDictionaryImpl.addSPSParams(). That makes the repro stop failing. I'm running the regression test suite now to see if that change breaks something.

> getMetaData().getIndexInfo crashes with "ERROR X0Y68: Column 'PARAM1' already exists."
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-4160
>                 URL: https://issues.apache.org/jira/browse/DERBY-4160
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>         Environment: FreeBSD java 1.6.0, 64-Bit Server VM; DataNucleus JDO
>            Reporter: ArtemGr
>              Labels: derby_triage10_5_2
>         Attachments: D4160.java
>
>
> The following code in DataNucleus:
> rs = conn.getMetaData().getIndexInfo(catalogName, schemaName, tableName, false,
> true);
> triggers an Exception (http://gist.github.com/95679):
> Caused by: java.sql.SQLException: Column 'PARAM1' already exists.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
>         ... 105 more
> Caused by: ERROR X0Y68: Column 'PARAM1' already exists.
>         at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303)
>         at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(DataDictionaryImpl.java:1678)
>         at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1662)
>         at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addSPSParams(DataDictionaryImpl.java:3682)
>         at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.updateSPS(DataDictionaryImpl.java:3830)
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.updateSYSSTATEMENTS(SPSDescriptor.java:1112)
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.getPreparedStatement(SPSDescriptor.java:736)
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.getPreparedStatement(SPSDescriptor.java:642)
>         at org.apache.derby.impl.sql.compile.ExecSPSNode.generate(ExecSPSNode.java:177)
>         at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:447)
>         at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
>         at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:794)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java:128)
>         ... 99 more



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)