UTF-8 sequence check when storing data on SQL query

[yuliu]

I encounter a MySQL error of invalid UTF-8 sequence when Merge is storing datatrace of records to be converted from tables using a non UTF-8 character-set.

No such error on MySQL 5.5, but the error appears on MySQL 5.7 (haven't checked 5.6). It should relate to MySQL but not PHP. However, I haven't found any reference to UTF-8 sequence check yet. Will continue investigation later.

[yuliu]

It's should be relating to MySQL's strict mode: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html . MySQL has some changes on its strict mode since 5.7. I haven't tested the Merge with PostgreSQL or SQLite.

Since this issue only occurs when storing datatrace on records that are being inserted, there are several ways to fix, but I'm not sure which is the best one:

• Use a custom insert_query() for datatrace which uses the IGNORE keyword for inserting: INSERT IGNORE INTO;
• When creating the debuglogs table, set the column message's collation to a collation that doesn't require sequence check ;
• Encode the data for datatrace, BASE64 or UTF8 or something else. However, it'll become harder for debug;
• Disable strict mode in session. Not ideal for real data importing to MyBB's database.

For MySQL users using 5.7 or higher, there are some methods to mitigate this issue:

• Disable MySQL's strict mode temporarily;
• Disable the Merge's logging feature, see this community post.

[euantorano]

Another possible approach:

In the data trace, rather than just using var_export rebuild the array and either skip or empty any strings that look like binary (e.g. preg_match('//u', $field) fails).