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

ErrorOverrides: override errors into warnings and warnings into errors #1675

Closed
umapathy08 opened this issue Jun 16, 2017 · 6 comments
Closed

ErrorOverrides: override errors into warnings and warnings into errors #1675

umapathy08 opened this issue Jun 16, 2017 · 6 comments

Comments

@umapathy08
Copy link

@umapathy08 umapathy08 commented Jun 16, 2017

What version of Flyway are you using?

Flyway 4.1.1

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Command-line

What database are you using (type & version)?

Oracle 11g

What operating system are you using?

RHEL 5.x

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

1 - I tried to run the below stored procedure using flyway.

create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;
/
show errors

Flyway complained that "show errors" is not a valid sql command and failed. It completely makes sense.

2 - So I tried to remove show errors and ran the same script again second time. Sql file look like below:

create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;
/

What did you expect to see?

I expect flyway to fail if there are any syntax errors in stored procedure.

I also understood that Oracle jdbc driver returns "warning" for the above execution. Is it a right ask to get flyway fail the migration as this is a syntax error stating the error code. As we can't fail the migration for all warnings, may be you can get the warning and check for the error code for syntax errors and throw an error explicitly.

What did you see instead?

For number 1 sql, And the output log looks like below:

DEBUG: Locking table "test"."schema_version"...
DEBUG: Lock acquired for table "test"."schema_version"
DEBUG: Found statement at line 1: create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;

DEBUG: Found statement at line 6: show errors
Migrating schema "test" to version 1.15.0 - test1.sql
DEBUG: Executing SQL: create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;

WARNING: DB: Warning: execution completed with warning (SQL State: 99999 - Error Code: 17110)
DEBUG: Update Count: 0
DEBUG: Executing SQL: show errors
DEBUG: Update Count: 0
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Migration of schema "test" to version 1.15.0 - test1.sql failed! Please restore backups and roll back database and code!
DEBUG: MetaData table "test"."schema_version" successfully updated to reflect changes
ERROR: Unexpected error
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V1_15_0__test1.sql failed

SQL State : 42000
Error Code : 900
Message : ORA-00900: invalid SQL statement
Location : sql/V1_15_0__test1.sql (/u01/app/nolio-jenkins/UBS/sql/V1_15_0__test1.sql)
Line : 6
Statement : show errors

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:145)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:76)
at org.flywaydb.core.internal.command.DbMigrate.doMigrate(DbMigrate.java:324)
at org.flywaydb.core.internal.command.DbMigrate.access$900(DbMigrate.java:49)
at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:280)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:277)
at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:49)
at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:201)
at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:142)
at org.flywaydb.core.internal.dbsupport.DbSupport$1.call(DbSupport.java:224)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
at org.flywaydb.core.internal.dbsupport.DbSupport.lock(DbSupport.java:220)
at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:142)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:969)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:930)
at org.flywaydb.core.Flyway.execute(Flyway.java:1413)
at org.flywaydb.core.Flyway.migrate(Flyway.java:930)
at org.flywaydb.commandline.Main.executeOperation(Main.java:148)
at org.flywaydb.commandline.Main.main(Main.java:103)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:267)
at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:142)

For number 2 sql, And the output log looks like below:

DEBUG: Filtering out resource: sql/V1_15_1__test1.sql (filename: V1_15_1__test1.sql)
Current version of schema "test": 1.14.8
DEBUG: Found statement at line 1: create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;

Migrating schema "test" to version 1.15.1 - test1.sql
DEBUG: Executing SQL: create or replace procedure sample1 is
BEGIN
DBMS_OUTPUT.1put_line ('Hello Mars!');
END;

WARNING: DB: Warning: execution completed with warning (SQL State: 99999 - Error Code: 17110)
DEBUG: Update Count: 0
DEBUG: Successfully completed migration of schema "test" to version 1.15.1 - test1.sql
DEBUG: MetaData table "test"."schema_version" successfully updated to reflect changes
DEBUG: Locking table "test"."schema_version"...
DEBUG: Lock acquired for table "test"."schema_version"
Successfully applied 1 migration to schema "test" (execution time 00:01.072s).

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 17, 2017

Related to #432

@davidkarlsen
Copy link

@davidkarlsen davidkarlsen commented Jun 17, 2017

also related to #1349

@haytona
Copy link

@haytona haytona commented Jul 19, 2017

Here is another approach which could be used as a work around: http://stackoverflow.com/a/16429231/79202. It does require extra SQL in the migration.

@axelfontaine axelfontaine added this to the Flyway 5.0.0 milestone Nov 18, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 29, 2017

It will be possible to achieve this with 5.0 using error handlers. We'll look at improving this for 5.1.

@axelfontaine axelfontaine removed this from the Flyway 5.0.0 milestone Nov 29, 2017
@axelfontaine axelfontaine added this to the Flyway 5.1.0 milestone Nov 29, 2017
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jan 5, 2018

A solution for this exact scenario has now been included in the docs: https://flywaydb.org/documentation/errorhandlers

Flyway 5.1 will also include a declarative solution that will not require you to write custom Java code.

@axelfontaine axelfontaine changed the title Flyway does not fail if there is a compilation error in Oracle stored procedure ErrorOverrides: override errors into warnings and warnings into errors May 24, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented May 24, 2018

This has now been implemented as a new configuration property.

To achieve the original intent of turning Oracle stored procedure compilation issues from warnings into errors, all you now need to do is add the following to your config:

flyway.errorOverrides=99999:17110:E

This will override the warning with SQL state 99999 and code 17110 into an error.

axelfontaine added a commit to flyway/flywaydb.org that referenced this issue May 24, 2018
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants