Re: Procedures or function using set command

[Phil Siyam]

Update :-

I think we finally fixed issue of db using all storage and then restarting, I posted this in another thread.

In addition to changing set to into , we also had to change one function, instead of query returning result directly, we changed query to return result into variable and then return variable.

Today is the first day , our db did not crash after upgrade, but user activity was less today so I am not celebrating yet.

I think this issue is because of casting of data type in set and into.


e,g

old function :
CREATE DEFINER=`root`@`%` FUNCTION `get_colorway_desc_org`(v_temp int) RETURNS varchar(500) CHARSET utf8mb4
BEGIN

return (

select group_concat(tt3.col_name order by tt3.cno separator '/') color from (
select tt2.cno, mac.col_name from mac where .....

) tt3
)




New Function :
CREATE DEFINER=`root`@`%` FUNCTION `get_colorway_desc_org`(v_temp int) RETURNS varchar(500) CHARSET utf8mb4
BEGIN
DECLARE coldesc varchar(500) DEFAULT '';

select group_concat(tt3.col_name order by tt3.cno separator '/') color into coldesc from (
select tt2.cno, mac.col_name from mac where .....

) tt3;