前二天我提出了【如何中斷 mysql call stored prodedure 之運行？】問題，綜合殺豬大及通靈亡二位先進的提示，我試著寫出如下測試程式，也算是表達謝意：

CREATE DEFINER=`root`@`%` PROCEDURE `proc_ctrlprocess`(IN `vJson` longtext)
    READS SQL DATA
    COMMENT '操控執行程序'
BEGIN
	declare vType,vInfo,vProcName,vSleep varchar(100) default '';
	declare vConnectionId int default 0;
	declare vTime datetime;
	if JSON_VALID(vJson)=0 then
		call raise(2019,concat('json 參數格式錯誤：',vJson));
	end if ;
	set vType = JSON_VALUE(vJson,'$.type');
	set vProcName = JSON_VALUE(vJson,'$.procname');
	set vInfo = JSON_VALUE(vJson,'$.info');
	case vType
		when 'start' then #開始
			CREATE TABLE IF NOT EXISTS `PROCESSLIST`  (
			  `ID` bigint(4) NOT NULL DEFAULT 0,
			  `USER` varchar(128)  NOT NULL DEFAULT user(),
			  `DB` varchar(64)  NULL DEFAULT database(),
			  `PROCNAME` varchar(160)  NOT NULL DEFAULT '',
			  `TIME` timestamp NOT NULL DEFAULT current_timestamp,
			  `INFO` varchar(64)  NULL DEFAULT NULL
			) ENGINE = memory;

			/* 先刪掉同名的程序 */
			DELETE from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName;

			/* 插入資料 */
			INSERT INTO `PROCESSLIST` (`ID`,`PROCNAME`)
			select CONNECTION_ID() , vProcName ;
		when 'set' then #設定狀態
			update PROCESSLIST set `INFO` = vInfo
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName;
		when 'get' then #取得狀態
			/* 取得起始時間 */
			select `TIME` into vTime
			from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName ;

			/* 回傳取得之訊息及已執行秒數 */
			select `INFO`,timestampdiff(second,vTime,now()) as elapsed_time
			from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName;
		when 'end' then #執行結束
			DELETE from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName;
		when 'kill' then #中斷執行
			select ID into vConnectionId from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName ;
			set @query = concat('kill ',vConnectionId,';');
			PREPARE QUERY FROM @query;
			EXECUTE QUERY;

			/* 取得起始時間 */
			select `TIME` into vTime
			from PROCESSLIST
			where `USER`=user() and `DB`=database() and  `PROCNAME`=vProcName ;

			DELETE from PROCESSLIST
			where `ID`=vConnectionId;

			/* 回傳中止程序之訊息及已執行秒數 */
			select @query AS info , timestampdiff(second,vTime,now()) as elapsed_time ;
	end case;
END

製造一個 stored procedure 來測試

CREATE DEFINER=`root`@`%` PROCEDURE `break_test`()
BEGIN
    /* 先註冊一下這支程序要開工啦 */
	call proc_ctrlprocess('{"type":"start","procname":"break_test"}');
	while true DO
		/* 做愛做的事


		*/
		/* 填入訊息 */
		call proc_ctrlprocess(concat('{',JSON_QUOTE('type'),':',JSON_QUOTE('set'),',',JSON_QUOTE("procname"),':',JSON_QUOTE("break_test"),',',JSON_QUOTE('info'),':',JSON_QUOTE(uuid()),'}'));
	end while;

	/* 結束時順便清一下垃圾 */
	call proc_ctrlprocess('{"type":"end","procname":"break_test"}');
END

建好環境後，可以測試執行 procedure　啦

call break_test();

如果，要取得執行中的結果

call proc_ctrlprocess('{"type":"get","procname":"break_test"}');

假如，想中斷執行程序

call proc_ctrlprocess('{"type":"kill","procname":"break_test"}');

目前，的確可以中斷break_test之運行，只是，很奇怪的是，我必需連下二次的【call proc_ctrlprocess('{"type":"kill","procname":"break_test"}');】
才有作用，只下一次中斷不了，如果可以的話，請幫我看看問題出在哪裡？