You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Chad Loder (JIRA)" <de...@db.apache.org> on 2006/09/15 02:06:23 UTC
[jira] Created: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
------------------------------------------------------------------------------------------------------
Key: DERBY-1854
URL: http://issues.apache.org/jira/browse/DERBY-1854
Project: Derby
Issue Type: Bug
Components: Store
Affects Versions: 10.1.3.1, 10.1.3.0
Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
Reporter: Chad Loder
Priority: Critical
Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
connect 'jdbc:derby:/testdb;create=true';
CREATE TABLE users (
user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
user_login VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id));
CREATE TABLE admins (
user_id INT NOT NULL,
PRIMARY KEY (user_id),
CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
INSERT INTO users (user_login) VALUES('TEST1');
INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts
table with a single column which is both a primary key and a foreign key
Posted by Mike Matrigali <mi...@sbcglobal.net>.
There are a number of code paths in the server that use the
"bait/switch" method. Off the top of my head: some alter table
modify column paths, maybe drop column, bulk load to an empty table.
Were these also broken for this problem? Is the code to do the
syscat update shared, and thus this fixes all of them? Probably
worth adding some test cases.
Mamta A. Satoor (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436636 ]
>
> Mamta A. Satoor commented on DERBY-1854:
> ----------------------------------------
>
> I did a quick review of the patch and the changes look good to me. DERBY-655 made changes such that duplicate indexes will have their own unique conglomerateIDs but there were other places in the code which relied on duplicate conglomerateIDs for duplicate indexes. I give a +1 to this patch.
>
>
>>SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
>>------------------------------------------------------------------------------------------------------
>>
>> Key: DERBY-1854
>> URL: http://issues.apache.org/jira/browse/DERBY-1854
>> Project: Derby
>> Issue Type: Bug
>> Components: Store
>> Affects Versions: 10.1.3.0, 10.1.3.1
>> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
>> Reporter: Chad Loder
>> Assigned To: Suresh Thalamati
>> Priority: Critical
>> Fix For: 10.2.1.0, 10.3.0.0
>>
>> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff
>>
>>
>>Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
>>connect 'jdbc:derby:/testdb;create=true';
>>CREATE TABLE users (
>> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
>> user_login VARCHAR(255) NOT NULL,
>> PRIMARY KEY (user_id));
>>CREATE TABLE admins (
>> user_id INT NOT NULL,
>> PRIMARY KEY (user_id),
>> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>>
>>INSERT INTO users (user_login) VALUES('TEST1');
>>INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
>>CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
>>SELECT * from admins;
>
>
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436636 ]
Mamta A. Satoor commented on DERBY-1854:
----------------------------------------
I did a quick review of the patch and the changes look good to me. DERBY-655 made changes such that duplicate indexes will have their own unique conglomerateIDs but there were other places in the code which relied on duplicate conglomerateIDs for duplicate indexes. I give a +1 to this patch.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436704 ]
Suresh Thalamati commented on DERBY-1854:
-----------------------------------------
Committed to trunk on revision 448758. Thanks for taking time to review the patch , Andrew, Mamta.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Mike Matrigali updated DERBY-1854:
----------------------------------
Component/s: SQL
(was: Store)
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Rick Hillegas updated DERBY-1854:
---------------------------------
Fix Version/s: 10.2.1.0
(was: 10.2.2.0)
Assigning this to 10.2.1 since it has been identified as a regression.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Andrew McIntyre updated DERBY-1854:
-----------------------------------
Fix Version/s: 10.3.0.0
Altering Fix In for this issue, a fix should go in each branch where DERBY-655 was checked in, marking trunk and 10.2 for now, but this should eventually go into the 10.1 and 10.0 branches as well.
Also, after applying the patch I am not able to reproduce DERBY-1854 OR DERBY-1641, and the provided testcase, which mirrors the repro found in the summary, passes with the patch and fails without.
+1 to commit to trunk and 10.2. There were some non-trivial errors to resolve applying the patch to 10.1 and 10.0.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Suresh Thalamati reassigned DERBY-1854:
---------------------------------------
Assignee: Suresh Thalamati
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.2.0
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Suresh Thalamati updated DERBY-1854:
------------------------------------
Attachment: derby-1854.diff
Problem was all the conglomerate descriptor entries in sys.sysconglomerates
were not getting updated with new conglomerate number generated for an index
during compress/bulk-insert, when an index is shared. Update code was assuming conglomerate id is common when an index is shared, but that is not correct. ConglomerateID's in sys.sysconglomerates are unique.
This patch modifies the update conglomerate descriptor code to update each
conglomerate descriptor separately using conglomerateID as the key, when
there are more than one conglomerate descriptor referring to the same
conglomerate(conglomerate number).
It would be great, if some can review the attached patch.
svn stat:
M java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java
M java\testing\org\apache\derbyTesting\functionTests\tests\lang\compressTable.sql
M java\testing\org\apache\derbyTesting\functionTests\master\compressTable.out
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.2.0
>
> Attachments: derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Andrew McIntyre closed DERBY-1854.
----------------------------------
Merged to 10.0, merged cleanly for the engine code, test needed to be edited slightly due to no identity columns in 10.0. Closing.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.5, 10.3.0.0, 10.1.4.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Mike Matrigali updated DERBY-1854:
----------------------------------
Fix Version/s: 10.2.1.0
This one seems pretty serious since it looks like you basically lose your table if you do it, making it a candidate for 10.2. I don't think it is a regression. I have not had time to look at it. If I had to guess it has the feel of a problem
with 2 indexes defined on the same column where the catalogs fudge it and make it look like 2 different indexes but in reality there is only one. And then when compress table does a "bait and switch" trying to replace all tables and indexes it gets confused. My guess is that this is a problem at the upper layer and not store.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Priority: Critical
> Fix For: 10.2.1.0
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Chad Loder (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12434856 ]
Chad Loder commented on DERBY-1854:
-----------------------------------
This may be similar to http://issues.apache.org/jira/browse/DERBY-1641, which is still open.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Priority: Critical
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Chad Loder (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436741 ]
Chad Loder commented on DERBY-1854:
-----------------------------------
Hm, that last comment didn't format correctly.
We locally applied "derby-1854-andrew-10.1.diff" against our 10.1 working copy and it worked OK. Cheers
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Suresh Thalamati updated DERBY-1854:
------------------------------------
Attachment: derby-1854_v1.diff
This patch has same fix as in deryby-1854.diff , Only thing new in this patch is the updates to the importExportThruIJ.sql to test import nto a table that has same column as a primary key and a foreign key (ADMINS table).. import internally uses bulk-insert when the table is empty , it also drops and recreates the indexes, sys,sysconglomerates were not gettting updated correctly in this case also because , bul-insert calls the same update conglomerate descriptors method in the data dictionary.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Andrew McIntyre updated DERBY-1854:
-----------------------------------
Attachment: derby-1854-andrew-10.1.diff
Attaching version of Suresh's patch which applies cleanly to 10.1, ran compressTable, it passes, haven't run other tests.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Chad Loder (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436574 ]
Chad Loder commented on DERBY-1854:
-----------------------------------
Andrew,
What kind of problems did you have applying the patch to 10.1?
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Mike Matrigali updated DERBY-1854:
----------------------------------
Fix Version/s: 10.1.4.0
backported change from trunk to the 10.1 branch :
------------------------------------------------------------------------
r449058 | mikem | 2006-09-22 12:17:47 -0700 (Fri, 22 Sep 2006) | 23 lines
DERBY-1854
contributed by suresht
merging change from trunk to 10.1 codeline.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.5, 10.3.0.0, 10.1.4.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436361 ]
Suresh Thalamati commented on DERBY-1854:
-----------------------------------------
debugged this bug little bit , looks like the problem is one of the conglomerate descriptor entries in the sys.sysconglomerates are not getting updated with the new conglomerate number that got created as part of the compress . In the test case , it happens to be entry for the foreign key index.
The current code that updates the conglomerate numbers indexes assumes ,there are duplicate entries in the sys.sysconglomerate with same conglomerid entries , when multiple indexes are referring to a conglomerate. I think this assumption is not true any more , fix for DERBY-655 ensures conglomerid is unique in sys.sysconglomerates.
This bug is likely to show up in all branches , where the fix for DERBY-655 is checked in.
/suresh
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Priority: Critical
> Fix For: 10.2.2.0
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Rick Hillegas updated DERBY-1854:
---------------------------------
Fix Version/s: 10.2.2.0
(was: 10.2.1.0)
Assigning to 10.2.2.0
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Priority: Critical
> Fix For: 10.2.2.0
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Chad Loder (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12435118 ]
Chad Loder commented on DERBY-1854:
-----------------------------------
Also reproducible in trunk (and thus 10.2)
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Priority: Critical
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts
table with a single column which is both a primary key and a foreign key
Posted by Mike Matrigali <mi...@sbcglobal.net>.
It would not be wrong to backport this fix to 10.0, I didn't have a 10.
0 client so didn't do it. At this point I hope most people would be
running 10.1.
Andrew McIntyre (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12438278 ]
>
> Andrew McIntyre commented on DERBY-1854:
> ----------------------------------------
>
> Should this also be ported to 10.0, since the fix for DERBY-655 which caused this regression was also ported there?
>
>
>>SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
>>------------------------------------------------------------------------------------------------------
>>
>> Key: DERBY-1854
>> URL: http://issues.apache.org/jira/browse/DERBY-1854
>> Project: Derby
>> Issue Type: Bug
>> Components: SQL
>> Affects Versions: 10.1.3.1, 10.1.3.0
>> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
>> Reporter: Chad Loder
>> Assigned To: Suresh Thalamati
>> Priority: Critical
>> Fix For: 10.2.1.5, 10.3.0.0, 10.1.4.0
>>
>> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>>
>>
>>Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
>>connect 'jdbc:derby:/testdb;create=true';
>>CREATE TABLE users (
>> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
>> user_login VARCHAR(255) NOT NULL,
>> PRIMARY KEY (user_id));
>>CREATE TABLE admins (
>> user_id INT NOT NULL,
>> PRIMARY KEY (user_id),
>> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>>
>>INSERT INTO users (user_login) VALUES('TEST1');
>>INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
>>CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
>>SELECT * from admins;
>
>
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12438278 ]
Andrew McIntyre commented on DERBY-1854:
----------------------------------------
Should this also be ported to 10.0, since the fix for DERBY-655 which caused this regression was also ported there?
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.5, 10.3.0.0, 10.1.4.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Suresh Thalamati resolved DERBY-1854.
-------------------------------------
Resolution: Fixed
committed to 10.2 as part of meg-merge , revision. 449013.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Chad Loder (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=comments#action_12436739 ]
Chad Loder commented on DERBY-1854:
-----------------------------------
We locally applied derby-1854-against the our 10.1 working copy and I have confirmed that it fixes the issue and also works fine with our large (>6 million rows) production databases in our full application environment. Thanks
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.0, 10.1.3.1
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.1.0, 10.3.0.0
>
> Attachments: derby-1854-andrew-10.1.diff, derby-1854.diff, derby-1854_v1.diff
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1854) SYSCS_COMPRESS_TABLE corrupts table
with a single column which is both a primary key and a foreign key
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1854?page=all ]
Andrew McIntyre updated DERBY-1854:
-----------------------------------
Derby Info: [Regression]
DERBY-655 has gone into all of the branches. I have verified that the problem does not occur before the fix for DERBY-655, and does occur after the fix for DERBY-655 in 10.0 and 10.1, so I am marking the regression flag.
> SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key
> ------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1854
> URL: http://issues.apache.org/jira/browse/DERBY-1854
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.1.3.1, 10.1.3.0
> Environment: Reproduced on Linux, Win2k, and WinXP running JDK 1.4.2.x
> Reporter: Chad Loder
> Assigned To: Suresh Thalamati
> Priority: Critical
> Fix For: 10.2.2.0
>
>
> Running the following short SQL script from ij will cause an error "ERROR XSAI2: The conglomerate (817) requested does not exist.". It appears that the SYSCS_COMPRESS_TABLE function corrupts tables that have a single column which is both a primary key and a foreign key.
> connect 'jdbc:derby:/testdb;create=true';
> CREATE TABLE users (
> user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
> user_login VARCHAR(255) NOT NULL,
> PRIMARY KEY (user_id));
> CREATE TABLE admins (
> user_id INT NOT NULL,
> PRIMARY KEY (user_id),
> CONSTRAINT admin_uid_fk FOREIGN KEY (user_id) REFERENCES users (user_id));
>
> INSERT INTO users (user_login) VALUES('TEST1');
> INSERT INTO admins VALUES (VALUES IDENTITY_VAL_LOCAL());
> CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ADMINS', 0);
> SELECT * from admins;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira