Not the answer you need?
Register and ask your own question!

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

seagenseagen ContributorInactive User Role Beginner
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..
`ts` timestamp NOT NULL,
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

Comments

  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    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
  • seagenseagen Contributor Inactive User Role Beginner
    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..:)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.