ISSUE: timestamp and pt-table-checksum on MySQL 5.6

Polyglot Projects Percona Toolkit
1

shell# pt-table-checksum --replicate percona.checksums

sql> show create table checksums\G
Create Table: CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
…omit…
[B]ts timestamp NOT NULL,[/B]
PRIMARY KEY (db,tbl,chunk),
KEY ts_db_tbl (ts,db,tbl)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

On MySQL 5.6,we must explicitly define the default value for the timestamp field. Otherwise, when running the tool, you will get the following warning:

Error checksumming table xxx: Error executing checksum query: Checksum query for table xxx caused MySQL error 1364:
Level: Warning
Code: 1364
Message: Field ‘ts’ doesn’t have a default value

2

I’ve run pt-table-checksum on MySQL 5.6.10 and this is the checksums table I get:

CREATE TABLE checksums (
[…]
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
[…]

Can you please share the my.cnf?

I’m using pt-table-checksum 2.1.8

3

Verison Info:

pt-table-checksum v2.2.1 (percona-toolkit-2.2.1.tar.gz)
mysqld 5.6.10 (mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz)

my.cnf:

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/20.pid
skip-name-resolve
skip-external-locking
max_connections = 300
key_buffer_size = 384M
max_allowed_packet = 64M
table_open_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 100
query_cache_size = 32M
thread_concurrency = 16
lower_case_table_names = 1
explicit_defaults_for_timestamp ## 5.6.10

log-bin=mysql-bin
binlog_format=row
expire-logs-days=8
log-error=20.err
tmp_table_size = 512M
server-id = 20
binlog_row_image = full ## 5.6.10

innodb_data_home_dir = /data/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DSYNC
innodb_file_per_table = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 600

GTID 5.6.10

log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1

Thanks miguelangelnieto for your reply…:slight_smile: