We are evaluating flyway to setup an Oracle database that has an OWNER schema where all tables live, and an USER, who gets granted access to OWNER tables in the form of synonyms. Sometimes the USER needs to write their own tables, too. We'd also like to keep the upgrade sql scripts in one subdirectory, rather than having a separate directory for OWNER and USER each.
So, OWNER has the following config:
flyway.url=jdbc:oracle:thin:@localhost:1521
flyway.user=OWNER
flyway.schemas=OWNER
flyway.password=OWNER
and USER, correspondingly
flyway.url=jdbc:oracle:thin:@localhost:1521
flyway.user=USER
flyway.schemas=OWNER, USER
flyway.password=USER
where i would expect that flyway will access the SCHEMA_VERSION table in the OWNER schema, as it is the first one in the list of schemas.
The whole thing runs in vagrant, and when i say vagrant up i get an error message. The relevant snippet is:
==> vagrantbox: Successfully applied 1 migration to schema "OWNER" (execution time 00:00.388s).
==> vagrantbox: flyway -configFile=config/USER.conf -target=1.99999 migrate
==> vagrantbox: Flyway 3.2.1 by Boxfuse
==> vagrantbox: Database: jdbc:oracle:thin:@localhost:1521 (Oracle 11.2)
==> vagrantbox: Validated 4 migrations (execution time 00:00.119s)
==> vagrantbox: Creating Metadata table: "OWNER"."schema_version"
==> vagrantbox: ERROR:
==> vagrantbox: Script failed
==> vagrantbox: -------------
==> vagrantbox: SQL State : 42000
==> vagrantbox: Error Code : 1031
==> vagrantbox: Message : ORA-01031: insufficient privileges
==> vagrantbox: Line : 17
==> vagrantbox: Statement : CREATE TABLE "OWNER"."schema_version" (
which looks like flyway is able to determine that the schema_version table for USER is supposed to be in the OWNER schema, but fails to see that it's already there.
In the flyway sources i see that this check is done in MetadataTableImpl like this:
private void createIfNotExists() {
if (table.exists()) {
return;
}
which in turn for Oracle results in a call to the connection's metadata:
resultSet = jdbcTemplate.getMetaData().getTables(
catalog == null ? null : catalog.getName(),
schema == null ? null : schema.getName(),
table,
types);
found = resultSet.next();
Does this work with a stock Oracle XE version 11.2.0 JDBC driver? After adding xdb6.jar and xmlparserv2.jar from SQL Developer to the classpath the following groovy snippet
def results = conn.getMetaData().getTables(null, "OWNER", "schema_version")
println results.next() as boolean
claims otherwise:
$ groovy TestMetaData.groovy
false
That would explain that flyway seems to think the table is not there, but now i am completely stumped. What is going on here? Surely the call to getMetaData().getTables() must work in general? Or is this something the thin driver can't do?
