5.3.4 Update: Tables not up-to-date - Upgrade failed

I updated to 5.3.4 today, and when I re-launched IJC, I was presented with a dialog box informing me that my tables were out-of-date and needed to be updated during schema initialization.

When I tried to upgrade the tables, I get the following error:

Upgrade failed: ORA-00972: identifier is too long

The Details from the error are as follows:

java.sql.SQLException: ORA-00972: identifier is too long

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)

at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)

at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)

at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1706)

at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1674)

at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:275)

at chemaxon.jchem.db.TableInfo.executeSql(TableInfo.java:800)

at chemaxon.jchem.db.TableInfo.createLogTable(TableInfo.java:1041)

at chemaxon.jchem.db.Updater.createCacheTables(Updater.java:830)

at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:181)

at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:168)

at com.im.commons.db.JChemTableUpdater.performNextAction(JChemTableUpdater.java:103)

at com.im.df.impl.db.sprovider.UpgradeJChemIterator$UpgradeAction.performNextAction(UpgradeJChemIterator.java:130)

at com.im.df.impl.db.sprovider.UpgradeJChemTablesState.proceedToNextState(UpgradeJChemTablesState.java:72)

at com.im.df.impl.db.sprovider.SchemaProviderImpl.proceedToNextState(SchemaProviderImpl.java:117)

at com.im.ijc.core.api.util.SchemaInitManager.startInit(SchemaInitManager.java:213)

at com.im.ijc.core.api.util.SchemaInitManager.access$000(SchemaInitManager.java:53)

at com.im.ijc.core.api.util.SchemaInitManager$1.run(SchemaInitManager.java:77)

at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:577)

[catch] at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1030)

This error indicates the upgrade process is trying to create a new object (they are called objects in oracle also) identifier which is too long in oracle (limit 30)

I would imagine it should be possible to fix this soon.

Daniel.

Hi,

In JChem 5.3.2 we have introduced a new cache registration system that uses new index identifiers and it seems that one of these identifiers is too long. These identifiers are 6 characters longer than your structure table name.

As a workaround:

1) try to use your previous IJC version
2) find your table names that are longer than 24 characters
3) export data from these tables then delete the tables
4) create new tables with names of 24 characters at most
5) import your data into the new tables

Please notify us if you have problems with this workaround.

Rgeards,
Tamás

Tamas:

Thank you for the reply.  We have shortened the name of our tables, and we are now able to access our data.  We had to shorten some of them to much less than 24 charachters, however.

As a request for the future, I would like to see these changes incorporated into the upgrade procedure.  

That is, if the table names need to be shortened, I would like to be told so in plain language during the upgrade process.  Ideally, I'd like the upgrade process to include a prompt that tells me what the offending problem is, and asks me to make changes then and there without leaving the update process. This way it would be impossible to enter a situation where we upgrade to a version of IJC without a concomitant upgrade to the table structure.

That sounds strange. Do you use schemas? What is the name and type of the table? Do you have any specific settings on the table? Could you send the stack trace?

Regarding the upgrade process I absolutely agree with you. We will consider identifying possible problems during upgrade and change the process.

Regards,
Tamás

To conlcude this topic, before upgrading to IJC 5.3.4 please check that none of your JChem table names are greater than 24 characters in length. If they are the upgrade process will not complete successfully.

If you do have names longer than 24 characters you have these options:

1. Choose not to use IJC 5.3.4. The problem will be fixed in the next IJC version.

2. Use your current IJC version to change the tables so that they are not longer than 24 characters. You can do this by exporting the data to a file and then importing using a shorter table name. Then you can update to IJC 5.3.4.

3. Wait for a hot fix for this problem that we are preparing. This will allow IJC 5.3.4 to upgrade the tables correctly. This fix should be ready in the next few days and we will announce it here.

You can rename all your tables using a single dynamic SQL statement but you should take care to make sure the results are acceptable before commiting.

If you combine an SQL from your users table list with other oracle statement :

alter table    table_name rename to new_table_name;

You can also use the SQL length function to ensure less than 24 chars long.

If anyone needs specific help with this please say and I can provide a working example.

You will also need to consider the names used in the JCHEMPROPERTIES table and so again should state this approach should be approached with CAUTION.

Renaming tables directly in the database will also result in problems in IJC, so we do definitely not recommend this approach.

Tim