Bug #22010 Multi line comment error in stored procedure
Submitted: 5 Sep 2006 10:04 Modified: 5 Sep 2006 11:03
Reporter: Andre Timmer Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.18 OS:
Assigned to: CPU Architecture:Any

[5 Sep 2006 10:04] Andre Timmer
Description:
Error when compiling procedure with multi line comment.

How to repeat:
CREATE TABLE if not exists `cwi_postcode` (
  `postcode` varchar(6) NOT NULL default '',
  `code_district` int(11) NOT NULL default '0',
  `district` varchar(30) NOT NULL default '',
  `code_cwi` int(11) NOT NULL default '0',
  `cwi` varchar(30) NOT NULL default '',
  `gemeente` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`postcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

drop table if exists sbrq_cwi_postcode;

create table sbrq_cwi_postcode
as
select * from cwi_postcode;

alter table sbrq_cwi_postcode 
add index cwi_postcode_i1(postcode);

drop procedure if exists p_sbrq_cwi_postcode_fill;

delimiter //

create procedure p_sbrq_cwi_postcode_fill()
begin
   declare done          int default 0;
   declare done1         int default done;
   declare v_char1       int default ascii('A')-1;   
   declare v_char2       int default ascii('A')-1;   
   declare v_c1_postcode      varchar(6);
   declare v_c1_code_district int(11);
   declare v_c1_district      varchar(30);
   declare v_c1_code_cwi      int(11);
   declare v_c1_cwi           varchar(30);
   declare v_c1_gemeente      varchar(30);

   declare v_tmp         varchar(6);
--
   declare c1 cursor 
   for 
   select 
         postcode, code_district, district, code_cwi, cwi, gemeente 
   from 
         sbrq_cwi_postcode 
   order by 
         postcode;
--         
   declare continue handler for sqlstate '02000' set done = 1;
--  
--
-- Fill column parent_code
--
   open c1;
   repeat
      set done = 0;
      fetch c1 into v_c1_postcode                    , v_c1_code_district                    , v_c1_district                    , v_c1_code_cwi                    , v_c1_cwi                    , v_c1_gemeente; 
      /*
      multiple-lines-of-code
      */
      set done1 = done;
--       
      -- select 'debug';
      l1:
      loop
         set v_char1 = v_char1+1;
--
         l2:
         loop
	    set v_char2 = v_char2+1;
            set v_tmp = null;
--
            select postcode into v_tmp
            from   sbrq_cwi_postcode
            where  postcode = v_c1_postcode;
--
            if (v_tmp is null) then
               select concat('To be inserted: ', v_c1_postcode, '+', char(v_char1), char(v_char2)) message;
               -- insert into 
            else    
               select concat('No action for: ', v_c1_postcode) message;
            end if;
--
            if (v_char2 >= ascii('Z')) then
               leave l2;
            end if;
         end loop;
--
         if (v_char1 >= ascii('Z')) then
            leave l1;
         end if;
       end loop;
--       
   until done1 end repeat;   
--    
   commit;
--
   close c1;
--
end;
//

delimiter ;

Gives:
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 'done1 end repeat;
   commit;
   close c1;
end' at line 45

Suggested fix:
Some how it has to do with the statements before the create procedure.

If after the first run you remove some the first statements the procedures get created succesfully.
[5 Sep 2006 11:03] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

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

mysql> CREATE TABLE if not exists `cwi_postcode` (
    ->   `postcode` varchar(6) NOT NULL default '',
    ->   `code_district` int(11) NOT NULL default '0',
    ->   `district` varchar(30) NOT NULL default '',
    ->   `code_cwi` int(11) NOT NULL default '0',
    ->   `cwi` varchar(30) NOT NULL default '',
    ->   `gemeente` varchar(30) NOT NULL default '',
    ->   PRIMARY KEY  (`postcode`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists sbrq_cwi_postcode;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table sbrq_cwi_postcode
    -> as
    -> select * from cwi_postcode;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbrq_cwi_postcode
    -> add index cwi_postcode_i1(postcode);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> delimiter //
mysql> create procedure p_sbrq_cwi_postcode_fill()
    -> begin
    ->    declare done          int default 0;
    ->    declare done1         int default done;
    ->    declare v_char1       int default ascii('A')-1;
    ->    declare v_char2       int default ascii('A')-1;
    ->    declare v_c1_postcode      varchar(6);
    ->    declare v_c1_code_district int(11);
    ->    declare v_c1_district      varchar(30);
    ->    declare v_c1_code_cwi      int(11);
    ->    declare v_c1_cwi           varchar(30);
    ->    declare v_c1_gemeente      varchar(30);
    ->
    ->    declare v_tmp         varchar(6);
    -> --
    ->    declare c1 cursor
    ->    for
    ->    select
    ->          postcode, code_district, district, code_cwi, cwi, gemeente
    ->    from
    ->          sbrq_cwi_postcode
    ->    order by
    ->          postcode;
    -> --
    ->    declare continue handler for sqlstate '02000' set done = 1;
    -> --
    -> --
    -> -- Fill column parent_code
    -> --
    ->    open c1;
    ->    repeat
    ->       set done = 0;
    ->       fetch c1 into v_c1_postcode                    , v_c1_code_distric
t
    ->             , v_c1_district                    , v_c1_code_cwi

    ->  , v_c1_cwi                    , v_c1_gemeente;
    ->       /*
   /*>       multiple-lines-of-code
   /*>       */
    ->       set done1 = done;
    -> --
    ->       -- select 'debug';
    ->       l1:
    ->       loop
    ->          set v_char1 = v_char1+1;
    -> --
    ->          l2:
    ->          loop
    ->     set v_char2 = v_char2+1;
    ->             set v_tmp = null;
    -> --
    ->             select postcode into v_tmp
    ->             from   sbrq_cwi_postcode
    ->             where  postcode = v_c1_postcode;
    -> --
    ->             if (v_tmp is null) then
    ->                select concat('To be inserted: ', v_c1_postcode, '+',
    -> char(v_char1), char(v_char2)) message;
    ->                -- insert into
    ->             else
    ->                select concat('No action for: ', v_c1_postcode) message;
    ->             end if;
    -> --
    ->             if (v_char2 >= ascii('Z')) then
    ->                leave l2;
    ->             end if;
    ->          end loop;
    -> --
    ->          if (v_char1 >= ascii('Z')) then
    ->             leave l1;
    ->          end if;
    ->        end loop;
    -> --
    ->    until done1 end repeat;
    -> --
    ->    commit;
    -> --
    ->    close c1;
    -> --
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

So, try to use newer version, 5.0.24a.