MySQL Script execution failed if LOOP statement at PROCEDURE used

[mgaidamak]

I'm trying to test PROCEDURE at MySQL testcontainers. This is an example from https://dev.mysql.com/doc/refman/5.7/en/statement-labels.html

Add this to testcontainers-java/modules/mysql/src/test/resources/somepath/init_mysql.sql

CREATE PROCEDURE doiterate(p1 INT)
  BEGIN
    label1: LOOP
      SET p1 = p1 + 1;
      IF p1 < 10 THEN
        ITERATE label1;
      END IF;
      LEAVE label1;
    END LOOP label1;
  END;

CREATE PROCEDURE dowhile()
  BEGIN
    DECLARE v1 INT DEFAULT 5;
    WHILE v1 > 0 DO
      SET v1 = v1 - 1;
    END WHILE;
  END;

CREATE PROCEDURE dorepeat(p1 INT)
  BEGIN
    SET @x = 0;
    REPEAT
      SET @x = @x + 1;
    UNTIL @x > p1 END REPEAT;
  END;

And run org.testcontainers.jdbc.mysql.MySQLJDBCDriverTest
It wil be failed

Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (somepath/init_mysql.sql:8): CREATE PROCEDURE doiterate(p1 INT) BEGIN
    label1: LOOP
      SET p1 = p1 + 1;
      IF p1 < 10 THEN
        ITERATE label1;
      END IF
	at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:49)
	at org.testcontainers.delegate.AbstractDatabaseDelegate.execute(AbstractDatabaseDelegate.java:34)
	at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:362)
	... 43 more
Caused by: java.sql.SQLSyntaxErrorException: 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 '' at line 6
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:42)

Than i run

$ docker run --name=mysql1 -d mysql/mysql-server:5.7.22

It works fine

mysql> DELIMITER $$
mysql> CREATE PROCEDURE doiterate(p1 INT)
    -> BEGIN
    ->     label1: LOOP
    ->         SET p1 = p1 + 1;
    ->         IF p1 < 10 THEN ITERATE label1; END IF;
    ->         LEAVE label1;
    ->     END LOOP label1;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE dowhile()
    -> BEGIN
    ->     DECLARE v1 INT DEFAULT 5;
    ->     WHILE v1 > 0 DO
    ->       SET v1 = v1 - 1;
    ->     END WHILE;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL dowhile();
Query OK, 0 rows affected (0.01 sec)

Can you explain me what is wrong?
Or why it not supported.

p.s. PROCEDURE whithout LOOP statements works fine.

[mgaidamak]

I think org.testcontainers.ext.ScriptUtils#splitSqlScript can't process END of LOOP statements correctly. trying to fix it with ScriptSplittingTest

    @Test
    public void splitProcedures() {
        String script = "CREATE PROCEDURE dorepeat(p1 INT)\n"
                + "BEGIN\n"
                + "    DECLARE v1 INT DEFAULT 0;\n"
                + "    REPEAT\n"
                + "        SET v1 = v1 + 1;\n"
                + "    UNTIL v1 > p1\n"
                + "        END REPEAT;\n"
                + "END;\n"
                + "\n"
                + "CREATE PROCEDURE dowhile()\n"
                + "BEGIN\n"
                + "    DECLARE v1 INT DEFAULT 5;\n"
                + "    WHILE v1 > 0\n"
                + "        DO\n"
                + "            SET v1 = v1 - 1;\n"
                + "        END WHILE;\n"
                + "END;";
        List<String> expected = asList(
            "CREATE PROCEDURE dorepeat(p1 INT)\n"
                + "BEGIN\n"
                + "    DECLARE v1 INT DEFAULT 0;\n"
                + "    REPEAT\n"
                + "        SET v1 = v1 + 1;\n"
                + "    UNTIL v1 > p1\n"
                + "        END REPEAT;\n"
                + "END;",
            "CREATE PROCEDURE dowhile()\n"
                + "BEGIN\n"
                + "    DECLARE v1 INT DEFAULT 5;\n"
                + "    WHILE v1 > 0\n"
                + "        DO\n"
                + "            SET v1 = v1 - 1;\n"
                + "        END WHILE;\n"
                + "END;");
        splitAndCompare(script, expected);
    }