Accepting DELIMITER syntax on MySQL and MariaDB

[Maransatto]

Folks,

Imagine a situation where we need to manage huge scripts with DELIMITERS, once the MySQL 5.6 does not accept commands like ALTER TABLE tableName DROP FOREIGN KEY IF EXISTS fkName. In this case, the solution would be to create an IF statement like this:

    DELIMITER $$

    IF EXISTS(
        SELECT * FROM information_schema.table_constraints
        WHERE 
            table_schema    = DATABASE()                    AND
            table_name      = 'tableName'         AND
            constraint_name = 'fkName'  AND
            constraint_type = 'FOREIGN KEY')
    THEN
              ALTER TABLE tableName 
         DROP FOREIGN KEY fkName;
    END IF $$

    DELIMITER ;

The fact is that vscode-sqltools does not accept DELIMITER syntax. We get an error like this:

Now I have to use Workbench because of it, but I really prefere to use vs code instead.

So here it is my recommendation for this project: Start accepting DELIMITER syntax. I still have no idea of how to make it, but I believe we can find the logic inside some forks from mysql.

[Lucs1590]

I was having the same problem! Very nice Issue!

[mtxr]

Please, take a look at this issue: https://github.com/mysqljs/mysql/issues/1683

Apparently DELIMITER is a command to mysql-cli and not a mysql syntax, so it's not part of the driver we are using. I found this library https://github.com/sidorares/node-mysql2 that I believe that works with the delimiter syntax, also I've found some guys mentioning that if we just remove the DELIMITER line the query would work.

I'll try mysql2 driver and see what happens, because I don't like the idea of transforming the query we run.

[VeryCrazyDog]

@Maransatto Did you tried something like this?

/*!50110 ALTER TABLE tableName DROP FOREIGN KEY IF EXISTS fkName */;

According to MySQL documentation, it seems helpful to your case?

If you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The KEY_BLOCK_SIZE keyword in the following comment is executed only by servers from MySQL 5.1.10 or higher:

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

@mtxr Hopefully you have noticed my comment in mysqljs/mysql#1683. In my opinion, unfortunately transformation must be happen before sending the query to server. Either the driver provided that transformation functionality or we implement our own.