pt-table-checksum causes MySQL warning with ONLY_FULL_GROUP_BY

[monty solomon]

pt-table-checksum 2.2.3

Server version: 5.5.31-30.3-log Percona Server (GPL), Release rel30.3, Revision 520

The server is configured with

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

07-15T23:00:06 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `mysql`.`time_zone_transition` FORCE INDEX(`PRIMARY`) WHERE ((`time_zone_id` < ?) OR (`time_zone_id` = ? AND `transition_time` < ?)) ORDER BY `time_zone_id`, `transition_time` /*explain past lower chunk*/: DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause [for Statement "EXPLAIN SELECT COUNT(*), '0' FROM `mysql`.`time_zone_transition` FORCE INDEX(`PRIMARY`) WHERE ((`time_zone_id` < ?) OR (`time_zone_id` = ? AND `transition_time` < ?)) ORDER BY `time_zone_id`, `transition_time` /*explain past lower chunk*/" with ParamValues: 0='1', 1='1', 2='-1830383032'] at /usr/bin/pt-table-checksum line 10903.

07-15T23:00:06 Skipping chunk 4 of mysql.time_zone_transition because MySQL chose no index instead of the PRIMARYindex.

07-15T23:00:06 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `mysql`.`time_zone_transition` FORCE INDEX(`PRIMARY`) WHERE ((`time_zone_id` > ?) OR (`time_zone_id` = ? AND `transition_time` > ?)) ORDER BY `time_zone_id`, `transition_time` /*explain past upper chunk*/: DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause [for Statement "EXPLAIN SELECT COUNT(*), '0' FROM `mysql`.`time_zone_transition` FORCE INDEX(`PRIMARY`) WHERE ((`time_zone_id` > ?) OR (`time_zone_id` = ? AND `transition_time` > ?)) ORDER BY `time_zone_id`, `transition_time` /*explain past upper chunk*/" with ParamValues: 0='1734', 1='1734', 2='2140045225'] at /usr/bin/pt-table-checksum line 10903.

[Daniel Nichter (daniel-nichter)]

Odd, the tool should set the sql mode it needs.

[monty solomon (monty+launchpad)]

The tool adds NO_AUTO_VALUE_ON_ZERO to the existing sql mode

# DSNParser:1612 29063 DBI::db=HASH(0x2a9d4f0) SELECT @@SQL_MODE
# DSNParser:1643 29063 DBI::db=HASH(0x2a9d4f0) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:1612 29063 DBI::db=HASH(0x2aa2450) SELECT @@SQL_MODE
# DSNParser:1643 29063 DBI::db=HASH(0x2aa2450) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:1612 29063 DBI::db=HASH(0x2aaa2a0) SELECT @@SQL_MODE
# DSNParser:1643 29063 DBI::db=HASH(0x2aaa2a0) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/

[monty solomon (monty+launchpad)]

Here is the code excerpt from pt-table-checksum v 2.2.5

      $sql = 'SET @@SQL_QUOTE_SHOW_CREATE = 1'
            . '/*!40101, @@SQL_MODE=\'NO_AUTO_VALUE_ON_ZERO'
            . ($sql_mode ? ",$sql_mode" : '')
            . '\'*/';

[monty solomon (monty+launchpad)]

This bug is related to bug 1019479.

[monty solomon (monty+launchpad)]

The default SQL mode in MySQL 5.7 includes ONLY_FULL_GROUP_BY as of MySQL 5.7.5.

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

[monty solomon (monty+launchpad)]

What are the plans to fix this bug? It is open more than two years.

[monty solomon (monty+launchpad)]

execute failed: Illegal mix of collations for operation 'concat_ws' at /usr/bin/pt-table-checksum line 11268.

execute failed: Illegal mix of collations for operation 'concat_ws' at /usr/bin/pt-table-checksum line 10658.

% /usr/bin/pt-table-checksum --version
pt-table-checksum 2.2.15

mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

[monty solomon (monty+launchpad)]

Comment 7 is a new issue. I will open a new bug.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-617