[oracle] flyway:clean doesn't handle DDL triggers very well

[eiden]

Here is how to reproduce

1. Create a table and a sequence

create table DDL_TRIGGER_LOG(BAR number);
create sequence DDL_TRIGGER_LOG_SEQ;

1. Create an "after ddl" and "before ddl" trigger that references the table and sequence

CREATE TRIGGER DDL_TRIGGER_AFTER
after ddl on schema
begin
    insert into DDL_TRIGGER_LOG(BAR)
    values(DDL_TRIGGER_LOG_SEQ.nextval);
end;

CREATE TRIGGER DDL_TRIGGER_BEFORE
before ddl on schema
begin
    insert into DDL_TRIGGER_LOG(BAR)
    values(DDL_TRIGGER_LOG_SEQ.nextval);
end;

1. Run flyway:clean

flyway-2.2.1 $ flyway clean [...]

ERROR: FlywayException: Unable to clean schema "DEVELOPER"
ERROR: Occured in com.googlecode.flyway.core.dbsupport.Schema.clean() at line 146
ERROR: Caused by java.sql.SQLSyntaxErrorException: ORA-04098: trigger 'DEVELOPER.DDL_TRIGGER' is invalid and failed re-validation

ERROR: Occured in oracle.jdbc.driver.T4CTTIoer.processError() at line 445

Since flyway drops the sequence before the triggers, the triggers become invalid. Since the triggers are ddl triggers, it's not possible to execute any more DDL statements:

drop trigger DDL_TRIGGER_BEFORE;
ORA-04098: trigger 'DEVELOPER.DDL_TRIGGER_AFTER' is invalid and failed re-validation

The only way to get rid of the triggers, is to log in as the SYSTEM-user and execute drop trigger <schema>.<trigger name>

I have worked around this by overwriting all DDL triggers before i run flyway:clean:

begin
    for r in (
        select owner || '.' || trigger_name as trigger_name
        from all_triggers 
        where owner = sys_context('userenv', 'current_schema')
                and trim(triggering_event) = 'DDL'
    ) loop
        execute immediate 'create or replace trigger ' || r.trigger_name || ' after ddl on schema begin null; end;';
    end loop;
end;

I'm not sure if overwriting the triggers is the best way to fix the problem...

[bjhartin]

I have submitted pull request #629, which would allow us to extend any of the DbSupport classes. This makes it trivial to override doClean(), as demonstrated by the 'CustomCleanTest' in the core module.

[axelfontaine]

Hi Christoffer,

thanks for the great report! I've reproduced it and fixed it.

Cheers
Axel