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
