Tracking down MySQL utf8 vs. utf8mb4 Issues

Question

We have a database that is utf8mb4 and utf8mb4_unicode_ci through and through. I've even exported all SQL code of the entire "project" (via dbForge) and can't find a single reference to UTF8 in the entire project. I've verified all tables use the proper charset and collation.

The problem is every time I perform a reset on our sandbox (which rebuilds the data within the database for testing purposes), I get the following error in multiple stored procedures:

SQLEXCEPTION:CORE_create_root_data:(HY000:3719) 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

I can't figure out why on earth this is happening; there is no UTF8 definition within the stored procedure or the stored procedures it calls or any columns of any table. I've even walked the code in debug mode and tracked down where these warnings are being triggered. It's a stored procedure that simply inserts root data into the database; and the warning is triggered at the return of the call. It makes absolutely no sense. For example:

Below is the handler that's triggered:

    DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
    GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
    IF @p1 REGEXP '^01' = 1 THEN
        SET @errortype = 'SQL WARNING';
    ELSEIF @p1 REGEXP '^02' = 1 THEN
        SET @errortype = 'NOT FOUND';
    ELSEIF @p1 REGEXP '^0[0-2]' = 0 THEN
        SET @errortype = 'SQLEXCEPTION';
    END IF;

    SET @full_error = CONCAT(@errortype, ':CORE_create_root_data:(', @p1, ':', @p2, ') ', @p3);
    SELECT
        @full_error;
END;

The below Stored Procedure is called (var_result is simply a BOOL result).

CALL CORE_create_global_static_settings(in_password, var_result);

The code from the above does this:

INSERT INTO global_static_setting (setting_category, setting_name, setting_value)
VALUES ('GLOBAL_VALUE', 'LIMIT_DEFAULT', 200);

Upon return from the SP that does the simple insert... it triggers the utf8 error/warning.

Any idea what's going on here?

score 1 · Accepted Answer · edited Aug 30 '21 at 22:19

1

Assuming that your database is named testdb: the following query gives you all the character sets of the tables:

SELECT c.character_set_name, t.table_name
  FROM information_schema.tables AS t
 INNER JOIN information_schema.collation_character_set_applicability AS c 
    ON c.collation_name = t.table_collation
 WHERE t.table_schema = "testdb";

The following one shows the character sets of all columns, but only when they are of a text datatype you see one. You can filter in the WHERE clause table names and column names if you get too many results, but even in big database utf8 among utf8mb4 is easy to spot:

SELECT character_set_name, column_name, table_name 
  FROM information_schema.COLUMNS
 WHERE table_schema = "testdb";

Furthermore: stored procedures and functions have also character sets. So you must:

Make a backup of all procedures and functions,
drop the procedures and functions,
execute SET NAMES utf8mb4;, and finally
recreate the procedures and functions.

They will now have all the character set utf8mb4.

edited Aug 30 '21 at 22:19

AmigoJack

5,234
1
15
31

answered Aug 30 '21 at 16:56

nbk

45,398
8
30
47

Thanks a ton for this; I ran something similar before I posted. Everything is utf8mb4. All Tables and Columns. Any other ideas that might help track it down? – Floobinator Aug 30 '21 at 18:18
stored procedure have also chareacter sets https://stackoverflow.com/questions/11920153/creating-a-stored-procedure-setting-the-character-set-and-collation/19382972 i hope they are not that many, as you need to drop every one who is utf8 – nbk Aug 30 '21 at 18:22
That did it. I had to drop and recreate all Stored Procedures and Functions (~100,000 lines of code LOL). Thanks a ton! If you update your answer to include the above, I'll mark it as correct. Thanks again! – Floobinator Aug 30 '21 at 20:44

score 1 · Answer 2 · answered Oct 29 '21 at 03:50

SQLEXCEPTION:CORE_create_root_data:(HY000:3719) 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

is a warning.

Years ago, MySQL implement an incomplete implementation of utf8 but called it utf8. Now it has a complete implementation and calls it utf8mb4. The old utf8 now has an alias "utf8mb3". MySQL would like to switch names, but it needs our help.

Wherever practical change any usage of "utf8" to be "utf8mb4" unless you explicitly need "utf8mb3". There could be some hiccups in the change, especially if you are still running version 5.x. Once you get fully moved to 8.0, sailing should be sooth again.

When a Stored Routine is created, the established charset and collation are stored as an attribute of the routine. Probably that was stored as "utf8".

The fix is to establish that which charset you are using (utf8mb4 or utf8mb3) and re-create the Stored Routine,

Any valid characters that existed in utf8mb3 (the old "utf8") will work in utf8mb4 (the future "utf8"). (Not vice versa, such as with many Emoji.)

Tracking down MySQL utf8 vs. utf8mb4 Issues

2 Answers2