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'

Answer 2

I encountered almost the same problem recently, where accented characters were causing PHP json_encode() to complain about "malformed UTF8 characters." Much to-ing and fro-ing in the documentation eventually led me to a paragraph at the bottom of the page 10.5 Character Set Configuration which states:

… when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly.

In fact, character_set_system defaults to utf8 while character_set_server defaults to latin1 — I dare not speculate why.

My solution was to explicitly set character_set_server = utf8 (it defaults to latin1) and collation_server = utf8_general_ci (it defaults to latin1_swedish_ci) in the [mysqld] section of my my.cnf configuration file and then restart the service. The fact that these settings differed from the corresponding *_system settings apparently was the cause of my problem.

Some experimentation has confirmed that character_set_system must be utf8 or the server won't start. The documentation says that character_set_database can be set differently from character_set_server, but I'm not currently equipped to test the effects of this.

Answer 3

If you want to store unicode character into the database you need to Define column character set to utf8mb4.

Also change character set of table if it is required.

it will help you store data in unicode formate

Thanks