pt-table-sync v3.5.7 does not produce the correct SQL statements to properly sync tables containing JSON columns, at least testing against MySQL 5.7.12.

Steps to Reproduce

Pre-test Setup:

Test:

What’s going on:

pt-table-sync emits the following SQL:

The INSERT statement works fine, but the DELETE fails to delete the row with `id`='2', because the AND `data`='{"baz": "quux"}' portion of the WHERE clause will result in the query matching zero rows.

Verify the incorrect contents of the test_to table with the following:

That should return the following output:

Witness the row with id=2 still exists in the table, and was not deleted as it should have been.

With JSON columns, the DELETE statement would need to look like this:

You can non-destructively confirm this using this command:

That should return the following output:

Post-test Cleanup: