MySQL Forums
Forum List  »  Stored Procedures

Re: Procedures or function using set command
Posted by: Phil Siyam
Date: January 27, 2022 04:17PM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Procedures or function using set command
95
January 27, 2022 04:17PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.