db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Missing Keys after 10.2 Upgrade
Date Tue, 24 Apr 2007 01:31:36 GMT
Hi -
Can you recover the old version DB from backup, verify that the KEYs do 
exist there and redo the upgrade?  Does this produce another 'defective' 
database?  If so, please open a JIRA issue on this and attach the 
database as a reproducible test case to investigate.

Are you getting errors trying to readd the constraints?  Derby has logic 
that prevents redundant physical indexes from being created on a table 
when a KEY is added to a table so you don't need to worry about 
duplicate index files.  Try running alter table add constraint statement 
on each of the tables and see what happens.  Check the count of the 
files in seg0 before and after this operation to determine if a physical 
index (new file) was created.  There will be two index entries in the 
data dictionary but they will point to the same physical file (the same 
conglomeratenumber: see entry in sysconglomerates).

HTH

Thomas J. Taylor wrote:
> 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
>
>
>
>
>   



Mime
View raw message