Incorrect String error when adding emoji to a database through a form

Question

I can successfully add emoji (i.e. utf8mb4 data) to tables using mysql using the terminal.

When my Python Flask website tries to send emoji to the same database table and field, the database returns the following incorrect string error:

(1366, "Incorrect string value: '\xF0\x9F\x98\x8E' for column 'p_description' at row 1")

UPDATE

• I read the suggested threads and am not seeing how to block the server from overriding my local character-set-server setting.
• Added SET NAMES utf8mb4; before INSERT INTO, but no effect
• I checked as suggested in the comments (thanks!) and yes, all my database character settings are showing utf8mb4 where it is possible. This is what I get from mysql:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

=>

| Variable_name | Value |

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| collation_connection | utf8mb4_unicode_ci |

| collation_database | utf8mb4_unicode_ci |

| collation_server | utf8mb4_unicode_ci |

10 rows in set (0.00 sec)

I am using an html form, jQuery, AJAX and Python Flask to send the data to the database. Python calls the SQL stored procedure below.

Stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addWish`(
    IN p_title varchar(45),
    IN p_description varchar(1000),
    IN p_user_id bigint
)
BEGIN
    SET NAMES utf8mb4; insert into tbl_wish(
        wish_title,
        wish_description,
        wish_user_id,
        wish_date
    )
    values
    (
        p_title,
        p_description,
        p_user_id,
        NOW()
    );
END

**Q: How do I force my website to send data to my database in the utf8mb4 format?

Answer 1

Python Flask defaults to communicating with MySQL in MySQL's utf-8, i.e. it can't handle the full utf8mb4 range (which includes emojis). Flask will override the database charset settings, including the character-set-server setting in my.cf. Adding the following setting to the Flask app fixes the problem by forcing it to communicate with MySQL in utf8mb4:

app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'