Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For registration and login issues contact website support.
5.3.4 Update: Tables not up-to-date - Upgrade failed
To watch this topic for replies  Register (enables digests) or give email address:
Reply to topic
Display posts from previous:   
    View previous topic :: View next topic    
Author Message
BME

Joined: 23 Apr 2009
Posts: 12

View user's profile

Back to top
Link to postPosted: Tue Jun 22, 2010 9:56 pmPost subject: 5.3.4 Update: Tables not up-to-date - Upgrade failed Reply with quote

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

 

 

Any ideas?  We are currently dead in the water, as we cannot access our data.

 

 

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)

 

DanielSButler
ChemAxon personnel
Joined: 22 Oct 2004
Posts: 495

View user's profile

Back to top
Link to postPosted: Tue Jun 22, 2010 10:42 pmPost subject: Reply with quote

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.

 

cheese
ChemAxon personnel
Joined: 03 Jan 2007
Posts: 119

View user's profile
Visit poster's website

Back to top
Link to postPosted: Wed Jun 23, 2010 2:07 pmPost subject: Reply with quote

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

BME

Joined: 23 Apr 2009
Posts: 12

View user's profile

Back to top
Link to postPosted: Wed Jun 23, 2010 4:13 pmPost subject: Reply with quote

cheese wrote:

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.

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.

cheese
ChemAxon personnel
Joined: 03 Jan 2007
Posts: 119

View user's profile
Visit poster's website

Back to top
Link to postPosted: Thu Jun 24, 2010 12:51 pmPost subject: Reply with quote

BME wrote:

 We had to shorten some of them to much less than 24 charachters, however.

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

tdudgeon
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1673

View user's profile
Visit poster's website

Back to top
Link to postPosted: Mon Jun 28, 2010 10:47 amPost subject: Reply with quote

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.

DanielSButler
ChemAxon personnel
Joined: 22 Oct 2004
Posts: 495

View user's profile

Back to top
Link to postPosted: Mon Jun 28, 2010 1:02 pmPost subject: Reply with quote

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.

DanielSButler
ChemAxon personnel
Joined: 22 Oct 2004
Posts: 495

View user's profile

Back to top
Link to postPosted: Mon Jun 28, 2010 1:03 pmPost subject: Reply with quote

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.

tdudgeon
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1673

View user's profile
Visit poster's website

Back to top
Link to postPosted: Mon Jun 28, 2010 1:20 pmPost subject: Reply with quote

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

Tim

Reply to topic
Page 1 of 1


To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum