pt-table-checksum - Unsafe statement written to the binary log using statement format

[Michael Coburn]

pt-table-checksum sets binlog_format=STATEMENT but this leads to Warning messages in error log:

140131 10:46:00 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'production', 'thumbsets', '311161', 'PRIMARY', '407937296', '407938395', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `clip_id`, `original_thumbnail_id`, `availability_id`, `frameset_id`, `total_size`, CONCAT(ISNULL(`availability_id`), ISNULL(`frameset_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `production`.`thumbsets` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '407937296')) AND ((`id` <= '407938395')) /*checksum chunk*/

This is fixed in PS 5.5.32-31.0 as long as we also include an ORDER BY on a PK column.

So this request is to add:

ORDER BY `PRIMARY KEY COLUMN`

Thanks

https://bugs.launchpad.net/percona-server/+bug/1132194
http://bugs.mysql.com/bug.php?id=42415

[Sebastian (sebastian-seo)]

# pt-table-checksum --version
pt-table-checksum 2.2.9

mysql> SELECT @@version, @@version_comment;
+---------------+----------------------------------------------------------------------------+
| @@version | @@version_comment |
+---------------+----------------------------------------------------------------------------+
| 5.6.15-56-log | Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061 |
+---------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

2014-07-20 11:10:40 2675 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'example_db', 'perm_templ_items', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `templ_id`, `perm_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `example_db`.`perm_templ_items` /*checksum table*/

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce with PS 5.6.20 and pt-table-checksum 2.2.10

2014-09-03 12:52:49 18977 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'mysql', 'time_zone_name', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `name`, `time_zone_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`time_zone_name` /*checksum table*/
2014-09-03 12:52:49 18977 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'mysql', 'time_zone_transition', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `time_zone_id`, `transition_time`, `transition_type_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`time_zone_transition` /*checksum table*/

[seikath (seikath)]

Reproduced for pt-table-checksum 2.2.11 and MariaDB 10.0.14

[Frank Cizmich (frank-cizmich)]

It's worth pointing out that although a warning is issued, this particular "REPLACE ... SELECT" query is not unsafe, since the order of the returned rows do not affect the outcome.
Nevertheless, the noise generated by the warnings is a problem.

[Frank Cizmich (frank-cizmich)]

I've tried adding ORDER BY 'PK' but I'm still getting the same warning.
Tried on PS 5.5.38 and PS 5.6 , also MySQL 5.5.37 and 5.6
I think this has yet to be fixed on the database side.
http://bugs.mysql.com/bug.php?id=73896

Changing status to incomplete until more info comes in.

[Shahriyar Rzayev (rzayev-sehriyar)]

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