[2.7.x][5.0.x] Evolution fails for auto-generated script contains create procedures script #166
Comments
|
Okay, so, what is the modern way of doing migrations in Ebean these days? |
|
@marcospereira not sure, you tell me 😁, that thing is just added with ebean version 11.19.1 and improved later as I see from history and releases here: https://github.com/ebean-orm/ebean/releases/tag/ebean-11.19.1 I think the current play version (2.6.20) use version 11.15.something. |
Play does not have a dependency on Ebean at all. This is why I asked to move the discussion here. I don't know Ebean really well so I cannot tell what is the suggested way to create/run the migration these days. I was not even aware that now Ebean needs such procedures. It looks like this is to handle the migrations itself, but I'm not sure. @almothafar do you have time to investigate this? |
|
Hi, Ebean started using procedures to assist with the migration DDL for mysql and sql server in particular (due to the relative limits with DDL on those databases). Since then Ebean has added the use of helper procedures to support Postgres partitioning (daily, monthly, yearly partitioning of tables with Postgres 10) but that is optional there for Postgres. The error you see is that the DDL parser/runner used with PlayEbean is not interpreting MySQL stored procedures with a delimiter.
Ebean has it's own database migrations support (Flyway like but with some enhancements like "Initial repeatable migrations". Maybe the paths forward are to:
Cheers, Rob. |
|
Thanks for joining the conversation, @rbygrave.
Is there a way to disable it? Asking since Play has its own way to run the migration, so I don't think the procedures would be useful here at first. The path you described sounds reasonable to me. It needs to happen in the context of this project instead of Play itself so changes should be made here. Also, we don't have the resources to make it happen now. So, contributions would be welcome here. |
|
@marcospereira sorry I meant play's one, meaning play-ebean not play itself. So are we moving back to play for the evolution parse? https://github.com/playframework/playframework/blob/683dc2c65d5daa463a3ffe2fb56157228cb8db8b/framework/src/play-jdbc-evolutions/src/main/scala/play/api/db/evolutions/Evolutions.scala#L59 |
No. The evolutions API is extensible in a way that Play Ebean can provide its bindings overriding the default one. This includes the evolutions parsing. |
|
@almothafar, this should be fixed on the lasted release 5.0.0-RC3 that is using Play 2.7.0-RC9. I'm closing it, therefore. |
|
@renatocaval Are you sure this is fixed? This has nothing to do with my evolution fixes. As I understand it this is a problem with ebean generating procedures and within that procudures the semicolon are not escaped (that has do be done manually). |
|
Oh, maybe I went to fast with that. I saw Reopening... |
|
The issues still exists. |
|
I also have this problem in Play 2.7.0 |
|
The issue is very annoying and makes 2.7 unusable for creating new projects, that require a lot of debugging. I had to downgrade to 2.6. |
|
I am unable to find a link to a post I read, regarding a workaround. I was unable to make it work but, at a glance it makes sense. Basically the post mentioned to skip using play-ebean creation by creating a basic access test using https://mvnrepository.com/artifact/io.ebean.test/ebean-test-config |
|
Also working on 2.7, is there a chance this is getting fixed withing the next time? Or should i also migrate to 2.6? Im kinda new to Play, is 2.7 even stable? |
Yes, it is. The problem here is not with Play itself, but with the integration between Play and Ebean, which is a separated module with its own issue tracker, releases, etc.
Depends on contributions if you want to accelerate it. The integration with Ebean is not a priority for us right now. |
|
OK, now I got the issue with MSSQL (Microsoft SqlServer) as well, it is adding package models;
import io.ebean.config.ServerConfig;
import io.ebean.event.ServerConfigStartup;
public class EbeanServerConfigStartup implements ServerConfigStartup {
public void onStart(ServerConfig serverConfig) {
serverConfig.setDatabasePlatformName("sqlserver17");
}
}And: Then first part of 1.sql: # --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions
# --- !Ups
-- init script create procs
-- Initial script to create stored procedures etc for sqlserver platform
-- create table-value-parameters
if not exists (select name from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint);
if not exists (select name from sys.types where name = 'ebean_float_tvp') create type ebean_float_tvp as table (c1 float);
if not exists (select name from sys.types where name = 'ebean_bit_tvp') create type ebean_bit_tvp as table (c1 bit);
if not exists (select name from sys.types where name = 'ebean_date_tvp') create type ebean_date_tvp as table (c1 date);
if not exists (select name from sys.types where name = 'ebean_time_tvp') create type ebean_time_tvp as table (c1 time);
if not exists (select name from sys.types where name = 'ebean_uniqueidentifier_tvp') create type ebean_uniqueidentifier_tvp as table (c1 uniqueidentifier);
if not exists (select name from sys.types where name = 'ebean_nvarchar_tvp') create type ebean_nvarchar_tvp as table (c1 nvarchar(max));
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_indices TABLE, COLUMN
-- deletes all indices referring to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_indices @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @indexName nvarchar(255)
BEGIN
DECLARE index_cursor CURSOR FOR SELECT i.name from sys.indexes i
join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
where i.object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @indexName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'drop index ' + @indexName + ' on ' + @tableName;
EXECUTE(@sql);
FETCH NEXT FROM index_cursor INTO @indexName
END;
CLOSE index_cursor;
DEALLOCATE index_cursor;
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_default_constraint TABLE, COLUMN
-- deletes the default constraint, which has a random name
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_default_constraint @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @tmp nvarchar(1000)
BEGIN
select @Tmp = t1.name from sys.default_constraints t1
join sys.columns t2 on t1.object_id = t2.default_object_id
where t1.parent_object_id = OBJECT_ID(@tableName) and t2.name = @columnName;
if @Tmp is not null EXEC('alter table ' + @tableName +' drop constraint ' + @tmp);
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_constraints TABLE, COLUMN
-- deletes constraints and foreign keys refering to TABLE.COLUMN
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_constraints @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
declare @constraintName nvarchar(255)
BEGIN
DECLARE name_cursor CURSOR FOR
SELECT cc.name from sys.check_constraints cc
join sys.columns c on c.object_id = cc.parent_object_id and c.column_id = cc.parent_column_id
where parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName
UNION SELECT fk.name from sys.foreign_keys fk
join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
and fkc.parent_object_id = fk.parent_object_id
join sys.columns c on c.object_id = fkc.parent_object_id and c.column_id = fkc.parent_column_id
where fkc.parent_object_id = OBJECT_ID(@tableName) AND c.name = @columnName;
OPEN name_cursor
FETCH NEXT FROM name_cursor INTO @constraintName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'alter table ' + @tableName + ' drop constraint ' + @constraintName;
EXECUTE(@sql);
FETCH NEXT FROM name_cursor INTO @constraintName
END;
CLOSE name_cursor;
DEALLOCATE name_cursor;
END
$$
delimiter $$
--
-- PROCEDURE: usp_ebean_drop_column TABLE, COLUMN
-- deletes the column annd ensures that all indices and constraints are dropped first
--
CREATE OR ALTER PROCEDURE usp_ebean_drop_column @tableName nvarchar(255), @columnName nvarchar(255)
AS SET NOCOUNT ON
declare @sql nvarchar(1000)
BEGIN
EXEC usp_ebean_drop_indices @tableName, @columnName;
EXEC usp_ebean_drop_default_constraint @tableName, @columnName;
EXEC usp_ebean_drop_constraints @tableName, @columnName;
set @sql = 'alter table ' + @tableName + ' drop column ' + @columnName;
EXECUTE(@sql);
END
$$
Any idea? I think for PG
|
|
Hi. I'm new to play (and also java). I met this problem too and felt very upset because I spent almost two days to try. Then I realize it has been an issue now. I'm now considering downgrade or giving up using play. |
|
I would downgrade if I were you. I continued my development in 2.6 and thus far the only disadvantage I foresee is having to refactor the code to the latest version once this is fixed. |
|
@AlbaroPereyra Thanks for advice. I found someone said there is the same issue in 2.6.20,.May I ask what version of play / play-ebean do you use? |
|
I’m using version 2.6, I installed the latest 2.6 and ebeans plugin 2 months ago. I will login to my server and let you know exactly what version that is later. |
almothafar
changed the title
|
@hiroya8649 I’m using Play 2.6.21 and sbt-play-ebean 4.1.3 Let me what files you would like to see to help you get up and running. I am planing to open source this project once I am done with it anyways. |
|
@AlbaroPereyra Sorry for late. I have set up a new project with the version you said and got it working yesterday. |
|
Any fix planned for this issue? I wanted to upgrade from Play 2.5 to 2.7 but I'm gonna give up... |
No, this is not a priority for us now. But contributions are welcome. |
I would love to contribute but my level in Scala programing isn't good enough. What's the preferred ORM for Play 2.7 so? Hibernate? |
|
This is fixed now with latest play-ebean, because it upgrades ebean to version |
|
@mkurz That's only for MySQL, for MSSQL here it is still doing it, also no need to spread comments to all tickets :) |
|
Hi, this seems to still persist in the latest 6.2.0-RC7 version. I am just using a plain play project (2.8.16) with play-ebean, and the existence of jaxb in the classpath makes ebean create the evolutions with procedures and delimites |
This comment was marked as duplicate.
This comment was marked as duplicate.
|
Hi, was an updated solution or workaround for this ever found? appears to still persist in latest version 8.3.0. |
|
No solution so far. -- !Play-Evolutions: KEEP SEMICOLONS START
// semicolons within this sql block should be kept as they are
-- !Play-Evolutions: KEEP SEMICOLONS ENDFor ebean that would mean: CREATE PROCEDURE usp_ebean_drop_foreign_keys(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255))
BEGIN
-- !Play-Evolutions: KEEP SEMICOLONS START
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;
-- !Play-Evolutions: KEEP SEMICOLONS END
END
$$I just opened to discuss this approach with @rbygrave, lets see how this turns out. |
Play Version
2.7.0-RC8 - until now 2.7.1
API
Java
Operating System (Ubuntu 15.10 / MacOS 10.10 / Windows 10)
WIndows 10 64-bit Enterprise
JDK
Library Dependencies
I tried to use MySQL and MariaDB:
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:
Reproducible Test Case
Can be reproduced with my fork here: https://github.com/almothafar/play-with-mapstruct/tree/play-2.7.x
More info with full details provided here: playframework/playframework#8879
The text was updated successfully, but these errors were encountered: