Change MySQL DDL to not use helper stored procedures by default - was auto-generated script containing create procedures fails

[cyrilfr]

I experience this issue with the Ebean SBT plugin while running the auto-generated script.

The script is actually produced by the plugin calling this method:

public static String generateEvolutionScript(EbeanServer server) {
    try {
        SpiEbeanServer spiServer = (SpiEbeanServer) server;
        CurrentModel ddl = new CurrentModel(spiServer);

        String ups = ddl.getCreateDdl();
        String downs = ddl.getDropAllDdl();

        if (ups == null || ups.trim().isEmpty()) {
            return null;
        }

        return
            "# --- Created by Ebean DDL\r\n" +
            "# To stop Ebean DDL generation, remove this comment and start using Evolutions\r\n" +
            "\r\n" +
            "# --- !Ups\r\n" +
            "\r\n" +
            ups +
            "\r\n" +
            "# --- !Downs\r\n" +
            "\r\n" +
            downs;
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    }
}

from the EbeanDynamicEvolutions.java file.

There is a syntax error when I try to run the SQL script manually:

Syntax error near 'delimiter' at line 3

[rbygrave]

Was this not fixed by ebean-orm/ebean-migration#69 ?

[rbygrave]

@almothafar Can you add a comment please?

[almothafar]

What is your DBMS @cyrilfr?
For me, I worked with PG and MSSQL and workarounds working fine for me.

Workarounds mentioned already in playframework/play-ebean#166

If you are using SQL Server, with the versions of 11.41.4 or later for ebean-orm and 11.19.1 or later for ebean-migration (and BTW play-ebean does not use these versions, it is sitting on older versions), you should have GO and no delimiters! and still, you need to do manual workarounds or run the script manually.

[cyrilfr]

@rbygrave I've this issue with the last version of the plugin and the last version of ebean.
@almothafar I've tried with MySQL 5.7.27. phpMyAdmin detect the syntax error.

I've tried the workaround by escaping ; by ;; in the procedures and replacing delimiter $$ by DELIMITER "$$" with no luck.

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 ';
DECLARE c_fk_name CHAR(255);;
DECLARE curs CURSOR FOR SELECT CONSTRAINT_' at line 3

[almothafar]

As far as I know, MySQL supports DELIMITER and it is documented, I don't have any instance running for MySQL to try and a little busy these days to start a new project with docker instance, but I see in your second error is different from the first one, am I wrong?

[cyrilfr]

As far as I know, MySQL supports DELIMITER and it is documented, I don't have any instance running for MySQL to try and a little busy these days to start a new project with docker instance, but I see in your second error is different from the first one, am I wrong?

Yes, the second one occurs when I try the workaround (as you can see there is ;; in the error).

[almothafar]

What about the raw script generated by ebean without any workarounds? the workaround should be applied if you want to use play-ebean to apply script, but if you want to do that manually using console or SQL tool, you need to use the raw script without modification, and then later for evolution, you tell Play the matter is resolved.

[cyrilfr]

What about the raw script generated by ebean without any workarounds? the workaround should be applied if you want to use play-ebean to apply script, but if you want to do that manually using console or SQL tool, you need to use the raw script without modification, and then later for evolution, you tell Play the matter is resolved.

It says

Syntax error near 'delimiter' at line 3

as you can see on my screenshot, phpMyAdmin underlines syntax errors.

[cyrilfr]

According to this stackoverflow answer:

Note that the DELIMITER keyword is a function of the command line mysql client (and some other clients) only and not a regular MySQL language feature. It won't work if you tried to pass it through a programming language API to MySQL. Some other clients like PHPMyAdmin have other methods to specify a non-default delimiter.

That means that this version of Ebean isn't compatible with MySQL/MariaDB anymore.

[rbygrave]

That means that this version of Ebean isn't compatible with MySQL/MariaDB anymore.

No it doesn't. What it means is that Ebean's DDL runner can parse DELIMITER just like the MySQL command line tool. Anyone using Ebean's DDL runner has never had a problem with this.

The problem I think is that the Play framework DDL parser/runner does not parse the DELIMITER keyword. So that gives you are few options:

• Change to use Ebean's DDL parser/runner
• Change the Play Framework DDL parser/runner to support this
• Change to not use these helper db functions for DB migrations.

The only option that Ebean can help is with the 3rd option. Have a configuration option to not use the db helper functions as part of DB migrations.

[cyrilfr]

OK I see. I'll try to disable the db helper functions. I don't even know if Play Framework has a DDL parser/runner. I just see Evolutions generating SQL files and running them if needed. Maybe I'm wrong about it... I discovered the DDL scripts with this issue...

[rbygrave]

My thought is that we can have the option of not using the db stored procedure functions for MySql. They are used for MySql to support "drop column" in a more robust manor. We can have the option to just use a normal "drop column" knowing that it won't work in the cases that the column is referenced as a foreign key etc (but that is ok, people can manually tweak the migration for those cases).

So I'm looking to do that. I should update this in a few days.

Cheers, Rob.