GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #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

    Comment


    • #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..

      Comment


      • #4
        Ok, I can reproduce the problem with explicit_defaults_for_timestamp. By default that variable is not enabled in MySQL 5.6:

        http://dev.mysql.com/doc/refman/5.6/en/server-system-variabl es.html#sysvar_explicit_defaults_for_timestamp

        So "On MySQL 5.6,we must explicitly define the default value" is not true. We must explicitly define the default value only if the new explicit_defaults_for_timestamp is enabled. Anyway, is something to take in account so I will open a new bug report on launchpad.

        Thank you!

        Comment


        • #5
          Here is seagen,

          https://bugs.launchpad.net/percona-toolkit/+bug/1163735

          I will discuss it with the developers

          Again, thank you!

          Comment


          • #6
            Always welcome for opensource software

            As you said the truth, actually the bug might be a pleasant surprise to me. Because I would like follow the advice from the error log once mysqld was started. I'll do my best to remove the warnings in it. So I hit the jackpot this time

            Comment

            Working...
            X