Evolution fails for auto-generated script contains create procedures script

[almothafar]

The issue confusing but seems play's version not going to fix this issue soon, this is because a new feature added in ebean version 11.19.1 releases of it is here: https://github.com/ebean-orm/ebean/releases/tag/ebean-11.19.1

Full details and discussion with play team done in 2 tickets:
playframework#166
And
playframework/playframework#8879

Also, you may try to use this branch to reproduce: https://github.com/almothafar/play-with-mapstruct/tree/play-2.6.x-payintech

[stogo]

I have the same issue. I'm using this as a workaround.
Same instructions than playframework/playframework#8879 but remove custom delimiter parts and replace all ; with ;; inside procedure definition and add ; after END

Easier to just replace procedure parts in the generated sql file with this.

-- init script create procs
-- Inital script to create stored procedures etc for mysql platform
DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys;

--
-- PROCEDURE: usp_ebean_drop_foreign_keys TABLE, COLUMN
-- deletes all constraints and foreign keys referring to TABLE.COLUMN
--
CREATE PROCEDURE usp_ebean_drop_foreign_keys(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;;
    DECLARE c_fk_name CHAR(255);;
    DECLARE curs CURSOR FOR SELECT CONSTRAINT_NAME from information_schema.KEY_COLUMN_USAGE
                            WHERE TABLE_SCHEMA = DATABASE() and TABLE_NAME = p_table_name and COLUMN_NAME = p_column_name
                              AND REFERENCED_TABLE_NAME IS NOT NULL;;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;;

    OPEN curs;;

    read_loop: LOOP
        FETCH curs INTO c_fk_name;;
        IF done THEN
            LEAVE read_loop;;
        END IF;;
        SET @sql = CONCAT('ALTER TABLE ', p_table_name, ' DROP FOREIGN KEY ', c_fk_name);;
        PREPARE stmt FROM @sql;;
        EXECUTE stmt;;
    END LOOP;;

    CLOSE curs;;
END;

DROP PROCEDURE IF EXISTS usp_ebean_drop_column;

--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column and ensures that all indices and constraints are dropped first
--
CREATE PROCEDURE usp_ebean_drop_column(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255))
BEGIN
    CALL usp_ebean_drop_foreign_keys(p_table_name, p_column_name);;
    SET @sql = CONCAT('ALTER TABLE ', p_table_name, ' DROP COLUMN ', p_column_name);;
    PREPARE stmt FROM @sql;;
    EXECUTE stmt;;
END;

[almothafar]

@stogo yes I'm using the same workaround but the problem is when you use auto generate (especially when you start a new project and you don't have a final form yet) and you keep auto-generate, it becomes annoying.

[cyrilfr]

Any chance to be fixed?

[allnash]

Same here. Frustrating!

My work around was to downgrade to 18.10. I am on play 2.6.20

[cyrilfr]

It will be fixed by ebean: ebean-orm/ebean#1802

[almothafar]

@cyrilfr will be fixed for MySQL what about the rest of DBMSs?

[cyrilfr]

@almothafar this is another issue. Here the problem is the DDL procedures that are not part of SQL language. You'll be able to disable this.

[allnash]

@almothafar When is the fix for this getting merged?
@cyrilfr

[allnash]

@almothafar actually this is fixed in ->

addSbtPlugin("com.typesafe.sbt" % "sbt-play-ebean" % "6.0.0")
Play 2.8.2

[mkurz]

Actually this is fixed because of ebean version 11.45.1. Starting with that ebean version no stored procedure will be generated automatically anymore by default: ebean-orm/ebean#1802 and ebean-orm/ebean#1815

[almothafar]

@mkurz That's only for MySQL, for MSSQL here it is still doing it

[RafalSladek]

do we have any news with this bug?

i have same issue with evolution being not able to create store procedure (using play 2.9) and no ebean

[mkurz]

i have same issue with evolution being not able to create store procedure (using play 2.9) and no ebean

what do you mean with "...no ebean"? You can create stored procedures with Play evolution's, you just have to escape ; with ;; within prodecure body's, see https://www.playframework.com/documentation/latest/Evolutions#Evolutions-scripts

[mkurz]

I came up with a potential solution, see

• [2.7.x][5.0.x] Evolution fails for auto-generated script contains create procedures script playframework/play-ebean#166 (comment)
• Add metadata to stored procedures for 3rd party libs ebean-orm/ebean#3408