Unable to perform flyway migrate with SYS schema

0
votes

The first of my many migration scripts creates the schema and tablespace which the rest of my migration scripts create tables in, perform inserts in, etc. This is done so I can drop the entire schema and start from scratch with flyway. The cmd flyway migrate command connects as "SYS AS SYSDBA", validates my migration files and then gives me the following error:

ERROR: Found non-empty schema "SYS" without without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the metadata table.

Setting baselineOnMigrate to true in flyway.conf does not change anything.

Is what I am attempting to do possible? Is there a reason I should not be doing this?

1
oracleflyway
You should never, ever, under any circumstances create any object owned by SYS. You always need to create a new schema for any user objects that you want to create. I have no experience with flyway but I would strongly assume that you would want to create a new schema, put your application's objects in that schema, and use that user when you run flyway. - Justin Cave
No objects are created under the SYS schema. A tablespace and schema are created in the first migration script. Then every subsequent object is created under that schema as specified in the question. - Roger
OK. So you're not trying to migrate your SYS schema? Just the schema you created for your application? The title and the error message made me think you were saying that you wanted to migrate the SYS schema itself. - Justin Cave
Exactly. The database is essentially empty when I am attempting to use flyway, so only SYS and SYSTEM exist. - Roger

1 Answers

0
votes

I believe you might not have set the "schemas" config variable correctly. If it is not set at all, Flyway will just use the connection schema - in this case "SYS". Then it will try to create the SCHEMA_MIGRATION Table as SYS and then terminates, because SYS is not empty.

The solution would be to set the "schemas" variable to a different user, and then Flyway would automatically create this user if it does not exist yet. Just make sure to add a migration script that would grant the respective privileges (e.g. connect/resource) as needed by your application and probably reset the password for that user via an ALTER USER statement (instead of a CREATE USER statement).