playframework
[2.7.x][5.0.x] Evolution fails for auto-generated script contains create procedures script
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
java version "11.0.1" 2018-10-16 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.1+13-LTS)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.1+13-LTS, mixed mode)
Library Dependencies
I tried to use MySQL and MariaDB:
"org.mariadb.jdbc" % "mariadb-java-client" % "2.3.0",
"mysql" % "mysql-connector-java" % "8.0.13",
Expected Behavior
- Create entities for ebean and configure it in the application.conf file:
db.default.driver = com.mysql.cj.jdbc.Driver
db.default.url = "jdbc:mysql://localhost:3306/mydb?zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false"
db.default.username = "username"
db.default.password = "passowrd"
play.evolutions.db.default.enabled = true
play.evolutions.db.default.autoApply = true
play.evolutions.db.default.autoApplyDowns = false
Also tried:
db.default.driver = org.mariadb.jdbc.Driver
db.default.url = "jdbc:mariadb://localhost:3306/mydb?zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false"
- Run application waiting for 1.sql to auto-generate the script, the first line of 1.sql should be:
# --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions
- sql script generated and it should be run the application without any issue with the newly created script.
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:
-- 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
$$
The console output:
2018-12-13 14:27:10 +0200 80902 ERROR p.a.d.e.DefaultEvolutionsApi - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter $$
--
-- PROCEDURE: usp_ebean_drop_foreign_keys TABLE, COLUMN
-- delet' at line 1 [ERROR:1064, SQLSTATE:42000]
2018-12-13 14:27:11 +0200 81207 ERROR application -
! @7a8adme52 - Internal server error, for (GET) [/] ->
play.api.db.evolutions.InconsistentDatabase: Database 'default' is in an inconsistent state![An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.]
at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3(EvolutionsApi.scala:293)
at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3$adapted(EvolutionsApi.scala:278)
at play.api.db.evolutions.DatabaseEvolutions.executeQuery(EvolutionsApi.scala:325)
at play.api.db.evolutions.DatabaseEvolutions.checkEvolutionsState(EvolutionsApi.scala:278)
at play.api.db.evolutions.DatabaseEvolutions.evolve(EvolutionsApi.scala:247)
at play.api.db.evolutions.DefaultEvolutionsApi.evolve(EvolutionsApi.scala:97)
at play.api.db.evolutions.ApplicationEvolutions.$anonfun$runEvolutions$1(ApplicationEvolutions.scala:63)
at play.api.db.evolutions.ApplicationEvolutions.withLock(ApplicationEvolutions.scala:100)
at play.api.db.evolutions.ApplicationEvolutions.runEvolutions(ApplicationEvolutions.scala:49)
at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$1(ApplicationEvolutions.scala:42)
at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$1$adapted(ApplicationEvolutions.scala:42)
at scala.collection.immutable.List.foreach(List.scala:388)
at play.api.db.evolutions.ApplicationEvolutions.start(ApplicationEvolutions.scala:42)
at play.api.db.evolutions.ApplicationEvolutions.<init>(ApplicationEvolutions.scala:151)
at play.api.db.evolutions.ApplicationEvolutionsProvider.get$lzycompute(EvolutionsModule.scala:50)
at play.api.db.evolutions.ApplicationEvolutionsProvider.get(EvolutionsModule.scala:49)
at play.api.db.evolutions.ApplicationEvolutionsProvider.get(EvolutionsModule.scala:40)
at com.google.inject.internal.ProviderInternalFactory.provision(ProviderInternalFactory.java:81)
at com.google.inject.internal.BoundProviderFactory.provision(BoundProviderFactory.java:72)
at com.google.inject.internal.ProviderInternalFactory.circularGet(ProviderInternalFactory.java:61)
at com.google.inject.internal.BoundProviderFactory.get(BoundProviderFactory.java:62)
at com.google.inject.internal.ProviderToInternalFactoryAdapter$1.call(ProviderToInternalFactoryAdapter.java:46)
at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1092)
at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:194)
at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:41)
at com.google.inject.internal.InternalInjectorCreator$1.call(InternalInjectorCreator.java:205)
at com.google.inject.internal.InternalInjectorCreator$1.call(InternalInjectorCreator.java:199)
at com.google.inject.internal.InjectorImpl.callInContext(InjectorImpl.java:1085)
at com.google.inject.internal.InternalInjectorCreator.loadEagerSingletons(InternalInjectorCreator.java:199)
at com.google.inject.internal.InternalInjectorCreator.injectDynamically(InternalInjectorCreator.java:180)
at com.google.inject.internal.InternalInjectorCreator.build(InternalInjectorCreator.java:110)
at com.google.inject.Guice.createInjector(Guice.java:99)
at com.google.inject.Guice.createInjector(Guice.java:84)
at play.api.inject.guice.GuiceBuilder.injector(GuiceInjectorBuilder.scala:185)
at play.api.inject.guice.GuiceApplicationBuilder.build(GuiceApplicationBuilder.scala:137)
at play.api.inject.guice.GuiceApplicationLoader.load(GuiceApplicationLoader.scala:21)
at play.core.server.DevServerStart$$anon$1.$anonfun$reload$3(DevServerStart.scala:174)
at play.utils.Threads$.withContextClassLoader(Threads.scala:21)
at play.core.server.DevServerStart$$anon$1.reload(DevServerStart.scala:171)
at play.core.server.DevServerStart$$anon$1.get(DevServerStart.scala:124)
at play.core.server.AkkaHttpServer.handleRequest(AkkaHttpServer.scala:241)
at play.core.server.AkkaHttpServer.$anonfun$createServerBinding$1(AkkaHttpServer.scala:138)
at akka.stream.impl.fusing.MapAsyncUnordered$$anon$26.onPush(Ops.scala:1304)
at akka.stream.impl.fusing.GraphInterpreter.processPush(GraphInterpreter.scala:519)
at akka.stream.impl.fusing.GraphInterpreter.processEvent(GraphInterpreter.scala:482)
at akka.stream.impl.fusing.GraphInterpreter.execute(GraphInterpreter.scala:378)
at akka.stream.impl.fusing.GraphInterpreterShell.runBatch(ActorGraphInterpreter.scala:588)
at akka.stream.impl.fusing.GraphInterpreterShell$AsyncInput.execute(ActorGraphInterpreter.scala:472)
at akka.stream.impl.fusing.GraphInterpreterShell.processEvent(ActorGraphInterpreter.scala:563)
at akka.stream.impl.fusing.ActorGraphInterpreter.akka$stream$impl$fusing$ActorGraphInterpreter$$processEvent(ActorGraphInterpreter.scala:745)
at akka.stream.impl.fusing.ActorGraphInterpreter$$anonfun$receive$1.applyOrElse(ActorGraphInterpreter.scala:760)
at akka.actor.Actor.aroundReceive(Actor.scala:517)
at akka.actor.Actor.aroundReceive$(Actor.scala:515)
at akka.stream.impl.fusing.ActorGraphInterpreter.aroundReceive(ActorGraphInterpreter.scala:670)
at akka.actor.ActorCell.receiveMessage(ActorCell.scala:588)
at akka.actor.ActorCell.invoke(ActorCell.scala:557)
at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:258)
at akka.dispatch.Mailbox.run(Mailbox.scala:225)
at akka.dispatch.Mailbox.exec(Mailbox.scala:235)
at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
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: https://github.com/playframework/playframework/issues/8879
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.
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.
I don't know Ebean really well so I cannot tell what is the suggested way to create/run the migration these days.
Ebean has it's own database migrations support (Flyway like but with some enhancements like "Initial repeatable migrations".
Maybe the paths forward are to:
- Adjust the DDL parser/runner that Play is using here to handle stored procedures (could look at the Ebean code if you like)
- Look at using Ebean's tools / ebean-migration ... to generate and run migrations. That would mean people would also be able to use the ebean-test-config docker testing to run tests against the migrations as well.
Cheers, Rob.
Thanks for joining the conversation, @rbygrave.
Ebean started using procedures to assist with the migration DDL for mysql and sql server in particular
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
So are we moving back to play for the evolution parse?
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 [2.7.0-RC8][5.0.0-RC2] in the title and assumed it was related with that other issue.
Reopening...
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?
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.
Also working on 2.7, is there a chance this is getting fixed withing the next time?
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 delimiter $$ even it is not correct, with:
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:
db.default.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
db.default.url = "jdbc:sqlserver://SRV_IP:1433;databaseName=DB"
db.default.jdbc-url = "jdbc:sqlserver://SRV_IP:1433;databaseName=DB"
db.default.username = "SA"
db.default.password = "PASSWORD"
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 delimiter $$ is correct, but SqlServer is not even correct to use delimiter $$:
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.
@hiroya8649
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.
@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. Thanks again!
Any fix planned for this issue? I wanted to upgrade from Play 2.5 to 2.7 but I'm gonna give up...
Any fix planned for this issue?
No, this is not a priority for us now. But contributions are welcome.
Any fix planned for this issue?
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 11.45.1: #192
Starting with that ebean version no stored procedure will be generated automatically anymore by default: https://github.com/ebean-orm/ebean/issues/1802 and https://github.com/ebean-orm/ebean/pull/1815
@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
Update by @mkurz: Double post, see https://github.com/playframework/playframework/discussions/11651
i use source /Users/xx/workspace/xx/conf/evolutions/default/1.sql in mysql client, the generated sql is correct, the tables created, but evolution has not been applied properly.
addSbtPlugin("com.typesafe.play" % "sbt-plugin" % "2.8.19") addSbtPlugin("com.typesafe.play" % "sbt-play-ebean" % "6.2.0") java version "17.0.6" 2023-01-17 LTS mysql version 8.0.32
An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved. -
We got the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter ## -- -- PROCEDURE: usp_ebean_drop_foreign_keys TABLE, COLUMN -- delet' at line 1 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:
