Upgrade database fails with "No upgrade path" error
Article ID: 277148
Updated On:
Products
Issue/Introduction
The product document states upgrade from 9.x to 10.1 is supported, but when upgrade the database, it shows following message,
Database upgrade is required.
Software version : 10.1.00
Database version : 9.0.00
Perform upgrade? [No]: yes
Enter Administrative Database Username [***]:
Enter Administrative Database Password:
Performing database upgrade:
Testing the upgrade on a test database ...
Creating test database "ssg_testUpgrade" (without audits).
Database creation may take a few minutes.
. . . . . . . . . . . . . . . . . . . . .
. . . . . . . . The test database was created.
Upgrading the test database. This may take a few minutes.
The database was not upgraded due to the following reasons:
No upgrade path from "9.0.00"
No changes have been made to the database. Please correct the problem and try again.
The config log (/opt/SecureSpan/Gateway/config/logs/config.log) shows following message,
INFO: Using database host '***'.
INFO: Using database port '3306'.
INFO: Using database name 'ssg'.
INFO: Using database user '***'.
INFO: Attempting to connect to an existing database (***/ssg) using username/password ***/<not shown>
INFO: Connection to the databases was a success
INFO: Now Checking database version.
INFO: Cancelling background task 'com.l7tech.gateway.config.manager.db.h@lbbdb65f (com.l7tech.gateway.config.manager.db.h)
INFO: SELECT COUNT(*) FROM ssg_testUpgrade.DATABASECHANGELOG
WARNING: No upgrade path from "9.0.00"
INFO: Cancelling background task 'com.l7tech.gateway.config.manager.db.h@6bff7554 (com.l7tech.gateway.config.manager.db.h)
INFO: dropping database "ssg_testUpgrade"
WARNING: The database was not upgraded due to the following reasons:
No upgrade path from "9.0.00"
No changes have been made to the database. Please correct the problem and try again.
Cause
Test the SQL directly in imported ssg database,
mysql -e "select count(*) from ssg.DATABASECHANGELOG"
ERROR 1146 (42S02) at line 1: Table 'ssg.DATABASECHANGELOG doesn't exist
But if use lower case table name, it works,
mysql -e "select count(*) from ssg.databasechangelog"
+----------+
| count(*) |
+----------+
| 446 |
+----------+
The table name becomes lower case after migrate the ssg database for some reason. (the reason could be, on the old database, the lower_case_table_names was set to 1)
On 10.1 the mysql by default set lower_case_table_names=0, which means the table name is case sensitive.
The gateway upgrade utility calls Liquibase API to check if the database has been liquified -- it will check for the existence of the liquibase changelog table, when table name is in lower case, it will get table doesn't exist error as above, then it returns false, then the gateway upgrade utility will try "legacyUpgrade", but the sql file(under /opt/SecureSpan/Gateway/config/etc/sql) for legacyUpgrade for 9.0 to 10.1 doesn't exist (the last one is upgrade_8.2.01-8.3.pre.sql), so it returns "No upgrade path" error.
Resolution
The solution is change the table name of DATABASECHANGELOG, DATABASECHANGELOGLOCK to upper case.
1. Assume that the database exported file name is ssg9.sql, in this file,
- search and replace all databasechangelog to DATABASECHANGELOG
- search and replace all databasechangeloglock to DATABASECHANGELOGLOCK
2. On the new gateway 10.1, drop ssg database, re-import the ssg database with the new ssg9.sql file
3. run SQL "select count(*) from ssg.DATABASECHANGELOG" again to ensure there is no more error like "ERROR 1146 (42S02) at line 1: Table 'ssg.DATABASECHANGELOG doesn't exist"
After that, the database upgrade is successful.
Feedback
