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) ALTER TABLE DROP COLUMN corrupts secondary index collation information
Date Fri, 10 Jun 2016 01:41:21 GMT

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

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

I spent some time looking through the test cases in CollationTest2.java. From what
I can tell, they are a pretty extensive set of test cases for this area of the code.

However, those test cases didn't reveal this bug.

I'm going to try to dig into the specific ALTER TABLE related test cases in CollationTest2
and try to understand why the existing test cases didn't trip this bug.

And then my hope is to extend and augment those test cases, rather than writing
a bunch of new test cases, because that seems a bit simpler overall.

I think that we want to cover:
- ALTER TABLE DROP COLUMN
- SYSCS_COMPRESS_TABLE
- TRUNCATE TABLE
other ALTER TABLE actions, such as ADD COLUMN, ALTER COLUMN, RENAME COLUMN
- different patterns of secondary indexes, both system-generated and user defined


> ALTER TABLE DROP COLUMN corrupts secondary index collation information
> ----------------------------------------------------------------------
>
>                 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
>            Assignee: Bryan Pendleton
>         Attachments: CollationTest.diff, Test.groovy, Test.java, doesntPassTests.diff,
fixIndexCollation.diff, proposed.diff, testRepro.diff
>
>
> For a database with "collation=/territory=" information configured via
> the JDBC Connection URL at database connection time, individual
> columns in tables and indexes in the database have collation identification
> which is stored as part of the table/index conglomerate.
> When an ALTER TABLE DROP COLUMN statement is run against
> such a database, the drop column processing performs logic which
> re-builds all of the (remaining) secondary indexes for that table
> to reflect their new state following the removal of that column.
> This index rebuild process does not properly re-configure the
> collation information for column(s) in those index(es), leaving
> the indexes in a corrupt state.
> As a workaround, following the ALTER TABLE DROP COLUMN, the
> damaged secondary indexes can be dropped and recreated explicitly.
> == Original issue description below ==
> 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