Restoring a dump of binary column, made with mysqldump, warns of invalid string

[Ryan Brothers]

I am seeing a backwards-incompatible change in MySQL starting with 5.6.27 related to saving binary data in a blob where warnings are now raised such as:

Warning (Code 1300): Invalid utf8 character string: '9CED9D'

I believe it's related to this item in the MySQL changelog at https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-27.html: "MySQL sometimes produced no warning when it was unable to interpret a character in a given character set. (Bug #20238729)".

There are a couple bugs about it such as https://bugs.mysql.com/bug.php?id=79317, where the workaround suggested is to put "_binary" before the data in the sql query.

Even if you do that, you still get the warnings when loading a mysqldump of a database that has binary data - https://bugs.mysql.com/bug.php?id=80150.

My question is could an option be added in Percona Server to suppress this warning completely as it didn't exist in MySQL versions earlier than 5.6.27?

Or if not, could you confirm the new correct way to insert binary data into a blob is to prefix it with _binary? And if so, could mysqldump be updated to account for this?

Thanks for your help.

[Laurynas Biveinis (laurynas-biveinis)]

I don't think we will add an option to restore pre-5.6.27 behavior. The upstream considerations of how string literals are interpreted and that _binary needs to be prefixed apply to Percona Server the same. mysqldump indeed needs fixing, and let's keep this bug open for this (referring to http://bugs.mysql.com/bug.php?id=80150)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1742