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

Writing multiple procedure/udfs for a Snowflake is throwing errors during migrate #2843

Closed
srjonemed opened this issue Jun 3, 2020 · 9 comments

Comments

@srjonemed
Copy link

@srjonemed srjonemed commented Jun 3, 2020

Which version and edition of Flyway are you using?

Flyway Community Edition 6.2.0 by Redgate
snowflake-jdbc-3.12.0.jar

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)

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

Command-line

Which database are you using (type & version)?

Snowflake 4.18.1

Which operating system are you using?

macOS

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.)
Created a new repeatable migration file, to create a set of related procedures and UDFs. However, when I run the command line migrate command, it fails with error.
If I edit the file to contain only ONE procedure or UDF then the migrate works.

What did you expect to see?

Expecting to be able to run the migrate successfully with multiple procedures and udfs in a single file.

What did you see instead?

Error Message -

Migration R__auth_view_procs.sql failed
-----------------------------------------------------------
SQL State  : 0A000
Error Code : 8
Message    : Actual statement count 2 did not match the desired statement count 1.

Also, tried the same using a Versioned sql file but results in same error message.

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Jun 3, 2020

Would you be able to share the script with us? If not in public then by email to support@flywaydb.org .

@srjonemed
Copy link
Author

@srjonemed srjonemed commented Jun 3, 2020

You could use the following test content as the content of the script:

/* script begins */
create or replace procedure tst_exec_owner_proc()
returns VARCHAR
language javascript
as
$$
// Set the second session variable
var stmt = snowflake.createStatement(
{sqlText: "select CURRENT_ROLE();"}
);
var rs = stmt.execute();
rs.next();

return rs.getColumnValue(1);
$$
;

create or replace function tst_exec_owner_func()
returns VARCHAR
as
$$
select current_role()
$$;

/* script ends */

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Jun 3, 2020

That's great, thankyou. I will investigate and get back to you!

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Jun 4, 2020

Many thanks; I can reproduce and a fix will be in the next patch version.

@juliahayward juliahayward added this to the Flyway 6.4.4 milestone Jun 4, 2020
rgautomatedbuild pushed a commit that referenced this issue Jun 4, 2020
@srjonemed
Copy link
Author

@srjonemed srjonemed commented Jun 4, 2020

Thats great! appreciate the quick response on this..

@srjonemed
Copy link
Author

@srjonemed srjonemed commented Jun 8, 2020

@juliahayward thanks again for putting in the fix. could you please let me know when 6.4.4 would be available for downloads?

@juliahayward
Copy link
Member

@juliahayward juliahayward commented Jun 8, 2020

It should be in the next couple of days, all being well.

@srjonemed
Copy link
Author

@srjonemed srjonemed commented Jun 8, 2020

Thanks!

juliahayward added a commit that referenced this issue Jun 10, 2020
@smsdatabase
Copy link

@smsdatabase smsdatabase commented Sep 10, 2020

One workaround is to substitute $$ with ' (singe quote)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants