Problem with db-migrate executing a CREATE PROCEDURE

[LuigiElleBalotta]

I'm submitting a...

• [x] Bug report
• [ ] Feature request
• [x] Question

Current behavior

The CREATE PROCEDURE won't execute if it isn't on one line

Expected behavior

The query should be executed even if the query is not on one line

Minimal reproduction of the problem with instructions

NOT WORKING QUERY

CREATE PROCEDURE `uspGetAttributes`(IN inDomain NVARCHAR(255), IN inScope NVARCHAR(255), IN inApplication NVARCHAR(255), IN inAttr NVARCHAR(255), IN inRef NVARCHAR(255))
BEGIN

    SET @query = CONCAT('SELECT at.domain, at.scope, at.`name`, at.`type`, at.attr, at.`value`, at.mandatory, a.ref, a.`value` as attrValue, a.createdAt, a.updatedAt, a.updatedBy FROM attributetypes at LEFT JOIN attributes a ON at.id = a.attributetypeId WHERE 1=1');

    IF inDomain IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND at.domain = ?');
    END IF;

    IF inScope IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND at.scope = ?');
    END IF;

    IF inApplication IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND at.`name` = ?');
    END IF;

    IF inAttr IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND at.attr = ?');
    END IF;

    IF inRef IS NOT NULL THEN
        SET @query = CONCAT(@query, ' AND a.ref = ?');
    END IF;

    SET @query = CONCAT(@query, ' ORDER BY a.updatedAt DESC, a.createdAt DESC;');

    PREPARE stmt FROM @query;

    IF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NOT NULL THEN
        EXECUTE stmt USING inDomain, inScope, inApplication, inAttr, inRef;
    ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NULL THEN
        EXECUTE stmt USING inDomain, inScope, inApplication, inAttr;
    ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NULL AND inRef IS NULL THEN
        EXECUTE stmt USING inDomain, inScope, inApplication;
    ELSEIF inDomain IS NOT NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN
        EXECUTE stmt USING inDomain;
    ELSEIF inDomain IS NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN
        EXECUTE stmt;
    END IF;

    DEALLOCATE PREPARE stmt;

END;

GIVES ERROR:

WORKING QUERY

CREATE PROCEDURE uspGetAttributes(IN inDomain VARCHAR(255), IN inScope VARCHAR(255), IN inApplication VARCHAR(255), IN inAttr VARCHAR(255), IN inRef VARCHAR(255)) BEGIN SET @query = CONCAT('SELECT at.domain, at.scope, at.`name`, at.`type`, at.attr, at.`value`, at.mandatory, a.ref, a.`value` as attrValue, a.createdAt, a.updatedAt, a.updatedBy FROM attributetypes at LEFT JOIN attributes a ON at.id = a.attributetypeId WHERE 1=1'); IF inDomain IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.domain = ?'); END IF; IF inScope IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.scope = ?'); END IF; IF inApplication IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.`name` = ?'); END IF; IF inAttr IS NOT NULL THEN SET @query = CONCAT(@query, ' AND at.attr = ?'); END IF; IF inRef IS NOT NULL THEN SET @query = CONCAT(@query, ' AND a.ref = ?'); END IF; SET @query = CONCAT(@query, ' ORDER BY a.updatedAt DESC, a.createdAt DESC;'); PREPARE stmt FROM @query; IF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NOT NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication, inAttr, inRef; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NOT NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication, inAttr; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NOT NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope, inApplication; ELSEIF inDomain IS NOT NULL AND inScope IS NOT NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain, inScope; ELSEIF inDomain IS NOT NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt USING inDomain; ELSEIF inDomain IS NULL AND inScope IS NULL AND inApplication IS NULL AND inAttr IS NULL AND inRef IS NULL THEN EXECUTE stmt; END IF; DEALLOCATE PREPARE stmt; END;

What is the motivation / use case for changing the behavior?

The query should be executed on more lines as in other sql clients like DBEaver or HeidiSQL

Environment

db-migrate version: 0.11.13
plugins with versions: X.Y.Z
db-migrate driver with versions:  "db-migrate-mysql": "^2.2.0",

Additional information:
- Node version: 18.13.0  
- Platform:   Windows

Others:

[wzrdtales]

please read the docs

https://db-migrate.readthedocs.io/en/latest/Getting%20Started/configuration/#important-for-mysql-users

[LuigiElleBalotta]

Hello, i already read the docs, this is the config:

 {
        "driver": "mysql",
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "root",
        "database": "cretinodelcazzo",
        "driver": "mysql",
        "multipleStatements": true
    }

Please don't consider other users stupid :D, reopen @wzrdtales ...

[wzrdtales]

worked according to those users here https://github.com/db-migrate/node-db-migrate/issues/625#issuecomment-670713081