pt-table-checksum fails if explicit_defaults_for_timestamp is enabled in 5.6

Reported by Miguel Angel Nieto on 2013-04-03
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Daniel Nichter

Bug Description

There is a new variable in MySQL 5.6 to avoid timestamp working in a non-standard behavior:

"Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp system variable at server startup."

So, if you follow warning advice, pt-table-checksum won't work anymore because it creates the checksums table without any default on the timestamp column:

04-03T09:13:30 Error checksumming table mysql.user: Error executing checksum query: Checksum query for table mysql.user caused MySQL error 1364:
    Level: Warning
     Code: 1364
  Message: Field 'ts' doesn't have a default value
    Query: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, 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`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, CONCAT(ISNULL(`plugin`), ISNULL(`authentication_string`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`user` /*checksum table*/

tags: added: mysql-5.6 pt-table-checksum
Changed in percona-toolkit:
status: New → Confirmed
milestone: none → 2.2.3
Brian Fraser (fraserbn) on 2013-05-03
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
Brian Fraser (fraserbn) on 2013-05-03
Changed in percona-toolkit:
status: Confirmed → In Progress
Changed in percona-toolkit:
assignee: Brian Fraser (fraserbn) → nobody
Changed in percona-toolkit:
status: In Progress → Triaged
Changed in percona-toolkit:
importance: Undecided → High
Changed in percona-toolkit:
assignee: nobody → Daniel Nichter (daniel-nichter)
status: Triaged → In Progress
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers