db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kalyan Inuganti <kinuga...@gmail.com>
Subject Re: Derby 10.1 -> 10.2 upgrade issue
Date Thu, 05 Mar 2009 18:43:30 GMT
Hi Thomas,

I have been doing some research on how i can get the column name(s) that the
index corresponds to (see the 2nd *bold* section of the SQL) and haven't
found anything yet. Any ideas? I would also like to know if a given index is
a unique index or not? The reason I say this is because I looked at "Create
Index..." statements and there are 2 flavors - ones with the *Unique *qualifier
and the others without.

statement.executeUpdate(“CREATE *UNIQUE* INDEX “+indexNameString+” ON
DeviceInfo *(DeviceID)*”);

Thanks a lot for your help!
Kal

On Thu, Mar 5, 2009 at 11:15 AM, Thomas J. Taylor <
Thomas.Taylor@infotechsoft.com> wrote:

>  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#getIndexInfo(java.lang.String,
> java.lang.String, java.lang.String, boolean, boolean)<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html%23getIndexInfo%28java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean%29>
>
>      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/%3C462D5DF8.80101@gmail.com%3E
>
> Thanks,
> Kal
>

Mime
View raw message