MySQL Bugs: #41926: source file containing DELIMITER commands along with create trigger statements

Bug #41926	source file containing DELIMITER commands along with create trigger statements
Submitted:	7 Jan 2009 15:44	Modified:	13 Jan 2009 15:11
Reporter:	Paul Lemay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.68-pro-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello there,

I am really having a hard time understanding how to deal with delimiter commands in files sourced in mysql from the command line. I am doing the following command

mysql < ./SubscriberProfile.sql

that file contains the following command:

DROP DATABASE IF EXISTS bluedb;
CREATE DATABASE bluedb;
USE bluedb;
source ./BlueDb.sql

the latter file contains the following definition:

drop table if exists SubscriberProfile;
create table SubscriberProfile (
  Imsi varchar(15) not null,
  IsMultiMsIsdn bool default 0,
  primary key (Imsi)
) type = InnoDB MIN_ROWS = 100000;

drop table if exists VolData;
CREATE TABLE VolData (
  Imsi             varchar(15) NOT NULL,
  VolVlrData       varbinary(500)
  primary key(Imsi)
) ENGINE=MyISAM;

drop TRIGGER if exists SubscriberProfile_T1;
delimiter //
CREATE TRIGGER SubscriberProfile_T1
    AFTER DELETE ON SubscriberProfile
    FOR EACH ROW
    begin
        delete from VolData where old.Imsi = imsi;
    end; //
delimiter ;

when I execute the command:

mysql < ./SubscriberProfile.sql

I get the following error:

ERROR 1064 (42000) at line 261 in file: '/blue/etc/Blueslice_BlueDbHlr.sql': 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 'end; //
delimiter ;

But when the delimiter // CREATE TRIGGER ... is copied directly into mysql, it works fine. Can you please give me a hand on this?

How to repeat:

create a file SubscriberProfile.sql

that file contains the following command:

DROP DATABASE IF EXISTS bluedb;
CREATE DATABASE bluedb;
USE bluedb;
source ./BlueDb.sql

create a file BlueDb.sql that contains the following definition:

drop table if exists SubscriberProfile;
create table SubscriberProfile (
  Imsi varchar(15) not null,
  IsMultiMsIsdn bool default 0,
  primary key (Imsi)
) type = InnoDB MIN_ROWS = 100000;

drop table if exists VolData;
CREATE TABLE VolData (
  Imsi             varchar(15) NOT NULL,
  VolVlrData       varbinary(500)
  primary key(Imsi)
) ENGINE=MyISAM;

drop TRIGGER if exists SubscriberProfile_T1;
delimiter //
CREATE TRIGGER SubscriberProfile_T1
    AFTER DELETE ON SubscriberProfile
    FOR EACH ROW
    begin
        delete from VolData where old.Imsi = imsi;
    end; //
delimiter ;

execute the command:

mysql < ./SubscriberProfile.sql

Thank you for the bug report. I couldn't repeat against the latest source server notice I modified your test case adding a comma before the primary key definition in the create table statement that to avoid a syntax error message:

Linux hegel 2.6.27-7-generic #1 SMP Fri Oct 24 06:40:41 UTC 2008 x86_64

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

To access official Ubuntu documentation, please visit:
http://help.ubuntu.com/
Last login: Wed Jan  7 20:24:07 2009 from 192.168.0.25
miguel@hegel:~$ cd dbs/5.0/bin/
miguel@hegel:~/dbs/5.0/bin$ cat ./BlueDb.sql 
drop table if exists SubscriberProfile;
create table SubscriberProfile (
  Imsi varchar(15) not null,
  IsMultiMsIsdn bool default 0,
  primary key (Imsi)
) type = InnoDB MIN_ROWS = 100000;

drop table if exists VolData;
CREATE TABLE VolData (
  Imsi             varchar(15) NOT NULL,
  VolVlrData       varbinary(500),
  primary key(Imsi)
) ENGINE=MyISAM;

drop TRIGGER if exists SubscriberProfile_T1;
delimiter //
CREATE TRIGGER SubscriberProfile_T1
    AFTER DELETE ON SubscriberProfile
    FOR EACH ROW
    begin
        delete from VolData where old.Imsi = imsi;
    end; //
delimiter ;
miguel@hegel:~/dbs/5.0/bin$ cat ./SubscriberProfile.sql 
DROP DATABASE IF EXISTS bluedb;
CREATE DATABASE bluedb;
USE bluedb;
source ./BlueDb.sql

miguel@hegel:~/dbs/5.0/bin$ ./mysql -uroot < ./SubscriberProfile.sql 
miguel@hegel:~/dbs/5.0/bin$ ./mysql -uroot -e 'show triggers from bluedb'
+----------------------+--------+-------------------+------------------------------------------------------------------+--------+---------+----------+----------------+
| Trigger              | Event  | Table             | Statement                                                        | Timing | Created | sql_mode | Definer        |
+----------------------+--------+-------------------+------------------------------------------------------------------+--------+---------+----------+----------------+
| SubscriberProfile_T1 | DELETE | SubscriberProfile | begin
        delete from VolData where old.Imsi = imsi;
    end | AFTER  | NULL    |          | root@localhost | 
+----------------------+--------+-------------------+------------------------------------------------------------------+--------+---------+----------+----------------+

miguel@hegel:~/dbs/5.0/bin$ ./mysql -uroot -e 'show variables like "%version%"'
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.0.76-debug        | 
| version_comment         | Source distribution | 
| version_compile_machine | x86_64              | 
| version_compile_os      | unknown-linux-gnu   | 
+-------------------------+---------------------+

Hello There,

the which command finally instructs me that I was executing the mysql client from an older distribution. The proper client was working properly.

Thanks for your help.