MySQL Bugs: #77470: run procedure error

Bug #77470	run procedure error
Submitted:	24 Jun 2015 8:58	Modified:	17 May 2016 22:47
Reporter:	Liang Xue	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.3.3, 6.3.4	OS:	Windows (Microsoft Windows 8.1 China)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
some correlation log in C:\Users\Liang\AppData\Roaming\MySQL\Workbench\log\sql_actions_unconnected.log：

[148, 13:44:05] create procedure basic_data.update_region_code()
begin
  declare _id varchar(50);
  declare _parent_id varchar(50);
  declare _parent_code varchar(50);
  declare _done varchar(1) default false;
  declare _cur cursor for select id, parent_id from region where level = 3 order by level asc;
  declare continue handler for not found set _done = true;
  open _cur;
  read_loop: loop
    fetch _cur into _id, _parent_id;
    select @_parent_code := `code` from region where id = _parent_id;
#    select concat(@_parent_code, id, ',') from region where id = _id;
    update region set `code` = concat(@_parent_code, _id, ',') where id = _id;
    if _done then 
      leave read_loop;
	end if;
  end loop;
  close _cur;
end: 0 row(s) affected
[149, 13:44:09] call update_region_code(): Running...
[149, 13:44:09] call update_region_code(): Fetching...
[149, 13:44:09] call update_region_code(): 1 row(s) returned
[150, 13:44:09] call update_region_code(): Fetching...
[150, 13:44:09] call update_region_code(): 1 row(s) returned
[151, 13:44:09] call update_region_code(): Fetching...
[151, 13:44:09] call update_region_code(): 1 row(s) returned
[152, 13:44:09] call update_region_code(): Fetching...
[152, 13:44:09] call update_region_code(): 1 row(s) returned
[153, 13:44:09] call update_region_code(): Fetching...
[153, 13:44:09] call update_region_code(): 1 row(s) returned
[154, 13:44:09] call update_region_code(): Fetching...
[154, 13:44:09] call update_region_code(): 1 row(s) returned
[155, 13:44:09] call update_region_code(): Fetching...
[155, 13:44:09] call update_region_code(): 1 row(s) returned
[156, 13:44:09] call update_region_code(): Fetching...
[156, 13:44:09] call update_region_code(): 1 row(s) returned
[157, 13:44:09] call update_region_code(): Fetching...
[157, 13:44:09] call update_region_code(): 1 row(s) returned
[158, 13:44:09] call update_region_code(): Fetching...
[158, 13:44:09] call update_region_code(): 1 row(s) returned
[159, 13:44:09] call update_region_code(): Fetching...
[159, 13:44:09] call update_region_code(): 1 row(s) returned
[160, 13:44:09] call update_region_code(): Fetching...
[160, 13:44:09] call update_region_code(): 1 row(s) returned
[161, 13:44:09] call update_region_code(): Fetching...
[161, 13:44:09] call update_region_code(): 1 row(s) returned
[162, 13:44:09] call update_region_code(): Fetching...
[162, 13:44:09] call update_region_code(): 1 row(s) returned
[163, 13:44:09] call update_region_code(): Fetching...
[163, 13:44:09] call update_region_code(): 1 row(s) returned
[164, 13:44:09] call update_region_code(): Fetching...
[164, 13:44:09] call update_region_code(): 1 row(s) returned
[165, 13:44:09] call update_region_code(): Fetching...
[165, 13:44:09] call update_region_code(): 1 row(s) returned
[166, 13:44:09] call update_region_code(): Fetching...
[166, 13:44:09] call update_region_code(): 1 row(s) returned
[167, 13:44:09] call update_region_code(): Fetching...
[167, 13:44:09] call update_region_code(): 1 row(s) returned
[168, 13:44:09] call update_region_code(): Fetching...
[168, 13:44:09] call update_region_code(): 1 row(s) returned
[169, 13:44:09] call update_region_code(): Fetching...
[169, 13:44:09] call update_region_code(): 1 row(s) returned
[170, 13:44:09] call update_region_code(): Fetching...
[170, 13:44:09] call update_region_code(): 1 row(s) returned
[171, 13:44:09] call update_region_code(): Fetching...
[171, 13:44:09] call update_region_code(): 1 row(s) returned
[172, 13:44:09] call update_region_code(): Fetching...
[172, 13:44:09] call update_region_code(): 1 row(s) returned
[173, 13:44:09] call update_region_code(): Fetching...
[173, 13:44:09] call update_region_code(): 1 row(s) returned
[174, 13:44:09] call update_region_code(): Fetching...
[174, 13:44:09] call update_region_code(): 1 row(s) returned
[175, 13:44:09] call update_region_code(): Fetching...
[175, 13:44:09] call update_region_code(): 1 row(s) returned
[176, 13:44:09] call update_region_code(): Fetching...
[176, 13:44:09] call update_region_code(): 1 row(s) returned
[177, 13:44:09] call update_region_code(): Fetching...
[177, 13:44:09] call update_region_code(): 1 row(s) returned
[178, 13:44:09] call update_region_code(): Fetching...
[178, 13:44:09] call update_region_code(): 1 row(s) returned
[179, 13:44:09] call update_region_code(): Fetching...
[179, 13:44:09] call update_region_code(): 1 row(s) returned
[180, 13:44:09] call update_region_code(): Fetching...
[180, 13:44:09] call update_region_code(): 1 row(s) returned
[181, 13:44:09] call update_region_code(): Fetching...
[181, 13:44:09] call update_region_code(): 1 row(s) returned
[182, 13:44:09] call update_region_code(): Fetching...
[182, 13:44:09] call update_region_code(): 1 row(s) returned
[183, 13:44:09] call update_region_code(): Fetching...
[183, 13:44:10] call update_region_code(): 1 row(s) returned
[184, 13:44:10] call update_region_code(): Fetching...
[184, 13:44:10] call update_region_code(): 1 row(s) returned
[185, 13:44:10] call update_region_code(): Fetching...
[185, 13:44:10] call update_region_code(): 1 row(s) returned
[186, 13:44:10] call update_region_code(): Fetching...
[186, 13:44:10] call update_region_code(): 1 row(s) returned
[187, 13:44:10] call update_region_code(): Fetching...
[187, 13:44:10] call update_region_code(): 1 row(s) returned
[188, 13:44:10] call update_region_code(): Fetching...
[188, 13:44:10] call update_region_code(): 1 row(s) returned
[189, 13:44:10] call update_region_code(): Fetching...
[189, 13:44:10] call update_region_code(): 1 row(s) returned
[190, 13:44:10] call update_region_code(): Fetching...
[190, 13:44:10] call update_region_code(): 1 row(s) returned
[191, 13:44:10] call update_region_code(): Fetching...
[191, 13:44:10] call update_region_code(): 1 row(s) returned
[192, 13:44:10] call update_region_code(): Fetching...
[192, 13:44:10] call update_region_code(): 1 row(s) returned
[193, 13:44:10] call update_region_code(): Fetching...
[193, 13:44:10] call update_region_code(): 1 row(s) returned
[194, 13:44:10] call update_region_code(): Fetching...
[194, 13:44:10] call update_region_code(): 1 row(s) returned
[195, 13:44:10] call update_region_code(): Fetching...
[195, 13:44:10] call update_region_code(): 1 row(s) returned
[196, 13:44:10] call update_region_code(): Fetching...
[196, 13:44:10] call update_region_code(): 1 row(s) returned
[197, 13:44:10] call update_region_code(): Fetching...
[197, 13:44:10] call update_region_code(): 1 row(s) returned
[198, 13:44:10] call update_region_code(): Fetching...
[198, 13:44:10] call update_region_code(): 1 row(s) returned
[199, 13:44:10] call update_region_code(): Error Code: 2014
Commands out of sync; you can't run this command now

