Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 486B5200B34 for ; Sat, 18 Jun 2016 05:28:08 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 46F50160A62; Sat, 18 Jun 2016 03:28:08 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 8F5EC160A61 for ; Sat, 18 Jun 2016 05:28:07 +0200 (CEST) Received: (qmail 36547 invoked by uid 500); 18 Jun 2016 03:28:05 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 36528 invoked by uid 99); 18 Jun 2016 03:28:05 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 18 Jun 2016 03:28:05 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 546B12C14F9 for ; Sat, 18 Jun 2016 03:28:05 +0000 (UTC) Date: Sat, 18 Jun 2016 03:28:05 +0000 (UTC) From: "Bryan Pendleton (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (DERBY-6890) ALTER TABLE DROP COLUMN corrupts secondary index collation information MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Sat, 18 Jun 2016 03:28:08 -0000 [ https://issues.apache.org/jira/browse/DERBY-6890?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bryan Pendleton updated DERBY-6890: ----------------------------------- Attachment: ready.diff Attached 'ready.diff' is the change I propose to commit. Tests are clean. > 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, CollationTest2.diff, Test.groovy, Test.java, doesntPassTests.diff, fixIndexCollation.diff, proposed.diff, ready.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)