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

Bug #1275783 reported by Michael Coburn on 2014-02-03
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Frank Cizmich

Bug Description

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

tags: added: pt-table-checksum
Sebastian (sebastian-seo) wrote :

# 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*/

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*/

Changed in percona-toolkit:
status: New → Confirmed
seikath (seikath) wrote :

Reproduced for pt-table-checksum 2.2.11 and MariaDB 10.0.14

Changed in percona-toolkit:
assignee: nobody → Frank Cizmich (frank-cizmich)
importance: Undecided → Low
importance: Low → Medium
Frank Cizmich (frank-cizmich) wrote :

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) wrote :

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.

Changed in percona-toolkit:
status: Confirmed → Incomplete
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.