How to repeat:
1:
init test data:
download this sql file and run it.
Link:http://pan.baidu.com/s/1pJkOCRL
Extracting password: eg4q

2:
create a procedure use bellow sql:

delimiter //
create procedure basic_data.update_region_code()
begin
  declare _id varchar(50);
  declare _parent_id varchar(50);
  declare _parent_code varchar(50);
  declare _done varchar(1) default false;
  declare _cur cursor for select id, parent_id from region where level > 0 order by level asc;
  declare continue handler for not found set _done = true;
  open _cur;
  read_loop: loop
    fetch _cur into _id, _parent_id;
    select @_parent_code := `code` from region where id = _parent_id;
    update region set `code` = concat(@_parent_code, _id, ',') where id = _id;
    if _done then 
      leave read_loop;
	end if;
  end loop;
  close _cur;
end //
delimiter ;

3、then, call the procedure:
call update_region_code();
--------------------------------------------------------------------------
soon,the Output say '13:44:10	call update_region_code()	Error Code: 2014 Commands out of sync; you can't run this command now'.

but,just use mysql commond line(use mysqld.exe login and call update_region_code();) call this procedure won't get this error,it goes well and finish.

my mysql server version: 5.6.25

I'm very sorry:
correct:
How to repeat:
3:
3、then, call the procedure:
call update_region_code();
--------------------------------------------------------------------------
soon,the Output say '13:44:10	call update_region_code()	Error Code: 2014 Commands out of sync; you can't run this command now'.

but,just use mysql commond line(it means use mysql.exe login) call this procedure won't get this error,it goes well and finish.

my mysql server version: 5.6.25

Hello liangzai XUE,

Thank you for the report and test case.
Confirmed this with WB 6.3.4 on Win7.

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry:

Workbench will now prompt to continue the execution of operations
containing loops in stored procedures, instead of just canceling it after
~200 executions.

Thank you for the bug report.