Upgrade crash with ORA-00955

Hi,

running an upgrade from 5.3.0.1 to latest cartridge relase (15.2).  The upgrade process crashes when the cartridge start to upgrade the structure tables.  Ouput pasted below.

How to proceed?

Regards,

Christer

 

Running in a DBA account so I have the "CREATE TABLE" role.

 

 

====> Rebuilding indexes on regular structure tables...
INFO: Using chemaxon.jchem.cartridge.util.cpool.Oracle10gDataSource

====> Upgrading JChem structure tables...

====> Upgrading ACT_CHEMSPEC.JCMOL_MOLTABLE...
java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
    at chemaxon.jchem.db.TableInfo.createLogTable(TableInfo.java:1300)
    at chemaxon.jchem.base.storage.cache.impl.CacheUpdaterLogTable.createTableScript(CacheUpdaterLogTable.java:243)
    at chemaxon.jchem.base.storage.cache.impl.CacheUpdaterLogTable.reCreateTableScript(CacheUpdaterLogTable.java:257)
    at chemaxon.jchem.base.storage.cache.CacheUpdaterType.reCreateTableScript(CacheUpdaterType.java:194)
    at chemaxon.jchem.db.Updater.createCacheTables(Updater.java:1472)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:332)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:310)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgradeJcbTable(UpgradeCmdLine.java:755)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgradeJcbTables(UpgradeCmdLine.java:704)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.rebuildIndices(UpgradeCmdLine.java:606)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:321)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:73)
    at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:770)
+ set +x

I have also logged in with the jcman and get the same error when trying to "Procced" when the manager detects that tables have been created with an ancient cartridge:

 

Chem has detected that some tables were created with an older version of JChem, and cache tables should be created for them to be usable with this version.
Do you want JChem to create these cache tables now?
Before you select Proceed, please stop all JChem operations and searches. NOTE: You must have the CREATE TABLE right for this.
Object list:
<<long list of moltables>>

Tried to manually upgrade the tables but this failed

 

./jcman u ACT_CHEMSPEC.JCMOL_MOLTABLE




JChem has detected that some tables were created with an older version of JChem,
 and some columns should be added to be usable with this version.

Do you want JChem to upgrade these tables now?

This may take several minutes for large tables.
Before you select Proceed, please stop all other JChem applications.
NOTE: You must have the sufficient rights to ALTER these tables.
Perform operation  ([Y]es / [N]o / [L]ist objects) ? : L


ACT_CHEMSPEC.JCMOL_MOLTABLE
ACT_CHEMSPEC.JCMOL_MOLTABLE
ACT_CHEMSPEC.JCMOL_MOLTABLE


Perform operation  ([Y]es / [N]o / [L]ist objects) ? : Y

Altering tables

Columns have been added successfully to structure tables.




JChem has detected that some tables were created with an older version of JChem,
and may contain obsolete calculated values or Chemical Terms data that need
full recalculation.

Do you want JChem to recalculate these tables?

This may take some time, possibly hours, depending on the size of your database.
If you select Skip, search results will be inaccurate for these tables.

Perform operation  ([Y]es / [N]o / [L]ist objects) ? : Y

Recalculating structure tables

chemaxon.jchem.db.UpdateHandlerException: chemaxon.jchem.base.storage.PersistenceException: chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
    at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:1314)
    at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:1266)
    at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1617)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:345)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:310)
    at chemaxon.jchem.Command.updateTables(Command.java:878)
    at chemaxon.jchem.Command.run(Command.java:384)
    at chemaxon.jchem.Command.main(Command.java:275)
Caused by: chemaxon.jchem.base.storage.PersistenceException: chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
    at chemaxon.jchem.base.storage.impl.sql.SqlStructureCollectionImpl.recalculateCollection(SqlStructureCollectionImpl.java:908)
    at chemaxon.jchem.db.UpdateHandler.recalculateTable(UpdateHandler.java:1306)
    ... 7 more
