Database Management

During an upgrade to DPA 2024.4 or 2024.4.100, the repository database upgrade fails with a Bad SqlGrammar Exception

Upgrading to DPA 2024.4 or 2024.4.100 fails if the DPA repository database is case-sensitive.

First published date

1/15/2025 4:36 PM

Last published date

2/12/2025 10:30 PM

Overview

During an upgrade to DPA 2024.4 or 2024.4.100, the repository database upgrade fails with a message such as the following: 

The Repository Database upgrade failed.

org.springframework.jdbc.Bad SqlGrammar Exception: StatementCallback; bad SQL grammar [UPDATE COND SET DB_TYPE = "MariaDB', MONDBVERSION - RTRIM(LEFT(MONDBVERSION, CHARINDEX((MariaDB), MONDBVERSION)-1)) WHERE jdbc_driver = 'org.mariadb.jdbc.Driver' AND DB_TYPE='MySQL' AND MONDBVERSION like % (MariaDB)]; nested exception is java.sql.SQLException: Invalid column name 'jdbc_driver'. at org.springframework.jdbc.support.SQLErrorCode SQLException Translator.do Translate(SQLErrorCode SQLException Translator.java:239) at org.springframework.jdbc.support.AbstractFallback SQLException Translator.translate(AbstractFallback SQLException Translator.java:70) at
org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1583) at org.springframework.jdbc.core.Jdbc Template.execute(Jdbc Template.java:398) at org.springframework.jdbc.core.JdbcTemplate.update(Jdbc Template.java:563) at SC.eval0(SC.java:1) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at 

repo-upgrade-failed.png

Product section

Database Performance Analyzer

Cause

A script that is part of the repository database upgrade has the column name jdbc_driver in lowercase letters, which does not match the capitalization of the column name in the repository. Therefore, in case-sensitive databases, this column is not found. 

SQL Server databases are case-sensitive by default. Oracle and MySQL databases can also be case-sensitive.

Resolution

Note: Edit the schema name to dbo or ignite in the following queries as needed.

  1. If you receive the error shown above, stop the DPA server.

  2. In the DPA repository database, run the following SQL statements in order:

    • For a SQL Server repository database:

      1. Run query 1:

        UPDATE ignite.COND SET DB_TYPE = 'MariaDB', MONDBVERSION = RTRIM(LEFT(MONDBVERSION, CHARINDEX('(MariaDB)', MONDBVERSION)-1)) WHERE JDBC_DRIVER = 'org.mariadb.jdbc.Driver' AND DB_TYPE = 'MySQL' AND MONDBVERSION like '%(MariaDB)';

      2. Run query 2:

        UPDATE ignite.CONLIC_INSTANCE_ALLOCATION SET DB_TYPE = 'B', CONSUMER_ID = CONCAT(SUBSTRING(CONSUMER_ID, 1, CHARINDEX('|', CONSUMER_ID) - 1), '|B') FROM ignite.CONLIC_INSTANCE_ALLOCATION AS cia INNER JOIN ignite.COND AS cd ON SUBSTRING(cia.CONSUMER_ID, 1, CHARINDEX('|', cia.CONSUMER_ID) - 1) = cd.ID WHERE cd.DB_TYPE = 'MariaDB' AND cia.DB_TYPE = 'M' AND cia.CONSUMER_ID = CONCAT(cd.ID, '|M');

      3. Run query 3:

        UPDATE ignite.CON_VERSION SET VERSION = 20244007 WHERE ARTIFACT = 'REPOVERSION';

    • For an Oracle repository database:

      1. Run query 1:

        UPDATE COND SET DB_TYPE = 'MariaDB', MONDBVERSION = RTRIM(SUBSTR(MONDBVERSION, 0, INSTR(MONDBVERSION,'(MariaDB)')-1)) WHERE JDBC_DRIVER = 'org.mariadb.jdbc.Driver' AND DB_TYPE = 'MySQL' AND MONDBVERSION like '%(MariaDB)';

      2. Run query 2:

        UPDATE CONLIC_INSTANCE_ALLOCATION cia SET (cia.DB_TYPE, cia.CONSUMER_ID) = (SELECT 'B', cd.ID || '|B' FROM COND cd WHERE cd.DB_TYPE = 'MariaDB' AND cia.CONSUMER_ID = TO_CHAR(cd.ID) || '|M') WHERE cia.DB_TYPE = 'M' AND cia.CONSUMER_ID IN (SELECT cd.ID||'|M' FROM COND cd WHERE cd.DB_TYPE = 'MariaDB');

      3. Run query 3:

        UPDATE CON_VERSION SET VERSION = 20244007 WHERE ARTIFACT = 'REPOVERSION';

    • For a MySQL repository database:

      1. Run query 1:

        UPDATE COND SET DB_TYPE = 'MariaDB', MONDBVERSION = TRIM(SUBSTRING_INDEX(MONDBVERSION, '(MariaDB)', 1)) WHERE JDBC_DRIVER = 'org.mariadb.jdbc.Driver' AND DB_TYPE = 'MySQL' AND MONDBVERSION like '%(MariaDB)';

      2. Run query 2:

        UPDATE CONLIC_INSTANCE_ALLOCATION cia  JOIN COND cd ON cd.ID = TRIM(SUBSTRING_INDEX(cia.CONSUMER_ID, '|', 1)) AND cd.DB_TYPE = 'MariaDB' AND cia.DB_TYPE  = 'M' SET cia.DB_TYPE = 'B', cia.CONSUMER_ID = CONCAT(cd.ID, '|B') WHERE cia.CONSUMER_ID = CONCAT(cd.ID, '|M');

      3. Run query 3:

        UPDATE CON_VERSION SET VERSION = 20244007 WHERE ARTIFACT = 'REPOVERSION';

  3. Restart the DPA server.