MySQL Bugs: #5337: script delimiter stored procedure

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

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:

The script source

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

The output

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

Thank you for bug report!

I was able to repeat it with the latest BK 5.0 source tree.

This is most likely a 4.1 bug really (as the delimiter mechanism was instroduced there).

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.

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 ; .

This is no longer repeatable, and was probably solved by the fix for Bug #11523.

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|

thanks Ben Swann.... i got the solution from your article dated [1 Sep 2004 12:06] Ben Swann  
thanks a lot.....