Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wildcards in errorOverrides #2318

Closed
Me1gireesh opened this issue Mar 5, 2019 · 5 comments

Comments

Projects
None yet
2 participants
@Me1gireesh
Copy link

commented Mar 5, 2019

Is there a way we can ignore any SQL errors with out existing the migration execution? I tried to explore the error override, but it requires to manually input all the sql error code and STATE and action, I am looking for something like flyway.errorOverrides=*:*:W. I have also tried to use a callback statement with "WHENEVER SQLERROR CONTINUE" , but the change doesnt stay when we move to a migration patch.( I guess that works if we have the "WHENEVER SQLERROR CONTINUE" in each migration patch. Appreciate your help here.

@axelfontaine axelfontaine added this to the Flyway 6.0.0 milestone Mar 5, 2019

@axelfontaine axelfontaine changed the title Is there a way we can ignore any SQL errors with out existing the migration execution? I tried to explore the error override, but it requires to manually input all the sql error code and STATE and action, I am looking for something like flyway.errorOverrides=*:*:W. I have also tried to use a callback statement with "WHENEVER SQLERROR CONTINUE" , but the change doesnt stay when we move to a migration patch.( I guess that works if we have the "WHENEVER SQLERROR CONTINUE" in each migration patch. Appreciate your help here. Wildcards in errorOverrides Mar 5, 2019

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented Mar 5, 2019

You can currently achieve this with Oracle, with SQL*Plus support activated, by placing WHENEVER SQLERROR CONTINUE in a beforeEachMigrate.sql callback.

I'm leaving this open as a feature request as it would however be nice to have this for all databases.

@Me1gireesh

This comment has been minimized.

Copy link
Author

commented Mar 5, 2019

As mentioned initially, I did try to create a beforeEachMigrate.sql call back with "WHENEVER SQLERROR CONTINUE", but that did not work as this session setting did not take effect when the migration patch was run. However, if I place the "WHENEVER SQLERROR CONTINUE" inside the migration Patch, It continues the execution by throwing a warning message for the ORA error and the migration is marked as success on the FLYWAY schema table.

Logs are below:

Successfully validated 8 migrations (execution time 00:00.876s)
DEBUG: Schema "PROCESS_CICD" already exists. Skipping schema creation.
Executing SQL callback: beforeMigrate
DEBUG: Executing SQL: WHENEVER SQLERROR CONTINUE
DEBUG: Locking table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"...
DEBUG: Lock acquired for table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"
Current version of schema "PROCESS_CICD": 20.0502.201902181324
WARNING: outOfOrder mode is active. Migration of schema "PROCESS_CICD" may not be reproducible.
Migrating schema "PROCESS_CICD" to version 20.0502.201902181325 - create table test1
DEBUG: Executing SQL: delete * from sdr.test1
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Migration of schema "PROCESS_CICD" to version 20.0502.201902181325 - create table test1 failed! Please restore backups and roll back database and code!
DEBUG: Locking table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"...
DEBUG: Lock acquired for table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"
DEBUG: Schema History table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE" successfully updated to reflect changes
DEBUG: Memory usage: 61 of 245M
ERROR: Unexpected error
org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
Migration V20.0502.201902181325__create_table_test1.sql failed

SQL State : 42000
Error Code : 903
Message : ORA-00903: invalid table name
Location : C:\gireesh\Enviornment\flyway_revamped\repo\source\V20.0502.201902181325__create_table_test1.sql (C:\gireesh\Enviornment\flyway_revamped\repo\source\V20.0502.201902181325__create_table_test1.sql)
Line : 1
Statement : delete * from sdr.test1

    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:370)
    at org.flywaydb.core.internal.command.DbMigrate.access$200(DbMigrate.java:54)
    at org.flywaydb.core.internal.command.DbMigrate$3.call(DbMigrate.java:284)
    at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:281)
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:246)
    at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:54)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:164)
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:161)
    at org.flywaydb.core.internal.database.base.Connection$1.call(Connection.java:147)
    at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
    at org.flywaydb.core.internal.database.base.Connection.lock(Connection.java:143)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:155)
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:161)
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:139)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1395)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1356)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1711)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1356)
    at org.flywaydb.commandline.Main.executeOperation(Main.java:161)
    at org.flywaydb.commandline.Main.main(Main.java:108)

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException:
Migration V20.0502.201902181325__create_table_test1.sql failed

SQL State : 42000
Error Code : 903
Message : ORA-00903: invalid table name
Location : C:\gireesh\Enviornment\flyway_revamped\repo\source\V20.0502.201902181325__create_table_test1.sql (C:\gireesh\Enviornment\flyway_revamped\repo\source\V20.0502.201902181325__create_table_test1.sql)
Line : 1
Statement : delete * from sdr.test1

    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:253)
    at org.flywaydb.core.internal.database.oracle.OracleSqlScriptExecutor.handleException(OracleSqlScriptExecutor.java:83)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:202)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:125)
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:77)
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:367)
    ... 20 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:461)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:402)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1065)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:681)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:256)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:577)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:205)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1222)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1401)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2493)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2423)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:1071)
    at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:235)
    at org.flywaydb.core.internal.sqlscript.StandardSqlStatement.execute(StandardSqlStatement.java:42)
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:189)
    ... 23 more
@Me1gireesh

This comment has been minimized.

Copy link
Author

commented Mar 5, 2019

Here is the log, when I added "WHENEVER SQLERROR CONTINUE" inside the migration script.

Successfully validated 8 migrations (execution time 00:00.943s)
DEBUG: Schema "PROCESS_CICD" already exists. Skipping schema creation.
Executing SQL callback: beforeMigrate
DEBUG: Executing SQL: WHENEVER SQLERROR CONTINUE
DEBUG: Locking table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"...
DEBUG: Lock acquired for table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"
Current version of schema "PROCESS_CICD": 20.0502.201902181324
WARNING: outOfOrder mode is active. Migration of schema "PROCESS_CICD" may not be reproducible.
Migrating schema "PROCESS_CICD" to version 20.0502.201902181325 - create table test1
DEBUG: Executing SQL: WHENEVER SQLERROR CONTINUE
DEBUG: Executing SQL: delete * from sdr.test1
WARNING: DB error ignored (Code: 903, State: 42000): ORA-00903: invalid table name

DEBUG: Executing SQL: commit
DEBUG: Successfully completed migration of schema "PROCESS_CICD" to version 20.0502.201902181325 - create table test1
DEBUG: Locking table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"...
DEBUG: Lock acquired for table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"
DEBUG: Schema History table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE" successfully updated to reflect changes
DEBUG: Locking table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"...
DEBUG: Lock acquired for table "PROCESS_CICD"."FLYWAY_SCHEMA_TABLE"
Successfully applied 1 migration to schema "PROCESS_CICD" (execution time 00:05.082s)
DEBUG: Memory usage: 61 of 245M

@Me1gireesh

This comment has been minimized.

Copy link
Author

commented Mar 5, 2019

I am working on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Apr 9, 2019

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented Apr 9, 2019

Wildcards have now been implemented.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.