Caused by: chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
    at chemaxon.jchem.db.Regenerator.init(Regenerator.java:406)
    at chemaxon.jchem.db.Regenerator.<init>(Regenerator.java:262)
    at chemaxon.jchem.base.storage.impl.sql.SqlStructureCollectionImpl.recalculateCollection(SqlStructureCollectionImpl.java:903)
    ... 8 more
chemaxon.jchem.base.storage.PersistenceException: chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
java.sql.SQLException: chemaxon.jchem.base.storage.PersistenceException: chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
chemaxon.jchem.db.RecalculationException: Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
Table 'ACT_CHEMSPEC.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
    at chemaxon.jchem.db.Updater.regenerateTables(Updater.java:1632)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:345)
    at chemaxon.jchem.db.Updater.performCurrentUpdate(Updater.java:310)
    at chemaxon.jchem.Command.updateTables(Command.java:878)
    at chemaxon.jchem.Command.run(Command.java:384)
    at chemaxon.jchem.Command.main(Command.java:275)

Hi Christer,

Thank you the reports.

On the basis of the stack trace you sent, we found a bug in the code of the upgrade process. We can provide the bugfix in our next week's release. In case you have no time to wait till the next release (about next Wednesday)  you could try to drop manually the sequences ending with "_USQ" before the upgrade.

Thank you for your patience.

Krisztina

Sorry but deleteing the sequences did not work.  Tested with two tables in different schemas where one had previously been attempted to manually upgrade with "jcman u" and the other not.  Same problem in both cases:

./jcman u BCPVS_TEST.JCMOL_MOLTABLE

...

Recalculating structure tables

chemaxon.jchem.db.UpdateHandlerException: chemaxon.jchem.base.storage.PersistenceException: chemaxon.jchem.db.RecalculationException: Table 'BCPVS_TEST.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.
Caused by:
chemaxon.jchem.db.RecalculationException: Table 'BCPVS_TEST.JCMOL_MOLTABLE' needs an important structural change in the database. Use 'jcman u' in command line, or JCman GUI to upgrade your JChem.

Thank you for the feedback. We investigate this issue further and will be back.

Krisztina

Please let me know if you need any further information or if I should perform any tests.

Hi Christer,

We are very sorry, but it seems hat the upgrade process of your system cannot be successfully rerun because the partly executed upgrade made some non-undoable changes compared to the original state. The main problem was that cache handling have been strongly modified between versions 5.3 and the latest  - especially in case of indexed JChem tables  -   and there is a bug relating sequence dropping present.

At the present situation, instead of the upgrade we recommend to create a new database schema with the fresh version of JChem Cartridge, create tables there (If it is not inevitably necessary to use JChem tables, we suggest to use standard Oracle tables to store the structure data), copy the content of the cd_structure column of your original JChem tables into the new tables, and create indexes with JChem Cartridge on these tables.

Best regards,

Krisztina

Oh, this is truely horrible news :(

kvajda wrote:

instead of the upgrade we recommend to create a new database schema with the fresh version of JChem Cartridge, create tables there (If it is not inevitably necessary to use JChem tables, we suggest to use standard Oracle tables to store the structure data), copy the content of the cd_structure column of your original JChem tables into the new tables, and create indexes with JChem Cartridge on these tables.

To move tables in the database is a huge task as we're not only talking several huge tables but also a lot of intricate oracle relationships between these tables and the rest of the database but also with external code.  Is there really no hope that the tables can be updated?   Running a lot SQL to "reset" the tables to proper structure is no problem - just give me some hints on what the structure should be.

As far as possible I would really want to avoid having to make major database changes.  Would it be possible to simply drop all jchem indexes, remove the table entries from jchem.jchemproperties and then remake the re-create indexes and perhaps manually add the entry in jchem.jchemproperties?

 

 

 

 

Were the unsuccessful upgrades run on a test system or on the live production system?

Could you send us the error stack trace produced when the upgrade was tried to run after dropping sequences with '_USQ' ending on a schema where there was no upgrade previously?.

Thank you

Production system.  Yeah, big FAIL.  

I've attached the output on a run where I've started by deleting the USQ sequence and then running the "jcman u"  on it.

 

This thread is continued in form of email support.