Incorrect String Value MySQL & Python for 3 Byte characters

Question

Before I begin, I know this question is highly asked, but i've viewed nearly every SO question that fits various search terms relating to this issue.

My issue is that I am trying to store a 3 Byte character in utf8, not 4 bytes as many questions ask, specifically chinese characters.

DB character set is utf8 with utf8_general_ci collation. The column, table, and connection char sets are also utf8.

MySQLdb.connect(host, user, passwd, schema, charset ='utf8', use_unicode=True)

I know use_unicode is implied, but I like the verbosity.

The warning I'm getting is: Warning: Incorrect string value: '\xE5\x9B\xBD' for column 'mycolumn' at row 1

This results in SELECTS of that field, resulting in a ?. My terminal supports utf8, as well as my system encoding being utf8.

Any ideas would help. Calling .decode('utf8') on this value also does not solve the issue.

Brandon Deo · Accepted Answer · 2017-08-09 19:05:29Z

1

Turns out this ended up being a problem with a stored procedure. From the MYSQL docs.

If CHARACTER SET and COLLATE attributes are not present, the database character set and collation in effect at routine creation time are used. To avoid having the server use the database character set and collation, provide explicit CHARACTER SET and COLLATE attributes for character data parameters.

The connection and client encoding was UTF8, however the DB still remained as latin1. This was causing issues because the stored procedure was created when the DB encoding was latin1.

TLDR; Add CHARSET utf8 to your stored procedure parameters.

answered Aug 9, 2017 at 19:05

Brandon Deo

4,3034 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Incorrect String Value MySQL & Python for 3 Byte characters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related