Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Fri, 20 Apr 2007 18:11:36 -0400
From: "Thomas J. Taylor" <thomas.taylor@itqa.miami.edu>
Subject: Missing Keys after 10.2 Upgrade
To: 'Derby Discussion' <derby-user@db.apache.org>
Message-id: <005701c78398$dd3193f0$3f00000a@itsdev.infotechsoft.com>
MIME-version: 1.0
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Thread-index: AceDmNyhQ1Hxl0HMSpaoZ5308nG/Kg==

Hi Everyone,

I have a database that was originally created with Derby 10.1.1.0 and was
recently upgraded to Derby 10.2.2.0 using the 'upgrade=true' parameter to
the URL. I've performed this upgrade on several copies of the same database
schema (each created on different computers, but with the same version of
Java (1.5.0_07) and Derby (10.1)). 

For all but one of the database upgrades, it worked correctly. However, in
one case, it appears that the PRIMARY KEY and FOREIGN KEY constraints have
been lost/corrupted. When I use DBLook to check a 'working' database, I see
the appropriate constraints for keys. However, on the 'defective' database,
these constraints are missing.

When I run a query that uses one of these keys, I get an SQLException -
"CONSTRAINT on TABLE 'X' does not exist."
When I run SYSCS_UTIL.SYSCS_CHECK_TABLE, I get - "CONSTRAINT for INDEX
'SQL050809020545200' does not exist."

In SQuirreL, I see that index 'SQL050809020545200' is specified on the
table, but in DBLook, it doesn't show the constraint.

If I DROP INDEX 'SQL050809020545200' and re-run the query/CHECK_TABLE, it
works.

In order to fix the 'defective' database, it appears that I must DROP all of
the missing/defective indices, then re-create them. The challenges are that:
 (1) there are around 30 indices to re-create
 (2) the index names were autogenerated by Derby (i.e., I don't know their
names)

Is there an easy way to drop all constraints on all tables? So I can
re-create the constraints using the appropriate DDL.

Is there a better way to handle this issue?

Thanks for your help and suggestions.

Thomas


-- DBLook on 'working' database
-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------
CREATE TABLE "APP"."DEVICEINFO" ("DEVICEID" INTEGER NOT NULL, "DEVICENAME"
VARCHAR(50) NOT NULL, "DEVICETYPE" INTEGER);
-- ----------------------------------------------
-- DDL Statements for indexes
-- ----------------------------------------------
CREATE INDEX "APP"."DEVICENAME" ON "APP"."DEVICEINFO" ("DEVICENAME");
-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------
-- primary/unique
ALTER TABLE "APP"."DEVICEINFO" ADD CONSTRAINT "SQL060110023932820" PRIMARY
KEY ("DEVICEID");
-- foreign
ALTER TABLE "APP"."DEVICEINFO" ADD CONSTRAINT "SQL060110023932821" FOREIGN
KEY ("DEVICETYPE") REFERENCES "APP"."DEVICETYPE" ("TYPEID") ON DELETE NO
ACTION ON UPDATE NO ACTION;

-- Sample Queries against working database

ij>VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'DEVICEINFO');

RESULT
1

-- Defective Database

-- DBLook on 'defective' database
-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------
CREATE TABLE "APP"."DEVICEINFO" ("DEVICEID" INTEGER NOT NULL, "DEVICENAME"
VARCHAR(50) NOT NULL, "DEVICETYPE" INTEGER, "DEVICELOCATION" VARCHAR(50) NOT
NULL, "MACADDRESS" VARCHAR(50), "IPADDRESS" VARCHAR(50), "KEYSTOREM"
CLOB(1048576), "KEYSTORET" CLOB(1048576));
-- ----------------------------------------------
-- DDL Statements for indexes
-- ----------------------------------------------
CREATE INDEX "APP"."DEVICENAME" ON "APP"."DEVICEINFO" ("DEVICENAME");

-- Sample Queries against defective database

ij>SELECT DeviceID, DeviceName, DeviceType FROM DeviceInfo WHERE DeviceID=1;

Query  elapsed time (seconds) - Total: 0.12, SQL query: 0.04, Building
output: 0.08
Error: java.sql.SQLException: CONSTRAINT on TABLE 'DEVICEINFO' does not
exist.


ij>VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'DEVICEINFO')
java.sql.SQLException: CONSTRAINT for INDEX 'SQL050809020545200' does not
exist.
Error:	 ResultSet not open. Operation 'getWarnings' not permitted. Verify
that autocommit is OFF.
SQLState:  XCL16
ErrorCode: 20000
Query  elapsed time (seconds) - Total: 0.01, SQL query: 0.01, Building
output: 0