EJBCA Upgrade from 6.5.x to 7.4.x fail - Fail to drop indexes on Postgres

[gardellajuanpablo]

I am testing upgrade and there is an issue during the upgrade. Reproduced using docker image version 7.4.3.2.

2022-01-18 10:20:38,163+0000 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (ServerService Thread Pool -- 49) SQL Error: 0, SQLState: 42601
2022-01-18 10:20:38,163+0000 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (ServerService Thread Pool -- 49) ERROR: syntax error at or near "ON"
  Position: 35
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49) An error occurred when adjusting indexes for database table 'CRLData': javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49) You can update the indexes manually by running the following SQL queries:
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49)     DROP INDEX IF EXISTS crldata_idx3 ON CRLData;
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49)     DROP INDEX IF EXISTS crldata_idx4 ON CRLData;
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49)     CREATE INDEX IF NOT EXISTS crldata_idx5 ON CRLData(cRLNumber, issuerDN, crlPartitionIndex);
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49)     CREATE UNIQUE INDEX IF NOT EXISTS crldata_idx6 ON CRLData(issuerDN, crlPartitionIndex, deltaCRLIndicator, cRLNumber);
2022-01-18 10:20:38,170+0000 ERROR [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49) These changes are only needed if you want to use 'Partitioned CRLs'. See ECA-8680.
2022-01-18 10:20:38,170+0000 INFO  [org.ejbca.core.ejb.upgrade.UpgradeSessionBean] (ServerService Thread Pool -- 49) If an index could not be created because duplicates were found you could remove them using something like:
    DELETE t1 FROM CRLData t1, CRLData t2 WHERE t1.fingerprint > t2.fingerprint AND t1.issuerDN = t2.issuerDN 
AND t1.deltaCRLIndicator = t2.deltaCRLIndicator AND t1.cRLNumber = t2.cRLNumber AND t1.crlPartitionIndex = t2.crlPartitionIndex;
``

[Droping index](https://www.postgresql.org/docs/14/sql-dropindex.html) in Postgres does not require define table. Using name is enough.

[primetomas]

So you can drop those indexes in your database manually if you like. As it says, it's not critical and only if you want to use partitioned CRLs.

[gardellajuanpablo]

Yes I know, I will try to provide a PR, although the workaround is very simple. Thanks!

[primetomas]

Thanks. Closing this, looking forward to a PR.

[benoiton]

PostgreSQL logs shows:

2023-09-07 09:03:48.196 EDT [16141] ERROR:  syntax error at or near "ON" at character 25
2023-09-07 09:03:48.196 EDT [16141] STATEMENT:  DROP INDEX crldata_idx3 ON CRLData
2023-09-07 09:03:48.198 EDT [16141] ERROR:  syntax error at or near "ON" at character 25
2023-09-07 09:03:48.198 EDT [16141] STATEMENT:  DROP INDEX crldata_idx4 ON CRLData

This syntax is wrong for PostgreSQL, there is no "ON tablename". But it does exist with MariaDB and MSSQL.

Prepared statement is in "modules/ejbca-ejb/src/org/ejbca/core/ejb/upgrade/UpgradeSessionBean.java", line 2228.

final Query dropCrlDataIndex = entityManager.createNativeQuery("DROP INDEX " + oldIndexName + " ON " + tableName);

[benoiton]

SQL script "doc/sql-scripts/remove-index-ejbca.sql" entirely fails with PostgreSQL.

[primetomas]

Can you create a new issue for that, this one is closed and is not about that remove index script. Unfortunately databases have not unified SQL, so some things need to be modified for different databases. If you open a new issue, we could at least document what you would change for postgres.

[benoiton]

I created #350 for the SQL script. I can create another issue for UpgradeSessionBean.java if you want.

[primetomas]

Absolutely, please create another issue for UpgradeSessionBean

[benoiton]

Issue #415 created for UpgradeSessionBean.