db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4677) SYSCS_COMPRESS_TABLE disables unique constraints
Date Thu, 03 Jun 2010 22:03:55 GMT

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

Mike Matrigali updated DERBY-4677:
----------------------------------

    Affects Version/s: 10.5.2.0
                       10.4.2.0
                       10.4.1.3
          Component/s: SQL

What happened was that store basically was instructed to build a non-unique index, and it
did so.  I agree with the proposed fix, and will review when a patch is submitted.

Note that this fix only will correct the issue for future compress calls.  Whatever has happened
in the past will remain broken, until the user takes
some action.  It will be hard to determine if the broken index exists.  One
can run a query to see if a candidate index exists (basically is there a unique
index that contains nullable columns), but I can't think of a generic way to 
tell if compress has been run and broken the index.

I believe if the user has not inserted any duplicates, than another call to offline compress
using a version with the bug will fix the problem.

If the user has inserted duplicate data into the index after compress broke
the index, then automatic cleanup is not going to work.  Once a fix has
been proposed we should verify the error that arises in this case.  And also verify that in
this case the compress gets at least a statement level error backing out whatever the intermediate
work that was done before failing.  Maybe 
we can even catch the exception at appropriate places in the code and
raise a new error giving instructions on what needs to be done.

> SYSCS_COMPRESS_TABLE disables unique constraints
> ------------------------------------------------
>
>                 Key: DERBY-4677
>                 URL: https://issues.apache.org/jira/browse/DERBY-4677
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3, 10.4.2.0, 10.4.2.1, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
>         Environment: Output of sysinfo:
> ------------------ Java Information ------------------
> Java Version:    1.6.0_20
> Java Vendor:     Sun Microsystems Inc.
> Java home:       C:\Program Files (x86)\Java\jre6
> Java classpath:  .;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derby.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbynet.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbyclient.jar;C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin\../lib/derbytools.jar
> OS name:         Windows 7
> OS architecture: x86
> OS version:      6.1
> Java user name:  bmason
> Java user home:  C:\Users\BMASON
> Java user dir:   C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\bin
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> java.runtime.version: 1.6.0_20-b02
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derby.jar] 10.6.1.0 - (938214)
> [C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbytools.jar] 10.6.1.0 - (938214)
> [C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbynet.jar] 10.6.1.0 - (938214)
> [C:\Users\BMASON\Sandbox\libs\db-derby-10.6.1.0-bin\lib\derbyclient.jar] 10.6.1.0 - (938214)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Current Locale :  [English/New Zealand [en_NZ]]
> Found support for locale: [cs]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [de_DE]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [es]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [fr]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [hu]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [it]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [ja_JP]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [ko_KR]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [pl]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [pt_BR]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [ru]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [zh_CN]
> 	 version: 10.6.1.0 - (938214)
> Found support for locale: [zh_TW]
> 	 version: 10.6.1.0 - (938214)
> ------------------------------------------------------
>            Reporter: Brett Mason
>            Assignee: Mamta A. Satoor
>
> It appears that running SYSCS_UTIL.SYSCS_COMPRESS_TABLE on a table with a null-able unique
constraint will disable the unique constraint. The script
> below should reproduce the problem. The expected behaviour is for the second insert to
fail due to the unique constraint but instead it is allowed. The second insert will fail as
expected if either the call to SYSCS_COMPRESS_TABLE is skipped or if the column is declared
NOT NULL.
> I have reproduced the problem using embedded Derby 10.5.1.1, 10.5.3.0 and 10.6.1.0 using
ij.
> CREATE TABLE TABLE1(NAME1 INT UNIQUE);
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1);
> INSERT INTO TABLE1(NAME1) VALUES(1);
> INSERT INTO TABLE1(NAME1) VALUES(1);
> SELECT * FROM TABLE1;
> DROP TABLE TABLE1;

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message