[2.6.20] Evolutions fails for auto-generated script contains create procedures script #8879
Comments
|
You have to escape the semicolons inside your procedures by using double semicolons (
For you script that means: -- init script create procs
-- Inital script to create stored procedures etc for mysql platform
DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys;
delimiter $$
--
-- 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;
delimiter $$
--
-- 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
$$ |
|
@mkurz as I said it in my issue, the script is AUTO-generated, from Ebean DLL, I can do that change but I need to stop that auto-generate, also, the script run inside external console without issues! If you support Ebean so you need to deal with this, nothing wrong with this part from Ebean side, as I said the script is running correctly without play, but inside play framework it fails, something must be solved here. |
|
Hmm... I don't use ebean myself, I guess you probably have to modify the generated ebean scripts for now (add the |
|
So please try to use ebean and see how it is really difficult during development to change that each time you do the change, the steps that I want to do each time for manually editing is:
It is not really fun using that, in production evolution already controlled, but during development, it is really hard, it is definitely an issue to solve anyway. If I keep the code mentioned in point no.1 and do the change manually, evolution will be triggered as the database is in The other solution is just never to use auto-generated code at all and write each line of script manually, and that adds a lot of time for development and maintenance. |
almothafar
changed the title
|
@mkurz may you open the issue for now until we get some final answer, I don't think it is out of scope as it is a feature added to ebean must be handled. |
|
Hi @almothafar, Could you please reopen this in playframework/play-ebean repository (and of course reference the conversation here)? This won't be solved here in Play itself but in play-ebean. The explanation that @mkurz gave is how Play evolutions work, and there is nothing wrong with it, but with the Ebean integration (note I'm not saying there is something wrong with Ebean). It would be good to have a small project to reproduce the problem also, using the exact Ebean version you are using, with a similar configuration (I was not even aware that Ebean creates procedures). That all said, contributions are very welcomed here. Best. |
|
@marcospereira sure thing I will do that, ASAP, but do you want me to close this after I reopen this in playframework/play-ebean project? |
|
Yes, please. |
|
OK after I got already a repo with almost the same structure and I updated it with most changes I got in real one, it seems the problem is not happening with official https://github.com/playframework/play-ebean repo, but happens with forked version https://github.com/payintech/play-ebean which is working with JDK 11 and got latest versions for ebean, I'm not sure if that will happen with official one or it is something added from payintech, I'll open the issue in their fork and mention this conversation. Here is my repo to reproduce the issue: https://github.com/almothafar/play-with-mapstruct I'll Test play 2.7-RC8 now. |
|
OK even with the official plugin it is happening for 2.7.0-RC8/5.0.0-RC2 |
|
@marcospereira I'm closing this, issue opened in https://github.com/playframework/play-ebean |
|
Just for the record here, replacing
|
|
You made a mistake: Do not use |
|
@mkurz yeah, I tried that too, I tried many ways I also tried to add |
|
|
|
Replace -- init script create procs
-- Inital script to create stored procedures etc for mysql platform
DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys;
DELIMITER "$$";
--
-- 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;
DELIMITER "$$";
--
-- 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
$$ |
Play Version
2.6.20
API
Java
Operating System (Ubuntu 15.10 / MacOS 10.10 / Windows 10)
WIndows 10 64-bit Enterprise
JDK (Oracle 1.8.0_72, OpenJDK 1.8.x, Azul Zing)
Library Dependencies
I tried to use MySQL, MariaDB, and Postgres:
Expected Behavior
Also tried:
Actual Behavior
The problem is that script contains
CREATE PROCEDURE!If I run the file manually it will be executed without any issues, but play evolution service can't run it, and it fails.
The first part of the file will be:
The console output:
The text was updated successfully, but these errors were encountered: