MySQL Stored Function - How to get @@warning_count?

Question

I have the following MySQL 8.0 stored function:

DELIMITER $$

CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT)
RETURNS text CHARSET utf8mb4 DETERMINISTIC
BEGIN
declare str_converted text charset utf8mb4;
declare max_error_count int default @@max_error_count;
set @@max_error_count = 0;
set @@max_error_count = max_error_count;
set str_converted = convert(cast(convert(str using latin1) as binary) using utf8mb4);
if @@warning_count > 0 then
INSERT INTO a_warnings (id) VALUES (rowid);
return str;
else
return str_converted;
end if;
END$$

DELIMITER ;

It gets executed like this:

UPDATE products SET description2 = maybe_utf8_decode(description,id);

I got this function from here: https://jonisalonen.com/2012/fixing-doubly-utf-8-encoded-text-in-mysql/ . The purpose is to fix our database by correcting "double encoded" product descriptions. The problem is that a plain CONVERT by itself truncates many of my product descriptions when it encounters an Error or a Warning. The function above isn't working because it's not recognizing Warnings/Errors when convert(cast(convert(str using latin1) as binary) using utf8mb4) is attempted. My plan was if a Warning/Error appeared, skip the conversion script and save the product id instead, so I can review them manually. Otherwise if there are no Errors/Warnings, I would like the function to convert the string.

What would be the proper way to write this function, so it skips strings if Errors/Warnings appear and store that rowid in a separate table?

Example of warning that I want to skip. When I run:

convert(cast(convert(description2 using latin1) as binary) using utf8mb4)

I'll occasionally get an warning like below, followed by my string getting truncated:

Warning: #1300 Invalid utf8mb4 character string: 'A04C69'

I would like my function to rollback that conversion and return the original string when a Warning like that happens (to prevent my data from being truncated). So the problem is how do I know a warning like this happens in my function? How do I get @@warning_count to work the way I intend?

Answer 1

Use user-defined variable.

CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT)
RETURNS text CHARSET utf8mb4 DETERMINISTIC
BEGIN
-- ...
if @@warning_count > 0 then
    INSERT INTO a_warnings (id) VALUES (rowid);
    SET @warnings_amount := @warnings_amount + 1;    -- increase UDV
    return str;
else
    return str_converted;
end if;
END

and then

UPDATE products 
  JOIN (@warnings_amount := 0) reset_UDV   -- init/clear UDV value
SET description2 = maybe_utf8_decode(description,id);

SELECT @warnings_amount;   -- look for warnings amount collected in UDV