MySQL Bugs: #2763: load procedure from file

Bug #2763	load procedure from file
Submitted:	12 Feb 2004 20:31	Modified:	3 May 2004 11:49
Reporter:	Steven Szelei	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	4.1	OS:	Windows (Windows 2000 Adv Svr)
Assigned to:	Jani Tolonen	CPU Architecture:	Any

Description:
Created a stored procedure with an insert statement. Attempting to load the file with the \. command gave an error 1048 (23000): Column 'Customer_id' cannot be null. The prompt returns to mysql>  attempting to type any thing into the prompt will result in receiving a -> prompt upon enter. The only way to get out it ctrl-c.
here is the capture of the error
mysql> \. C:\SoftPros\Clients\HomePhone\homefone_sp.sql
ERROR 1048 (23000): Column 'Customer_id' cannot be null
mysql> $
    -> mysql
    -> connect
    ->
Here is the file contents 
delimiter $
create procedure sp_LENSOrder(@Icustid Bigint unsigned, @Iactphone char(15), @Iestcnndt Date, @Ibilltype Bigint unsigned, @Ipon char(100), @Iactstat Bigint unsigned, @IPackage_id BIGINT unsigned)
BEGIN
DECLARE @llenid unsigned bigint;
Insert into LENS (Customer_id, PON, Status_id, Billing_type_id) VALUES (@Icustid, @Ipon, @Iactstat, @Ibilltype);
SELECT MAX(LENS_id) into @llenid FROM LENS WHERE Customer_id = @ICustid;
insert into lenspackage (LENS_id, IPakcage_id) VALUES(@llenid, @IPackage_id);
CASE @Iactstat
  WHEN 1 THEN
    Update table Customer set status_id = (@Iactstat + 4), Assigned_phone_no = @Iactphone, Estimated_connection_dt = @Iestcnndt;
  WHEN 2 THEN
    Update table Customer set status_id = (@Iactstat + 4), Inactive_dt = @Iestcnndt;
  when 3 THEN
    Update table Customer set status_id = (@Iactstat + 4), Inactive_dt = @Iestcnndt;
  when 4 THEN
        Update table Customer set status_id = (@Iactstat + 4), Estimated_connection_dt = @Iestcnndt;
END CASE;
END$
Delimiter ;

How to repeat:
Create an file mysp.sql and copy the contents into it and save it.
in the database selected at the mysql prompt type \. PathInfo\mysp.sql
once the error occures and the prompt reutrns attempt to type anything in.

Suggested fix:
Return the system to original state after error. Fix the ability of stored procedure to accept insert commands constructs.
Also found it odd that the DECLARE statement would not accept bigint unsigned as type but did accept unsigned bigint.

I was only able to repeat the behavior reported when to try the command
mentioned without to select a database and after this:

mysql> show databases;
    -> show databases;
    -> show databases;
    ->

Thanks you for the bug report.

This is a bug in the command line client mysql.
When using the command "delimiter" without the current delimiter,
i.e. just "delimiter $" instead of "delimiter $;", the delimiter
gets set to something else when reading from a file. It is then
not reset properly due to syntax errors in the file. (The given
example file will not work, as it is not SQL-99/2003 syntax.)

A workaround is to always properly delimit the "delimiter" command
in source files.

N.B. fix in 4.1.

According to bugs comment, it should be ok to close this bug now.