play evolution can't execute sql correctly

[zhaoenhz]

Play Version

2.8.19

API

Java

Operating System

macos

JDK

17.0.6

Library Dependencies

addSbtPlugin("com.typesafe.play" % "sbt-play-ebean" % "6.2.0")
mysql  8.0.32 
"mysql" % "mysql-connector-java" % "8.0.32"

Expected Behavior

I can use mysql client to import the generated sql file correctly, all table created, but play evolution cannot

Actual Behavior

web browser:

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

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:
1-- Rev:1,Ups - 25cef54
2
3-- init script create procs
4-- Inital script to create stored procedures etc for mysql platform
5DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys;
6
7delimiter $$
8--
9-- PROCEDURE: usp_ebean_drop_foreign_keys TABLE, COLUMN
10-- deletes all constraints and foreign keys referring to TABLE.COLUMN
11--
12CREATE PROCEDURE usp_ebean_drop_foreign_keys(IN p_table_name VARCHAR(255), IN p_column_name VARCHAR(255))
13BEGIN
14DECLARE done INT DEFAULT FALSE;
15DECLARE c_fk_name CHAR(255);
16DECLARE curs CURSOR FOR SELECT CONSTRAINT_NAME from information_schema.KEY_COLUMN_USAGE
17WHERE TABLE_SCHEMA = DATABASE() and TABLE_NAME = p_table_name and COLUMN_NAME = p_column_name
18AND REFERENCED_TABLE_NAME IS NOT NULL;
19DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
....

log:

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

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:
1-- Rev:1,Ups - 25cef54
2
3-- init script create procs
4-- Inital script to create stored procedures etc for mysql platform
5DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys;
6
7delimiter $$
8--
9-- PROCEDURE: usp_ebean_drop_foreign_keys TABLE, COLUMN
10-- deletes all constraints and foreign keys referring to TABLE.COLUMN
11--
2023-02-02 18:16:01 INFO  play.api.db.DefaultDBApi  Database [default] initialized
2023-02-02 18:16:01 INFO  play.api.db.HikariCPConnectionPool  Creating Pool for datasource 'default'
2023-02-02 18:16:01 DEBUG p.a.d.e.DefaultEvolutionsApi  Execute: -- init script create procs
-- Inital script to create stored procedures etc for mysql platform
DROP PROCEDURE IF EXISTS usp_ebean_drop_foreign_keys
2023-02-02 18:16:01 DEBUG p.a.d.e.DefaultEvolutionsApi  Finished in 1ms
2023-02-02 18:16:01 DEBUG p.a.d.e.DefaultEvolutionsApi  Execute: 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
2023-02-02 18:16:01 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]
2023-02-02 18:16:01 DEBUG p.a.l.concurrent.ActorSystemProvider  Starting application default Akka system: application
2023-02-02 18:16:01 DEBUG play.api.http.HttpConfiguration  Generated dev mode secret bf090e738fa40b3d869c4a2e003da416 for app at file:/Users/zhao/workspace/wecan/target/scala-2.13/classes/application.conf
2023-02-02 18:16:01 ERROR play.api.http.DevHttpErrorHandler  

! @80f6opjje - Internal server error, for (GET) [/@evolutions/resolve/default/1?redirect=http%3A%2F%2Flocalhost%3A9000%2F%40evolutions%2Fresolve%2Fdefault%2F1%3Fredirect%3Dhttp%253A%252F%252Flocalhost%253A9000%252F%2540evolutions%252Fresolve%252Fdefault%252F1%253Fredirect%253Dhttp%25253A%25252F%25252Flocalhost%25253A9000%25252F%252540evolutions%25252Fresolve%25252Fdefault%25252F1%25253Fredirect%25253Dhttp%2525253A%2525252F%2525252Flocalhost%2525253A9000%2525252F%25252540evolutions%2525252Fresolve%2525252Fdefault%2525252F1%2525253Fredirect%2525253Dhttp%252525253A%252525252F%252525252Flocalhost%252525253A9000%252525252F%2525252540evolutions%252525252Fresolve%252525252Fdefault%252525252F1%252525253Fredirect%252525253Dhttp%25252525253A%25252525252F%25252525252Flocalhost%25252525253A9000%25252525252F%252525252540evolutions%25252525252Fresolve%25252525252Fdefault%25252525252F1%25252525253Fredirect%25252525253Dhttp%2525252525253A%2525252525252F%2525252525252Flocalhost%2525252525253A9000%2525252525252F] ->
 
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:307)
	at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3$adapted(EvolutionsApi.scala:291)
	at play.api.db.evolutions.DatabaseEvolutions.executeQuery(EvolutionsApi.scala:339)
	at play.api.db.evolutions.DatabaseEvolutions.checkEvolutionsState(EvolutionsApi.scala:291)
	at play.api.db.evolutions.DatabaseEvolutions.evolve(EvolutionsApi.scala:259)
	at play.api.db.evolutions.DefaultEvolutionsApi.evolve(EvolutionsApi.scala:103)
	at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$2(ApplicationEvolutions.scala:79)
	at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$2$adapted(ApplicationEvolutions.scala:66)
	at play.api.db.evolutions.ApplicationEvolutions$.$anonfun$runEvolutions$1(ApplicationEvolutions.scala:210)
	at play.api.db.evolutions.ApplicationEvolutions$.withLock(ApplicationEvolutions.scala:231)
	at play.api.db.evolutions.ApplicationEvolutions$.runEvolutions(ApplicationEvolutions.scala:201)
	at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$1(ApplicationEvolutions.scala:66)
	at play.api.db.evolutions.ApplicationEvolutions.$anonfun$start$1$adapted(ApplicationEvolutions.scala:61)
	at scala.collection.immutable.List.foreach(List.scala:333)
	at play.api.db.evolutions.ApplicationEvolutions.start(ApplicationEvolutions.scala:61)
	at play.api.db.evolutions.ApplicationEvolutions.<init>(ApplicationEvolutions.scala:109)
	at play.api.db.evolutions.ApplicationEvolutionsProvider.get$lzycompute(EvolutionsModule.scala:67)
	at play.api.db.evolutions.ApplicationEvolutionsProvider.get(EvolutionsModule.scala:60)
	at play.api.db.evolutions.ApplicationEvolutionsProvider.get(EvolutionsModule.scala:51)
	at com.google.inject.internal.ProviderInternalFactory.provision(ProviderInternalFactory.java:85)
	at com.google.inject.internal.BoundProviderFactory.provision(BoundProviderFactory.java:77)
	at com.google.inject.internal.ProviderInternalFactory.circularGet(ProviderInternalFactory.java:59)
	at com.google.inject.internal.BoundProviderFactory.get(BoundProviderFactory.java:61)
	at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
	at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:168)
	at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:39)
	at com.google.inject.internal.InternalInjectorCreator.loadEagerSingletons(InternalInjectorCreator.java:213)
	at com.google.inject.internal.InternalInjectorCreator.injectDynamically(InternalInjectorCreator.java:184)
	at com.google.inject.internal.InternalInjectorCreator.build(InternalInjectorCreator.java:111)
	at com.google.inject.Guice.createInjector(Guice.java:87)
	at com.google.inject.Guice.createInjector(Guice.java:78)
	at play.api.inject.guice.GuiceBuilder.injector(GuiceInjectorBuilder.scala:201)
	at play.api.inject.guice.GuiceApplicationBuilder.build(GuiceApplicationBuilder.scala:155)
	at play.api.inject.guice.GuiceApplicationLoader.load(GuiceApplicationLoader.scala:21)
	at play.core.server.DevServerStart$$anon$1.$anonfun$reload$2(DevServerStart.scala:193)
	at play.utils.Threads$.withContextClassLoader(Threads.scala:22)
	at play.core.server.DevServerStart$$anon$1.reload(DevServerStart.scala:185)
	at play.core.server.DevServerStart$$anon$1.get(DevServerStart.scala:148)
	at play.core.server.AkkaHttpServer.handleRequest(AkkaHttpServer.scala:310)
	at play.core.server.AkkaHttpServer.$anonfun$createServerBinding$1(AkkaHttpServer.scala:227)
	at akka.stream.impl.fusing.MapAsync$$anon$30.onPush(Ops.scala:1309)
	at akka.stream.impl.fusing.GraphInterpreter.processPush(GraphInterpreter.scala:542)
	at akka.stream.impl.fusing.GraphInterpreter.processEvent(GraphInterpreter.scala:496)
	at akka.stream.impl.fusing.GraphInterpreter.execute(GraphInterpreter.scala:390)
	at akka.stream.impl.fusing.GraphInterpreterShell.runBatch(ActorGraphInterpreter.scala:650)
	at akka.stream.impl.fusing.GraphInterpreterShell$AsyncInput.execute(ActorGraphInterpreter.scala:521)
	at akka.stream.impl.fusing.GraphInterpreterShell.processEvent(ActorGraphInterpreter.scala:625)
	at akka.stream.impl.fusing.ActorGraphInterpreter.akka$stream$impl$fusing$ActorGraphInterpreter$$processEvent(ActorGraphInterpreter.scala:800)
	at akka.stream.impl.fusing.ActorGraphInterpreter$$anonfun$receive$1.applyOrElse(ActorGraphInterpreter.scala:818)
	at akka.actor.Actor.aroundReceive(Actor.scala:537)
	at akka.actor.Actor.aroundReceive$(Actor.scala:535)
	at akka.stream.impl.fusing.ActorGraphInterpreter.aroundReceive(ActorGraphInterpreter.scala:716)
	at akka.actor.ActorCell.receiveMessage(ActorCell.scala:579)
	at akka.actor.ActorCell.invoke(ActorCell.scala:547)
	at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:270)
	at akka.dispatch.Mailbox.run(Mailbox.scala:231)
	at akka.dispatch.Mailbox.exec(Mailbox.scala:243)
	at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
	at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
	at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
	at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
	at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)

Reproducible Test Case

/Users/xx/workspace/xx/conf/evolutions/default/1.sql

# --- Created by Ebean DDL
# To stop Ebean DDL generation, remove this comment and start using Evolutions

# --- !Ups

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

I am pretty sure that is because you didn't escape the semicolons within the procedures your are trying to create.
Please read: https://www.playframework.com/documentation/2.8.x/Evolutions#Evolutions-scripts

Play splits your .sql files into a series of semicolon-delimited statements before executing them one-by-one against the database. So if you need to use a semicolon within a statement, escape it by entering ;; instead of ;. For example, INSERT INTO punctuation(name, character) VALUES ('semicolon', ';;');.

So for you that means all semicolons inside the procedure definition needs to be escaped with ;;:

...
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;; <------------------------------------------ SEE HERE
DECLARE c_fk_name CHAR(255);; <--------------------------------------------- SEE HERE

... and so on

CLOSE curs;; <-------------------------------------------------------------- SEE HERE
END
$$
...`

[zhaoenhz]

thank you very much. i escape it by entering ;; instead of ; and removed delimiter command, it works

delimiter is a command for SQL client, there is no need to use delimiter in JDBC.