pt-table-checksum does not work with sql_mode ONLY_FULL_GROUP_BY

[Jaime Sicam]

mysql> set global sql_mode="ONLY_FULL_GROUP_BY";
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
~$ ./pt-table-checksum -h 127.0.0.1 --port=21178

            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
06-30T11:07:41 0 0 9 1 0 0.256 employees.departments
06-30T11:07:44 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`dept_emp` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?) OR (`emp_no` = ? AND `dept_no` < ?)) ORDER BY `emp_no`, `dept_no` /*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 `employees`.`dept_emp` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?) OR (`emp_no` = ? AND `dept_no` < ?)) ORDER BY `emp_no`, `dept_no` /*explain past lower chunk*/" with ParamValues: 0='10001', 1='10001', 2='d005'] at ./pt-table-checksum line 7866.

06-30T11:07:44 Skipping chunk 11 of employees.dept_emp because MySQL chose no index instead of the PRIMARYindex.
06-30T11:07:44 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`dept_emp` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?) OR (`emp_no` = ? AND `dept_no` > ?)) ORDER BY `emp_no`, `dept_no` /*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 `employees`.`dept_emp` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?) OR (`emp_no` = ? AND `dept_no` > ?)) ORDER BY `emp_no`, `dept_no` /*explain past upper chunk*/" with ParamValues: 0='499999', 1='499999', 2='d004'] at ./pt-table-checksum line 7866.

06-30T11:07:44 2 0 331603 12 2 2.685 employees.dept_emp
06-30T11:07:44 0 0 24 1 0 0.289 employees.dept_manager
06-30T11:07:45 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?)) ORDER BY `emp_no` /*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 `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` < ?)) ORDER BY `emp_no` /*explain past lower chunk*/" with ParamValues: 0='10001'] at ./pt-table-checksum line 7866.

06-30T11:07:45 Skipping chunk 3 of employees.employees because MySQL chose no index instead of the PRIMARYindex.
06-30T11:07:45 Error executing EXPLAIN SELECT COUNT(*), '0' FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?)) ORDER BY `emp_no` /*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 `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` > ?)) ORDER BY `emp_no` /*explain past upper chunk*/" with ParamValues: 0='499999'] at ./pt-table-checksum line 7866.

[Raghavendra D Prabhu (raghavendra-prabhu)]

I hit a similar bug. This is also a bug in mysql documentation - http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_only_full_group_by - since according to that implicit group by is not covered under ONLY_FULL_GROUP_BY.

In fact, two different error codes are used for same sql_mode - ER_WRONG_FIELD_WITH_GROUP (which is what I should be) and ER_MIX_OF_GROUP_FUNC_AND_FIELDS. Furthermore, former error validates that the field is actually a real field (v/s a string/int/real field like '0' or 1) whereas the later doesn't do that check --

  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list &&
      select_lex->non_agg_field_used() &&
      select_lex->agg_func_used())

non_agg_field_used() ideally shouldn't be true for fields which are not Item::FIELD_ITEM.

I will file a bug with bugs.mysql regarding this.

[Raghavendra D Prabhu (raghavendra-prabhu)]

On a related note,

I would like the ability to define mysql session variables for all/most pt-tools (through --mysql-var variable which can take an array type argument value) before they start since there are several combinations of variables one would like to define (to workaround among other things), and we have seen issues with variables like tx_isolation and sql_mode, also it is hard to test with different values of sql_mode (and countless combinations of these with values of other variables) .

Most of them can be solved by defining session variable (currently the workaround is to set the global variable and toggle back but that is not good).

So, this is more of a wishlist type request.

[Baron Schwartz (baron-xaprb)]

You can currently set arbitrary variables with --set-vars.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Thanks. --set-vars helped.

However, specifically in case of sql_mode it didn't help initially. Checking PTDEBUG output revealed that it is due to the fact that pt-table-checksum itself modifies @@SQL_MODE for 'show create table' and unsets it later. http://sprunge.us/JbFN

So, the workaround I used is

pt-table-checksum --set-vars="@@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'" -h 127.0.0.1

and it works.

(NO_AUTO_VALUE_ON_ZERO because in the code it uses that for some reason)

[Raghavendra D Prabhu (raghavendra-prabhu)]

Ignore the previous comment. It works as normal.

pt-table-checksum --set-vars="sql_mode=''" -h 127.0.0.1 works.

(The mistake I made earlier is that I skipped the single quotes there)

[Raghavendra D Prabhu (raghavendra-prabhu)]

Related to comments #4 and #5, setting sql_mode as in #5 can clobber the @@SQL_MODE set by the script itself.

http://sprunge.us/NOAJ?pl fixes that.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Bug filed under bugs.mysql for the issue mentioned in #1 is http://bugs.mysql.com/bug.php?id=65798

[Raghavendra D Prabhu (raghavendra-prabhu)]

Reported bug for #6 at lp:1078887

[Daniel Nichter (daniel-nichter)]

I doubt we'll be able to make the checksum queries compatible with ONLY_FULL_GROUP_BY. So we should at least document this requirement (i.e. no ONLY_FULL_GROUP_BY). We should probably also look into making the tool simply set the sql_mode it knows it needs. In this case, I can imagine why a user's server has ONLY_FULL_GROUP_BY on by default--to prevent people from abusing aggregate functions--but in our case we know we're correctly exploiting MySQL's extension to/flexibility with aggregate functions.

[Corey Jewett (y-cj)]

I'm still having this issue with version 2.2.1. (Installed from Percona's yum repo)

Running Percona 5.5 with a configuration from the Percona configuration generator. The server wide sql_mode is set with a very strict sql_mode setup. This is fine (actually the dev-team likes it), but I believe there is a regression in pt-table-checksum. I'm not qualified to definitively say that some of the queries in pt-table-checksum should be amended to be compatible with ONLY_FULL_GROUP_BY, but that does seem like a reasonable, maybe? Alternately, the tool needs to be smarter about setting a compatible sql_mode or --set-vars needs to actually take effect before the tool runs the SELECT @@SQL_MODE.

Which brings me to why I believe there is a regression. Earlier commenters say that --set-vars works for them. It did not work for me. I had to modify the script so that the values in --set-vars are applied before @@SQL_MODE is cached. See attached patch.

[Bill Karwin (bill-karwin)]

pt-table-sync is also susceptible to this conflict. There's a query near line 4056 it pt-table-sync that has the same problem.

[Shahriyar Rzayev (rzayev-sehriyar)]

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