CODEWITHSUNDEEP
mysqllinuxubuntumysql-workbench
Hooman Bahreini
Hooman Bahreini

Reputation: 15569

White space changes the delimiter when running MySQL script from the shell/command prompt

I use MySQL innodb version 5.7.10 'MySQL Community Server (GPL)' on a Windows machine. The following script runs fine if I run it from MySQL Workbench:

DROP PROCEDURE IF EXISTS procedure1;

DELIMITER //

CREATE PROCEDURE procedure1(IN pageSize BIGINT) 
BEGIN
    SELECT * FROM table1 LIMIT pageSize;
END //

DELIMITER ;     -- note that there is an extra tab char here

DROP PROCEDURE IF EXISTS procedure2;

DELIMITER //

CREATE PROCEDURE procedure2() 
BEGIN
    SELECT * FROM table2;
END //

DELIMITER ;

But if copy the script into schema.sql and run it from Windows command prompt:

mysql> c:\release\ver1\schema.sql

I get 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 //

This error is because of the tab char, if I remove the tab, it will run fine.

I have also tried running it from Linux shell (MyQSL innodb version 5.7.25) and I get the same error:

mysql> source /home/ubuntu/release/ver1/schema.sql

Note: I have tried replacing the tab char (\t) with a couple of white space characters, and the white spaces are fine... it's only the tab char which changes the delimiter.

I am using MySQL Workbench for the Dev Environment, but for Test and Prod Environments, I am just using MySQL from Linux Shell... this error has caught me so many times, because the scripts which have passed the Dev Environment, fails in Test and every time I have to remember to go back and remove the tabs from the script.

Is there anyway to fix this issue or configure MySQL to ignore tab char?

Upvotes: 6

Views: 657

Answers (1)

Chibueze Opata
Chibueze Opata

Reputation: 10044

There are programs like GrepWin that can easily replace characters in files. However, if you want something automated, I would recommend you setup a webhook and have a command to replace all tabs like below execute every time your schema is pushed.

sed -i.bak $'s/\t*//g' schema.sql

Upvotes: 1

Related Questions