db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor" <Thomas.Tay...@INFOTECHSoft.com>
Subject RE: Derby 10.1 -> 10.2 upgrade issue
Date Thu, 05 Mar 2009 17:15:03 GMT
Hi Kal,

 

I'll check to see if I can find the code/process that I used back then to
solve the issue. Since I only had one (remote) Derby installation causing
problems, once I figured out the way to resolve the problem (drop & recreate
index), I probably (1) used DBLook to identify the corrupt (missing)
indexes, then used SQurilleL to (2) identify the names of the keys through
the GUI, (3) write the DDL to drop and re-create the indexes.

 

You should be able to use JDBC to get the same index information and
drop/create the index that way; however, the challenge is identifying the
corrupt indices. Perhaps this might work?

 

Connection connection; // existing db connection

Statement statement = connection.createStatement();  

try {

     // test table to confirm corrupt index: SQLException is thrown if
corrupt

statement.executeQuery("SELECT DeviceID, DeviceName, DeviceType FROM
DeviceInfo WHERE DeviceID=1");

} catch (SQLException ex) {

     // retrieve index information for the corrupt table

//
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIn
dexInfo(java.lang.String,
<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html%23ge
tIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boole
an,%20boolean)>  java.lang.String, java.lang.String, boolean, boolean)

     DatabaseMetaData databaseMetaData = conn.getMetaData();

     ResultSet resultSet = databaseMetaData.getIndexInfo(null, null,
"DeviceInfo", false, false);

     // for each index, drop & recreate the index

     while (resultSet.hasNext()) {

           // get the name of the 

           String indexNameString = resultSet.getString("INDEX_NAME");

           statement.executeUpdate("DROP INDEX "+indexNameString+" ON
DeviceInfo);

// recreate index:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj20937.html

           statement.executeUpdate("CREATE UNIQUE INDEX "+indexNameString+"
ON DeviceInfo (DeviceID)");

}

}

 

Thomas Taylor

INFOTECH Soft, Inc.

 

From: Kalyan Inuganti [mailto:kinuganti@gmail.com] 
Sent: Thursday, March 05, 2009 11:10 AM
To: derby-user@db.apache.org
Subject: Derby 10.1 -> 10.2 upgrade issue

 

Hi,

I am reaching out to you guys for some help with a Derby indexing issue that
we have run into at Monsanto, St. Louis. The issue is pretty much the same
issue that was reported by Thomas J. Taylor in 2007 (The link is provided
below). 

Brief Description:

I have a database that was originally created with Derby 10.1.1.0 and was


recently upgraded to Derby 10.2.2.0. 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.


We have over 80 tables in the DB and over 1000 users. Even though we have
only 2 reported occurrences of this issue so far, it might be more
widespread. It is a nightmare to manually identify the corrupted indexes for
each occurrence. Any thoughts on how we can tackle this through a
programmatic approach?

Here is the link to the old report:

http://mail-archives.apache.org/mod_mbox/db-derby-user/200704.mbox/%3C462D5D
F8.80101@gmail.com%3E

Thanks,
Kal   


Mime
View raw message