Migration error on Snowflake procedure and Clean error on Snowflake function and procedure

[zjwu2000]

Which version and edition of Flyway are you using?

6.1.0 and "Flyway PR #2274"

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)
The issues exist in both 6.1.0 and "Flyway PR #2274"

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

Command-line

Which database are you using (type & version)?

Snowflake

Which operating system are you using?

Windows

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.)
procedure migration "R__Procedure_MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES.sql":

CREATE OR REPLACE PROCEDURE MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES () RETURNS VARIANT LANGUAGE JAVASCRIPT
AS $$ 
var results_array = []; 
var rs = snowflake.createStatement( 
            { sqlText: 'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES' }).execute(); 
    while (rs.next()) 
    {    var schema_name = rs.getColumnValue(1);    
        var table_name = rs.getColumnValue(2);  
        results_array.push([schema_name, table_name]); 
    }  
return results_array; 
$$;

What did you expect to see?

Migrate command should apply procedure script successfully
and Clean command should drop the function and procedure successfully

What did you see instead?

Error message

> ERROR: Migration of schema "PUBLIC" with repeatable migration Procedure MDM.SP ARRAY GET INF SCHEMA TABLES failed! Please restore backups and roll back database and code!
ERROR:
Migration R__Procedure_MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES.sql failed
--------------------------------------------------------------------
SQL State  : 42000
Error Code : 1003
Message    : SQL compilation error:
parse error line 3 at position 22 near '<EOF>'.
syntax error line 2 at position 4 unexpected '$'.
Location   : C:\flyway-6.1.0\sql\Procedures\R__Procedure_MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES.sql (C:\flyway-6.1.0\sql\Procedures\R__Procedure_MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES.sql)
Line       : 1
Statement  : CREATE OR REPLACE PROCEDURE MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES () RETURNS VARIANT LANGUAGE JAVASCRIPT
AS $$
var results_array = []

Here's what I did to fix tissue

A) To fix the snowflake procedure script migration issue, I added the function extractAlternateOpenQuote(String token) in the flyway PR2274 source file flyway-core\src\main\java\org\flywaydb\core\internal\database\snowflake\SnowflakeSqlStatementBuilder.java:
@Override protected String extractAlternateOpenQuote(String token) { if (token.startsWith("$$")) { return "$$"; } return null; }

B) To fix the Clean command issue which failed in dropping the snowflake function, I modified the function generateDropFunctionStatements() in the flyway PR2274 source file snowflake\SnowflakeSchema.java. The fix is to put the quote around the function name only instead of function name and parameters.
old code:

private List<String> generateDropFunctionStatements() throws SQLException {
       List<Map<String, String>> objects = getObjects(FUNCTIONS, "%","arguments");
       List<String> result = new ArrayList<String>();
       for (Map<String, String> object : objects) {
           String value = object.get("arguments");
           // remove the RETURN clause from the fuction signature
           value = value.replaceAll("\\sRETURN\\s.*", "");
           result.add("DROP " + FUNCTIONS.getCreateDropType() + " " + database.quote(name, value));
       }
       return result;
   }

new code:

private List<String> generateDropFunctionStatements() throws SQLException {
        List<Map<String, String>> objects = getObjects(FUNCTIONS, "%","name", "arguments");
        List<String> result = new ArrayList<String>();
        for (Map<String, String> object : objects) {
            String funcName = object.get("name");
            String value;
            value = object.get("arguments");

            // remove the RETURN clause from the fuction signature
            value = value.replaceAll("\\sRETURN\\s.*", "");
            value = value.replaceFirst(funcName, "");
            result.add("DROP " + FUNCTIONS.getCreateDropType() + " " + database.quote(name, funcName) + value);        }
        return result;
    }

C) To fix the Clean command issue which failed in dropping the snowflake procedure, I updated the following source code files:

1. file ..\database\snowflake\SnowflakeObjectType.java
   added PROCEDURES("PROCEDURES", "PROCEDURE") in public enum SnowflakeObjectType { }:

public enum SnowflakeObjectType {

    SCHEMAS("SCHEMAS", "SCHEMA"),
    OBJECTS("OBJECTS", "OBJECT"),
    TABLES("TABLES", "TABLE"),
    VIEWS("VIEWS", "VIEW"),
    FILE_FORMATS("FILE FORMATS", "FILE FORMAT"),
    SEQUENCES("SEQUENCES", "SEQUENCE"),
    STAGES("STAGES", "STAGE"),
    PIPES("PIPES", "PIPE"),
    FUNCTIONS("USER FUNCTIONS", "FUNCTION"),
// *** new code ***
    PROCEDURES("PROCEDURES", "PROCEDURE");  

2. in snowflake\SnowflakeSchema.java, added function generateDropProcedureStatements() :

private List<String> generateDropProcedureStatements() throws SQLException {
        List<Map<String, String>> objects = getObjects(PROCEDURES, "%","name", "arguments");
        List<String> result = new ArrayList<String>();
        for (Map<String, String> object : objects) {
            String procName = object.get("name");
            String value;
            value = object.get("arguments");

            // remove the RETURN clause from the fuction signature
            value = value.replaceAll("\\sRETURN\\s.*", "");
            value = value.replaceFirst(procName, "");
            result.add("DROP " + PROCEDURES.getCreateDropType() + " " + database.quote(name, procName) + value);
        }
        return result;
    }

3. added procedure "for loop" in doClean() in snowflake\SnowflakeSchema.java:

@Override
    protected void doClean() throws SQLException {
        for (String statement : generateDropStatements(VIEWS)) {
            jdbcTemplate.execute(statement);
        }
        for (Table table : allTables()) {
            table.drop();
        }
        for (String statement : generateDropStatements(STAGES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(FILE_FORMATS)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(SEQUENCES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropStatements(PIPES)) {
            jdbcTemplate.execute(statement);
        }
        for (String statement : generateDropFunctionStatements()) {
            jdbcTemplate.execute(statement);
        }
// *** new code  ***
       for (String statement : generateDropProcedureStatements()) {
            jdbcTemplate.execute(statement);
        }
    }

[MikielAgutu]

Thanks for the detailed repro steps. The fix will be shipped in Flyway 6.1.1

[zjwu2000]

@MikielAgutu, Thanks for fixing the snowflake bug so quickly.
I have downloaded the latest code from the master branch and tested out the "Migrate" online command and "Clean" online command on the stored procedure scripts. The good news is the reported stored procedure is migrated successfully!
Meanwhile, I find two issues:

Issue 1:
if the stored procedure migration has the semicolon ";" put in a separate line at the end of the script file, the "Migrate" command reports "Unable to parse statement" error

Here is the sample migration code:

CREATE OR REPLACE PROCEDURE MDM.SP_ARRAY_GET_INF_SCHEMA_TABLES () RETURNS VARIANT LANGUAGE JAVASCRIPT
AS $$ 
var results_array = []; 
var rs = snowflake.createStatement( 
            { sqlText: 'SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;' }).execute(); 
    while (rs.next()) 
    {    var schema_name = rs.getColumnValue(1);    
        var table_name = rs.getColumnValue(2);  
        results_array.push([schema_name, table_name]); 
    }  
return results_array; 
$$
;

Here is the error message:
ERROR: Unable to parse statement in C:\flyway-6.1.1\sql\Procedures\R__Procedure_mdm_nossn_MatchRecursion.sql at line 17 col 1: Index 256 out of bounds for length 256

Issue 2:
The "Clean" command failed in dropping snowflake stored procedure from the database.
For example, I successfully migrated two functions and two stored procedures into the Snowflake database, then I run "Flyway clean" command to clean the database. The "clean" command completed without error, when I check the database, the two functions were dropped, but the two stored procedures are still in the database.

Here are the sample statements I use to check the functions and procedures in Snowflake database:

SHOW USER FUNCTIONS IN SCHEMA MDM;
SHOW PROCEDURES IN SCHEMA MDM;