Bug #5337 script delimiter stored procedure
Submitted: 1 Sep 2004 10:06 Modified: 22 Apr 2006 0:44
Reporter: Ben Swann Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1 standard OS:Linux (linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[1 Sep 2004 10:06] Ben Swann
Description:
Inputting of a stored procedure by hand through the client console works fine.  However, when placing the exactly same sequence of commands in an sql file it fails. I believe it is a problem with setting the delimiter within a script file.

How to repeat:
Contents of script.sql

### script.sql ###

drop database if exists temp;
create database temp;

use temp;

drop table if exists ttemp;
create table ttemp 
(
 id int not null auto_increment, 
 name varchar (40) not null default '',
 primary key (id)
);

drop procedure if exists tttemp;
delimiter //
create procedure tttemp (in sName varchar (40))
begin
 insert into ttemp (name) values (sName);
end ;
//

delimiter ;

call tttemp ('bds');
select * from ttemp;

### End Script.sql ###

Firstly I entered these commands in by hand and then attempted to run
the script.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.1-alpha-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.1-alpha-standard |
+----------------------+
1 row in set (0.00 sec)

mysql> drop database if exists temp;
Query OK, 0 rows affected (0.01 sec)

mysql> create database temp;
Query OK, 1 row affected (0.00 sec)

mysql> use temp;
Database changed
mysql> drop table if exists ttemp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table ttemp (id int not null auto_increment, name varchar (40) not null default '', primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure if exists tttemp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> create procedure tttemp (in sName varchar (40))
    -> begin
    -> insert into ttemp (name) values (sName);
    -> end ;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call tttemp ('bds');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttemp;
+----+------+
| id | name |
+----+------+
|  1 | bds  |
+----+------+
1 row in set (0.00 sec)

## UPTO THIS POINT EVERYTHING SEEMS OK
mysql> 

mysql> source script.sql
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): 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 ;
call tttemp ('bds');
select * from ttemp' at line 1
mysql>

Suggested fix:
[1 Sep 2004 10:07] Ben Swann
The script source

Attachment: script.sql (text/plain), 418 bytes.

[1 Sep 2004 10:07] Ben Swann
The output

Attachment: output.txt (text/plain), 1.73 KiB.

[1 Sep 2004 11:15] Victoria Reznichenko
Thank you for bug report!

I was able to repeat it with the latest BK 5.0 source tree.
[7 Dec 2004 12:08] Per-Erik Martin
This is most likely a 4.1 bug really (as the delimiter mechanism was instroduced there).
[27 Jun 2005 23:39] Jim Winstead
The patch for Bug #11523 likely solves this, too. The problem is in how the delimiter command is handled within a file that is read via the source command.
[5 Nov 2005 13:38] Marek Woch
in 5.0.15 on Win98SE in client (mysql), delimiter setted in script works in script but after returning to client prompt you have to repeat (as 1 command after finishing script)  statement  , for example delimiter ; .
[22 Apr 2006 0:44] Jim Winstead
This is no longer repeatable, and was probably solved by the fix for Bug #11523.
[3 May 2010 11:39] vinay maurya
hi...can anyone help me...
i am trying this simplest code to create stored procedure on mysql command prompt.but it is giving an syntax error...i know this error is because of semicolon(';')...while writing on command prompt, when we use the semicolon and press ENTER key to go to next statement, without going on next statement,it is executed and gives syntax error..can anyone help me ..for this on command prompt.........thanks 

mysql> CREATE PROCEDURE sp_condition(IN var1 INT) 
BEGIN 
 IF (var1 > 10)  
  THEN SELECT 'greater'; 
  ELSE SELECT 'less than or equal'; 
 END IF; 
END|
[5 May 2010 7:04] vinay maurya
thanks Ben Swann.... i got the solution from your article dated [1 Sep 2004 12:06] Ben Swann  
thanks a lot.....