pt-table-checksum creates mysql logfile entries about unsafe statement

Hello,
Long time user, first time poster to these forums.

We are performing backups with innobackupex. After that, we are doing a checksum of the tables so that we can verify that our data’s integrity is intact if/when we perform a restore. So I’m doing this:

echo “flush tables with read lock; set global read_only = ON;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf
pt-table-checksum --defaults-file=/var/lib/mysql/defaults.cnf --chunk-size=1000000 --chunk-size-limit=0 --recursion-method none >/dev/null

The problem is, I notice these messages written to our /var/log/mysqld.log at the time my job is running:

2015-08-09 04:00:15 43539 [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 ‘ClearingFillsDb’, ‘CsvFileTransactions’, ‘1’, NULL, NULL, NULL, COUNT() AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(’#’, tradedate, inserttime, account, type, exchangename, symbol, clearingside, fillprice, fillqty, transactiontype, strike, callputfutureorstock, clearingfilestrikemultiplier, CONCAT(ISNULL(inserttime), ISNULL(fillqty), ISNULL(clearingfilestrikemultiplier)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM ClearingFillsDb.CsvFileTransactions /checksum table/
… (about 80 more lines like that, until) …
2015-08-09 04:00:54 43539 [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’, ‘user’, ‘1’, NULL, NULL, NULL, COUNT(
) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(’#’, host, user, password, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv, show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv, create_routine_priv, alter_routine_priv, create_user_priv, `ev

That last line is how it’s written to the log, truncated like that.

Anyway, this does not look comforting to me. Is there something else I should be doing? Notice that this is happening off-hours so database downtime is not a concern for us. Specifically, I adjusted check-size and chuck-size-limit to avoid some warning messages that I didn’t care for.

We are running mysql-community-server-5.6.22-2.el7.x86_64 and percona-toolkit-2.2.14-1.noarch on Centos 7.

Thanks.
-Mike Schwager

Hi GreyGnome;

The warning is nothing to worry about, as the order of the rows is not important for the checksum.

That aside, there are quite a few related bugs open. The “fix” for this is for the tool to order by the PK, but there appears to be a MySQL bug that still causes the warning in that case. So given that, I’m guessing this is not going to get fixed anytime soon. So you’ll just have to ignore it at this point.

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

-Scott

Thanks. “Don’t worry, nothing you can do.” works for me :slight_smile: