| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.