[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

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: https://github.com/playframework/playframework/issues/8879

Reproduce the failure with the linked play-with-mapstruct fork and the application.conf settings, then trace how the generated 1.sql is executed through the evolution path shown in Evolutions.scala. Check handling of MySQL delimiter and CREATE PROCEDURE statements; done means the auto-generated evolution runs successfully for the reported MySQL setup.