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:
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

[12 Feb 2004 20:31] Steven Szelei
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)
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
    Update table Customer set status_id = (@Iactstat + 4), Assigned_phone_no = @Iactphone, Estimated_connection_dt = @Iestcnndt;
    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;
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.
[17 Feb 2004 18:57] MySQL Verification Team
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.
[6 Apr 2004 8:12] Per-Erik Martin
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.
[6 Apr 2004 8:22] Per-Erik Martin
N.B. fix in 4.1.
[3 May 2004 11:49] Michael Widenius
According to bugs comment, it should be ok to close this bug now.