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] [Updated] (DERBY-6890) ALTER TABLE DROP COLUMN corrupts secondary index collation information
Date Sun, 05 Jun 2016 19:20:59 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6890?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Bryan Pendleton updated DERBY-6890:
-----------------------------------
       Assignee: Bryan Pendleton
    Description: 
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.







  was:
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.







        Summary: ALTER TABLE DROP COLUMN corrupts secondary index collation information  (was:
INSERT error after PK altered to GENERATED)

Updated issue title and description.

> 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, 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