pt-table-checksum causes MySQL warning with ONLY_FULL_GROUP_BY

Reported by monty on 2013-07-16
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Unassigned

Bug Description

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.

affects: percona-server → percona-toolkit
Daniel Nichter (daniel-nichter) wrote :

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

tags: added: pt-table-checksum sql-mode
Changed in percona-toolkit:
status: New → Confirmed
milestone: none → 2.2.5
summary: - pt-checksum has problems with ONLY_FULL_GROUP_BY
+ pt-table-checksum causes MySQL warning with ONLY_FULL_GROUP_BY
Changed in percona-toolkit:
importance: Undecided → Medium
Changed in percona-toolkit:
milestone: 2.2.5 → none
monty (monty+launchpad) wrote :

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 (monty+launchpad) wrote :

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 (monty+launchpad) wrote :

This bug is related to bug 1019479.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers