SQL command SPOOL not working as expected using a callback script during migrate/info

[dfly64]

Which version and edition of Flyway are you using?

Flyway Enterprise Edition 6.3.2 by Redgate

In searching I found this bug issue: unsupported SQL*Plus statement: SPOOL #2674 FYI but am running later version than what was documented.

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Yes, in Flyway Enterprise Edition 6.5.0 by Redgate

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

Command-line

Which database are you using (type & version)?

Oracle 12.1.0.2.190716

Which operating system are you using?

Oracle Linux Server release 6.10

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

Would like to use the SQL callback feature to build and run a grants script after migration. The spool file grants_for_merge.sql is not being created. Resulting in unable to open file.

Here is the command:

flyway migrate info -url=jdbc:oracle:thin:@//xxxx-db-xx.asrs.local:1521/database.asrs.local -enterprise -locations=filesystem:/u01/flyway-6.5.0/sql

Here is the script and contents:

Script: afterMigrate__build_grants_for_merge.sql

$ cat afterMigrate__build_grants_for_merge.sql
set heading off;
set feedback off;
set define on;
set linesize 200;
set serveroutput on;
spool grants_for_merge.sql; >>>> spool not working as expected
prompt Building Merge Grants file...
@Merge_select_grant_script.sql;
spool off;
set feedback on;
prompt
prompt Finished building Merge Grants file.
prompt
prompt Running Merge Grants...
exec DBMS_LOCK.SLEEP(5);
@grants_for_merge.sql; >>>> Offending line of script according to Flyway error.

What did you expect to see?

grants_for_merge.sql should be created in /u01/flyway-6.5.0/sql

which contents would look something like this:

....
GRANT SELECT, UPDATE ON FDS_SOURCE.FDS_FAILED_DECEASED20190102 TO peris_merge;
GRANT SELECT, UPDATE ON FDS_SOURCE.FDS_FAILED_DECEASED_LOGINS TO peris_merge;
GRANT SELECT, UPDATE ON FDS_SOURCE.FDS_FAILED_DECEA_20190215 TO peris_merge;
GRANT SELECT, UPDATE ON FDS_SOURCE.FDS_KBA_PERSON_20190215 TO peris_merge;

...

What did you see instead?

ERROR: Unable to parse statement in /u01/flyway-6.5.0/sql/afterMigrate__build_grants_for_merge.sql at line 16 col 1. See https://flywaydb.org/documentation/knownparserlimitations for more information: SP2-0310: unable to open file "grants_for_merge.sql"
Caused by: SP2-0310: unable to open file "grants_for_merge.sql"

File was not created. Even if I create empty file to satisfy "unable to open file" - The error is suppressed and it steps thru the remaining script and runs as expected but the grants_for_merge.sql (I created) is still empty, telling me that SPOOL command is not working.

Note: If I run afterMigrate__build_grants_for_merge.sql via SQLplus.. it works as expected... no issues.

DEBUG info from Flyway 6.3.2 environment (/u01/flyway)

DEBUG: Loading config file: /u01/flyway/conf/flyway.conf
DEBUG: Unable to load config file: /home/oracle/flyway.conf
DEBUG: Unable to load config file: /u01/flyway/flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.baselineOnMigrate -> true
DEBUG: flyway.cleanDisabled -> true
DEBUG: flyway.connectRetries -> 5
DEBUG: flyway.defaultSchema -> FLYWAY
DEBUG: flyway.ignoreIgnoredMigrations -> true
DEBUG: flyway.installedBy ->
DEBUG: flyway.jarDirs -> /u01/flyway/jars
DEBUG: flyway.licenseKey -> FL0135E3******41D4
DEBUG: flyway.locations -> filesystem://u01/flyway/sql
DEBUG: flyway.oracle.sqlplus -> true
DEBUG: flyway.oracle.sqlplusWarn -> true
DEBUG: flyway.outputQueryResults -> true
DEBUG: flyway.password -> *************
DEBUG: flyway.schemas -> FLYWAY
DEBUG: flyway.sqlMigrationSuffixes -> sql,trg,pck
DEBUG: flyway.table -> FLYWAY_SCHEMA_HISTORY
DEBUG: flyway.tablespace -> FLYWAY_DATA_SMALL
DEBUG: flyway.url -> jdbc:oracle:thin:@//xxxx-db-xx.asrs.local:1521/database.asrs.local
DEBUG: flyway.user -> yvettec

....

DEBUG: Parsing afterMigrate__build_grants_for_merge.sql ...
DEBUG: Found statement at line 1: set heading off
DEBUG: Found statement at line 2: set feedback off
DEBUG: Found statement at line 3: set define on
DEBUG: Found statement at line 4: set linesize 200
DEBUG: Found statement at line 5: prompt Building Merge Grants...
DEBUG: Found statement at line 6: spool grants_for_merge.sql
DEBUG: Parsing Merge_select_grant_script.sql ...
DEBUG: Found statement at line 1: select 'GRANT ' || grants || ' ON ' || owner_name || '.' || table_name || ' TO peris_merge;' from TABLE(peris_merge.list_grants)
DEBUG: Found statement at line 7: @Merge_select_grant_script.sql
DEBUG: Found statement at line 8: spool off
DEBUG: Found statement at line 9: set feedback on
DEBUG: Found statement at line 10: prompt
DEBUG: Found statement at line 11: prompt Finished building Merge Grants file.
DEBUG: Found statement at line 12: prompt
DEBUG: Found statement at line 13: prompt Running Merge Grants...
DEBUG: Memory usage: 37 of 2048M
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to parse statement in /u01/flyway/sql/afterMigrate__build_grants_for_merge.sql at line 15 col 1: SP2-0310: unable to open file "grants_for_merge.sql"
at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:287)
at org.flywaydb.core.internal.parser.Parser.access$000(Parser.java:41)
at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.next(Parser.java:712)
at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.next(Parser.java:677)
at org.flywaydb.core.internal.sqlscript.ParserSqlScript.parse(ParserSqlScript.java:82)
at org.flywaydb.core.internal.sqlscript.ParserSqlScript.validate(ParserSqlScript.java:127)
at org.flywaydb.core.internal.sqlscript.ParserSqlScript.executeInTransaction(ParserSqlScript.java:196)
at org.flywaydb.core.internal.callback.SqlScriptCallbackFactory$SqlScriptCallback.canHandleInTransaction(SqlScriptCallbackFactory.java:127)
at org.flywaydb.core.internal.callback.DefaultCallbackExecutor.execute(DefaultCallbackExecutor.java:103)
at org.flywaydb.core.internal.callback.DefaultCallbackExecutor.onMigrateOrUndoEvent(DefaultCallbackExecutor.java:67)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:147)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:193)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:153)
at org.flywaydb.core.Flyway.execute(Flyway.java:523)
at org.flywaydb.core.Flyway.migrate(Flyway.java:153)
at org.flywaydb.commandline.Main.executeOperation(Main.java:192)
at org.flywaydb.commandline.Main.main(Main.java:128)
Caused by: org.flywaydb.core.api.FlywayException: SP2-0310: unable to open file "grants_for_merge.sql"
at org.flywaydb.core.internal.database.oracle.pro.SQLPlusAtParsedSqlStatement.(SQLPlusAtParsedSqlStatement.java:63)
at org.flywaydb.core.internal.database.oracle.OracleParser.createStatement(OracleParser.java:240)
at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:226)
... 16 more
No More lines...

[Lyeeedar]

I just have a couple of questions about how SPOOL works.

Am I right in saying that SPOOL pipes all the output of queries run whilst it is on into a local file?
I see you have call SPOOL with just a file name, looking at the docs for SPOOL it seems theres CREATE, REPLACE, and APPEND modes, which mode is the default?
Do you expect SPOOL state to persist across migration files when migrating multiple files at once?

[dfly64]

Am I right in saying that SPOOL pipes all the output of queries run whilst it is on into a local file?
SPOOL captures output of SQL results to a local file until SPOOL END.

I see you have call SPOOL with just a file name, looking at the docs for SPOOL it seems theres CREATE, REPLACE, and APPEND modes, which mode is the default?
REPLACE is default

Do you expect SPOOL state to persist across migration files when migrating multiple files at once?
Once the SQL is spooled, I expect the file to be available until it is deleted

[dfly64]

Hi Philip,

Could you please let me know in what version of Flyway SPOOL will be supported and also please provide a list of SQL commands that ARE supported for future reference.

If SPOOL is not supported, I would expect to see a message more like:

WARNING: Detected unsupported SQLPlus statement: SPOOL OFF
-> Please file an issue requesting support at https://github.com/flyway/flyway/issues
WARNING: Detected unsupported SQLPlus statement: SPOOL /spoolfile.log
-> Please file an issue requesting support at https://github.com/flyway/flyway/issues

As pointed out in #2674

Which by the way in this ticket, it is mentioned that the fix would be in Flyway 6.2.4 and we are using 6.3.2

Could you please confirm with the Flyway developer Julia Hayward @juliahayward if the fix went in?

Our error message is:

DEBUG: Parsing afterMigrate__build_grants_for_merge.sql ...
DEBUG: Found statement at line 1: set heading off
DEBUG: Found statement at line 2: set feedback off
DEBUG: Found statement at line 3: set define on
DEBUG: Found statement at line 4: set linesize 200
DEBUG: Found statement at line 5: prompt Building Merge Grants...
DEBUG: Found statement at line 6: spool grants_for_merge.sql
DEBUG: Parsing Merge_select_grant_script.sql ...
DEBUG: Found statement at line 1: select 'GRANT ' || grants || ' ON ' || owner_name || '.' || table_name || ' TO peris_merge;' from TABLE(peris_merge.list_grants)
DEBUG: Found statement at line 7: @Merge_select_grant_script.sql
DEBUG: Found statement at line 8: spool off
DEBUG: Found statement at line 9: set feedback on
DEBUG: Found statement at line 10: prompt
DEBUG: Found statement at line 11: prompt Finished building Merge Grants file.
DEBUG: Found statement at line 12: prompt
DEBUG: Found statement at line 13: prompt Running Merge Grants...
DEBUG: Memory usage: 37 of 2048M
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to parse statement in /u01/flyway/sql/afterMigrate__build_grants_for_merge.sql at line 15 col 1: SP2-0310: unable to open file "grants_for_merge.sql"

I have copied my Manager on this email.

Thanks very much, I await your reply.

Thank you,

Yvette Cook

Database Administrator

Technical Services Department (TSD)

Arizona State Retirement System

3300 N. Central Avenue

Phoenix, AZ 85012

Desk: 602-240-2186

Cell: 480-330-0725

Email: yvettec@azasrs.gov

DBA Team Email: dbas@azasrs.gov

DBA JIRA Email: dbahelp@azasrs.gov

[dfly64]

I tested flyway using a known unsupported SQL command - SAVE and received the following:

WARNING: Detected unsupported SQL*Plus statement: SAVE grants_for_merge.sql create
-> Please file an issue requesting support at https://github.com/flyway/flyway/issues

This is what I would expect from SPOOL if it was truly unsupported.

Please confirm.

[dfly64]

I also want to mention that I tried to provide a path in both the SPOOL command and at the SQL execution of spooled file:
@grants_for_merge.sql;

Either way, it failed with the same error.

[juliahayward]

Fixed in v6.5.1