[2.7.x][5.0.x] Evolution fails for auto-generated script contains create procedures script

[almothafar]

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

1. 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"

2. 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

3. 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: playframework/playframework#8879

[marcospereira]

Okay, so, what is the modern way of doing migrations in Ebean these days?

[almothafar]

@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.

[marcospereira]

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?

[rbygrave]

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.

[marcospereira]

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.

[almothafar]

@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

[marcospereira]

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.

[octonato]

@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.

[mkurz]

@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).

[octonato]

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...

[AlbaroPereyra]

The issues still exists.