Jmix version: 1.5.3
Jmix Studio plugin version: 1.5.5-231
IntelliJ IDEA 2023.1.2 (Community Edition)
Build #IC-231.9011.34, built on May 16, 2023
Runtime version: 17.0.6+10-b829.9 x86_64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
GC: G1 Young Generation, G1 Old Generation
Kotlin: 231-1.8.21-IJ9011.34
Java 17.0.4 2022-07-19 LTS
Java™ SE Runtime Environment (build 17.0.4+11-LTS-179)
Java HotSpot™ 64-Bit Server VM (build 17.0.4+11-LTS-179, mixed mode, sharing)
Operating System: macOS 13.5 (22G74)
File System: Case-Sensitive Journaled HFS+ (APFS)
Database: PostgreSQL 13
Hello Everyone
I believe that I have discovered a Liquibase bug (or two).
Use Case: Correct a PostgreSQL stored procedure return value type from integer to bigint.
Background: I implemented several stored procedures using the Jmix example here: https://github.com/jmix-framework/jmix-samples-2/tree/main/stored-procedures-sample
For example:
<changeSet id="4" author="nf" dbms="postgresql">
<createProcedure procedureName="total_memberships">
create or replace function total_memberships()
returns integer as
$$
select count(*) as result from NF_MEMBERSHIP;
$$
language SQL;
</createProcedure>
</changeSet>
The above code executed correctly with Studio on my local MacOS development system and also when deploying my application via github in Heroku for all Jmix 1.5.x versions.
I then decided to deploy to Azure with PostgreSQL and in the Azure environment the Liquibase ChangeLog would not execute and threw the following exception(s) for all of my stored procedures:
2023-08-19T16:54:17.069095660Z Running Changeset: com/company/nf/liquibase/changelog/2022/11/25-133137-50849d54.xml::1::nf
2023-08-19T16:54:17.114681818Z 2023-08-19 16:54:17.114 WARN 73 — [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘jmix_Liquibase’ defined in class path resource [io/jmix/autoconfigure/data/JmixLiquibaseAutoConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/company/nf/liquibase/changelog/2022/11/25-133137-50849d54.xml::1::nf:
2023-08-19T16:54:17.114713226Z Reason: liquibase.exception.DatabaseException: ERROR: return type mismatch in function declared to return integer
2023-08-19T16:54:17.114740828Z Detail: Actual return type is bigint.
2023-08-19T16:54:17.114744986Z Where: SQL function “total_memberships” [Failed SQL: (0) create or replace function total_memberships()
2023-08-19T16:54:17.114749264Z returns integer as
2023-08-19T16:54:17.114753171Z $$
2023-08-19T16:54:17.114756918Z select count() as result from NF_MEMBERSHIP;
2023-08-19T16:54:17.114773239Z $$
2023-08-19T16:54:17.114777316Z language SQL]
2023-08-19T16:54:17.134018740Z 2023-08-19 16:54:17.133 INFO 73 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated…
2023-08-19T16:54:17.475967902Z 2023-08-19 16:54:17.475 INFO 73 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2023-08-19T16:54:17.499701661Z 2023-08-19 16:54:17.499 INFO 73 — [ main] ConditionEvaluationReportLoggingListener :
2023-08-19T16:54:17.499724243Z
2023-08-19T16:54:17.499729162Z Error starting ApplicationContext. To display the conditions report re-run your application with ‘debug’ enabled.
2023-08-19T16:54:17.536172082Z 2023-08-19 16:54:17.535 ERROR 73 — [ main] o.s.boot.SpringApplication : Application run failed
2023-08-19T16:54:17.536198802Z
2023-08-19T16:54:17.536204493Z org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘jmix_Liquibase’ defined in class path resource [io/jmix/autoconfigure/data/JmixLiquibaseAutoConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/company/nf/liquibase/changelog/2022/11/25-133137-50849d54.xml::1::nf:
2023-08-19T16:54:17.536210023Z Reason: liquibase.exception.DatabaseException: ERROR: return type mismatch in function declared to return integer
2023-08-19T16:54:17.536214321Z Detail: Actual return type is bigint.
2023-08-19T16:54:17.536218869Z Where: SQL function “total_memberships” [Failed SQL: (0) create or replace function total_memberships()
2023-08-19T16:54:17.536223428Z returns integer as
2023-08-19T16:54:17.536227636Z $$
2023-08-19T16:54:17.536231964Z select count() as result from NF_MEMBERSHIP;
2023-08-19T16:54:17.536236392Z $$
2023-08-19T16:54:17.536240510Z language SQL]
2023-08-19T16:54:17.536244387Z at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.28.jar!/:5.3.28]
I checked the PostgreSQL documentation and it confirms that the return type for the “select count(*) …” statement is bigint.
I then tried to change the return type to bigint with a new changeLog and changeSet(s)…
For example:
<changeSet id="1" author="nf" dbms="postgresql">
<createProcedure procedureName="total_memberships">
create or replace function total_memberships()
returns bigint as
$$
select count(*) as result from NF_MEMBERSHIP;
$$
language SQL;
</createProcedure>
</changeSet>
but Liquibase threw the following exception stating that the procedure cannot be replaced and must be dropped in order to change the return type.
Running Changeset: com/company/nf/liquibase/changelog/2023/08/19-215104-50849d54.xml::1::nf
[2023-08-19 21:52:05] INFO [liquibase.lockservice] Successfully released change log lock
Unexpected error running Liquibase: Migration failed for change set com/company/nf/liquibase/changelog/2023/08/19-215104-50849d54.xml::1::nf:
Reason: liquibase.exception.DatabaseException: ERROR: cannot change return type of existing function
Hint: Use DROP FUNCTION total_memberships() first. [Failed SQL: (0) create or replace function total_memberships()
returns integer as
$$
select count(*) as result from NF_MEMBERSHIP;
$$
language SQL]
For more information, please use the --logLevel flag
Please note that in the above exception information it says “Use DROP FUNCTION” and not “Use DROP PROCEDURE” and also “… existing function” and not “… existing procedure”.
I then tried to drop the procedure with a new changeLog and changeSet but I received the following new exception stating that my total_memberships procedure is not a procedure:
[2023-08-19 20:02:45] INFO [liquibase.changelog] Reading from public.databasechangelog
Running Changeset: com/company/nf/liquibase/changelog/2023/08/19-195137-50849d54.xml::1::nf
[2023-08-19 20:02:46] INFO [liquibase.lockservice] Successfully released change log lock
Unexpected error running Liquibase: Migration failed for change set com/company/nf/liquibase/changelog/2023/08/19-195137-50849d54.xml::1::nf:
Reason: liquibase.exception.DatabaseException: ERROR: public.total_memberships() is not a procedure [Failed SQL: (0) DROP PROCEDURE public.total_memberships]
For more information, please use the --logLevel flag
[2023-08-19 20:02:46] SEVERE [liquibase.integration] Unexpected error running Liquibase: Migration failed for change set com/company/nf/liquibase/changelog/2023/08/19-195137-50849d54.xml::1::nf:
Reason: liquibase.exception.DatabaseException: ERROR: public.total_memberships() is not a procedure [Failed SQL: (0) DROP PROCEDURE public.total_memberships]
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set com/company/nf/liquibase/changelog/2023/08/19-195137-50849d54.xml::1::nf:
Reason: liquibase.exception.DatabaseException: ERROR: public.total_memberships() is not a procedure [Failed SQL: (0) DROP PROCEDURE public.total_memberships]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126)
I then checked directly in the database and saw that my procedures are registered as functions and not as procedures.
I then decided to try and drop it as a function but Studio would not accept the syntax and apparently a Liquibase PRO license is required to drop a function.
I gave up at this point… and as a workaround, I manually replaced the return types in my existing changeLogs and then recreated my database; since I do not have any production databases currently; I was able to do this locally (MacOS development) and in Heroku and Azure. The end result works correctly in all three environments.
So, there appears to be 3 errors (bugs) here:
- My original ChangeLog executed locally in Studio (MacOS) and in Heroku, when it should not have; it should have thrown the same error that Azure did; the error that Azure threw is correct for PostgreSQL.
- The procedures should be stored as procedures and not as functions.
- It must be possible to drop a procedure with a normal changeSet; this should be possible after correcting point number 2 directly above. (Of course, the procedure(s) can then be added again with the correct return type with a new changeSet.)
I hope that this information is clear and helpful. Thank you in advance for any improvements in this area.
Best regards
Chris