Flyway init() fails to upgrade schema_version when it is a synonym

Question

In some respects, similar to How to handle Oracle synonyms with Flyway 2.0.1?. Our controlling Security & DBA groups have decreed one Oracle schema for the objects (tables, views, sp) {owner} and one schema for accessing via synonyms {user}. Most apps could probably use the switch context, but apart from the aforementioned policy blindsiding me, there is some legacy PL/SQL usage in amongst the mostly Java JPA access. Owner does not have any access to user schema. User can create synonyms.

Not yet looked at writing custom Java code - trying to do off the mvn command line out of the box.

So, I have run Flyway 2.2 init() against Owner schema and then immediately created a copy called SCHEMA_VERSION_USER. (Upper case to avoid Oracle (10g) causing issues with synonym.)

Manually created a synonym SCHEMA_VERSION_USER to Owner.SCHEMA_VERSION_USER.

Executed

mvn compile flyway:migrate -Dflyway.user=USER -Dflyway.table=SCHEMA_VERSION_USER

but received

[INFO] Upgrading the metadata table "USER"."SCHEMA_VERSION_USER" to the Flyway 2.0 format...
[INFO] Checking prerequisites...
[ERROR] com.googlecode.flyway.core.api.FlywayException: Unable to upgrade the metadata table "USER"."SCHEMA_
VERSION_USER" to the Flyway 2.0 format
[ERROR] Caused by java.sql.SQLException: ORA-00904: "DESCRIPTION": invalid identifier

[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Flyway Error: com.googlecode.flyway.core.api.FlywayException: Unable to upgrade the metadata table "USER"."SCHEMA_VERSION_USER" to the Flyway 2.0 format

ORA-00904: "DESCRIPTION": invalid identifier

When I tried qualifying the table as in -Dflyway.table=USER.SCHEMA_VERSION_USER it fails with

[ERROR] com.googlecode.flyway.core.api.FlywayException: Found non-empty schema "USER" without metadata table! Use init() first to initialize the metadata table.

Is this a defect; deliberate design; not thought of; backlog? Do I need to have the DBA execute the everything as system dba to avoid permissioning issues? Prefer not to as it kind of locks us into using them for all environments not just prod and we are trying to introduce 'automated' continuous delivery. Or maybe just have them manually create the schema_version table in the User schema to avoid the initial upgrade check? Do I have to write java to bypass the initial upgrade check?

Post note: We are trying to get them to embrace some modern DB automation tools - they have previously only accepted custom handrolled control scripts that are a maintenance nightmare.

Thanks!

Axel Fontaine Axel Fontaine · Accepted Answer · 2013-08-13T08:11:11

This is a scenario that had not come up so far. The upgrade code assumes it is a table, and wants to ensure a smooth migration from Flyway 1.x. It cannot be bypassed at this point.

Creating the table in the user's schema should solve the issue. You can specify the schema where the table will be created by using the flyway.schemas property. The table will be created in the first one of the list.

Flyway init() fails to upgrade schema_version when it is a synonym

3 Answers