db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6890) INSERT error after PK altered to GENERATED
Date Sat, 04 Jun 2016 16:30:59 GMT

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

Bryan Pendleton commented on DERBY-6890:
----------------------------------------

I haven't really developed a good intuitive theory about this bug yet.

It seems that the problem lies with the Module_title index, for several reasons:
1) The proximate cause of the problem is that when we go to manufacture
   a "template" row for the Module_title index, the template is manufactured
   with a simple SQLVarchar datatype, rather than a CollatorSQLVarchar datatype.
2) If we drop and recreate the Module_title index, the problem goes away

And surely the problem must be with one of the alteration statements that
are issued (ALTER TABLE, RENAME COLUMN).

But none of those alteration statements are performed against the Module_title
index, so I don't have a clear idea about how this (unrelated?) secondary
index is getting damaged by the alter statements that should only be affecting
(a) the base table, and (b) the system-generated primary key constraint index.

And another big problem with my intuitive grasp of this bug is that the collation
and territory information for a database is supposed to be database-wide, not
table-by-table and index-by-index, so I don't understand how we could get
into a situation where some of the tables and indexes in the database think
they have one sort of collation/territory configuration, while other tables/indexes
think they have a different configuration.

Perhaps the next step is to back WAY up, and study what happens with the
'collation=' attribute on the connection URL at database creation time.


> INSERT error after PK altered to GENERATED
> ------------------------------------------
>
>                 Key: DERBY-6890
>                 URL: https://issues.apache.org/jira/browse/DERBY-6890
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.12.1.1
>         Environment: Mac OS X 10.11.5
> JDK: Oracle 1.8.0_92
>            Reporter: Andrei Koiro
>         Attachments: CollationTest.diff, Test.groovy, Test.java, testRepro.diff
>
>
> After issue https://issues.apache.org/jira/browse/DERBY-3888 was fixed, we want to use
the 'GENERATED BY DEFAULT' feature 
> for our tables.  
> To migrate our tables, we use this sql: 
>      ALTER TABLE MODULE ADD COLUMN ID_TEMP BIGINT GENERATED BY DEFAULT AS IDENTITY;
>      UPDATE MODULE SET ID_TEMP = ID;
>      ALTER TABLE MODULE ALTER COLUMN ID_TEMP NOT NULL;
>      ALTER TABLE MODULE DROP ID;
>      RENAME COLUMN MODULE.ID_TEMP TO ID;
> But after I applied it, I started to get this exception:
> Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED type of
inserted column[0] = org.apache.derby.iapi.types.CollatorSQLVarchartype of template column[0]
= org.apache.derby.iapi.types.SQLVarchar
> 	at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
> 	at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
> 	at org.apache.derby.impl.store.access.btree.OpenBTree.isIndexableRowConsistent(OpenBTree.java:519)
> 	at org.apache.derby.impl.store.access.btree.BTreeController.doIns(BTreeController.java:679)
> 	at org.apache.derby.impl.store.access.btree.BTreeController.insert(BTreeController.java:1372)
> 	at org.apache.derby.impl.store.access.btree.index.B2IController.insert(B2IController.java:210)
> 	at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(IndexChanger.java:565)
> 	at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(IndexChanger.java:393)
> 	at org.apache.derby.impl.sql.execute.IndexChanger.insert(IndexChanger.java:713)
> 	at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(IndexSetChanger.java:268)
> 	at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:458)
> 	at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:881)
> 	at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:452)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:473)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:352)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1340)
> 	... 30 more
> I attached Test.groovy class which shows this issue. 
> also I found this workaround: 
> we need to drop all indexes and create them again, after we applied this pk column update.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message