Don't use the ANSI SQL mode since it has different meanings for different MySQL versions (and breaks MySQL 5.7 support in Drupal 7)

Unhandled exceptions are at least major.

I can explain what is causing this. Drupal uses a special alias SQL Mode called ANSI when connecting to MySQL initially, to ask it to behave more standards compliant:

/includes/database/mysql/database.inc:79
'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",

The problem with using an alias is that the behavior of what is included in it can differ between MySQL releases. Here is ANSI in 5.5/5.6/5.7:

5.5: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
5.6: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
5.7: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI

So I suggest removing "ANSI" and replacing it with each of the individual sub-modes if they are desired. This is not a problem for D8, which supports ONLY_FULL_GROUP_BY (which coincidentally is also enabled by default without this set sql_mode line).

This is very MySQL 5.7 specific, hope nobody minds that I am changing the title.

What do you think about marking this a duplicate of #567148: Use ONLY_FULL_GROUP_BY for MySQL and re-opening that one for backport to D7?

I think that might be unfair on third party modules (which would have behavior changes without a major version bump).

To clarify on a point: this is not a MySQL bug either (although I have since had it added to the upgrading page):
> The ONLY_FULL_GROUP_BY mode is also now included in the modes comprised by the ANSI SQL mode.

Since MySQL 5.7 now has an ANSI compliant group-by option, it is included in the ANSI sql-mode alias. D7 supports the subset of what was ANSI up until MySQL 5.7. If the actual supported modes are used rather than the alias, then D7 can be made to support all versions of MySQL at the same.

Attached you can find the patch to fix this.

As noted by Morgan Tocker and documented in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo, the ANSI combined sql mode includes ONLY_FULL_GROUP_BY since mysql57, which breaks Drupal Support.

+1 on explicit mode-setting rather than an alias with a wandering meaning.

Agreed with David. This is the most straightforward fix, so let's do.

If we're going to stop using that we should do it in Drupal 8 too, but also what about this point from the above-linked MySQL docs?:

ANSI mode also causes the server to return an error for queries where a set function S with an outer reference S(outer_ref) cannot be aggregated in the outer query against which the outer reference has been resolved. This is such a query:

SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);

Here, MAX(t1.b) cannot aggregated in the outer query because it appears in the WHERE clause of that query. Standard SQL requires an error in this situation. If ANSI mode is not enabled, the server treats S(outer_ref) in such queries the same way that it would interpret S(const).

Is there a way we can avoid losing that behavior, or are we stuck changing it too?

(Similar documentation is also present for earlier versions of MySQL.)

Sigh, MySQL. morgantocker: Is there a way to enable that behavior other than setting ANSI mode specifically?

No, I believe the manual is correct here. My suggestion would be to apply this fix only for D7. Since D8 can support ONLY_FULL_GROUP_BY (#567148), it is safe to also leave ANSI enabled.

Using version detection is also too nuanced.

Besides consistency, one reason to consider this for Drupal 8 and beyond is this: What happens if MySQL 5.8 or 5.9 or later changes the meaning of ANSI again?

It's too bad if we lose the behavior for outer references described above, although it's an unusual type of query and at least we're going from throwing an error to not throwing an error (so it's highly unlikely it will break anything in practice). So if we have to lose that in Drupal 7 as the price for supporting MySQL 5.7, we can live with it :)

The modes NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER are deprecated and may be removed in a future release... but in general it is not common that SQL modes change as they can affect how statements replicated to slaves are processed.

Morgan and I discussed this issue at ZendCon. My recommendation is as follows:

1) For D8, leave as is. The change in 5.7 shouldn't have any effect in D8.

2) For D7, let's go ahead and make the change in #7. It should not break any existing queries. A few edge case query strings will as a result become legal, so there's no BC break. The worst case scenario is that there will be a few edge case query styles that are legal in only late-issue Drupal 7, but become illegal again in D8. To which my honest response is ¯\_(ツ)_/¯.

It is unfortunate that MySQL's various and sundry flags are not more consistent and thought-out, but it is what it is. Morgan, please warn us if you're going to change ANSI again in future versions, or at least ensure that it is a strict union of various other flags. :-) That will make issues like this much easier in the future.

Setting back to RTBC for D7.

Sounds good to me.

#7 Works for me, thanks Gryp.

+1 for RTBC

Ran into the this error running D7, MySQL 5.7.9. The patch made the error go away.

#7 Worked for me as well (Drupal 7.41, MySQL 5.7.9)

This is in David's hands now...

I also tested this and solved some problems for me. A vote for RTBC

Anything else we can do to move it forward?

This is not a bug, so re-qualifying as a task. The bug is in Views, that generates totally bogus queries in some cases. The one in the OP is probably not the worst one, mix of aggregated and non-aggregated columns (queries like SELECT a, MAX(b) FROM t) are much worse, because there is no correct answer.

The patch sounds reasonably pragmatic to me.

@Damien

This isn't just a bug in Views. In my case, the error showed up while using the Migrate module.

@RoloDMonkey: Well, I haven't said it's *only* a bug in Views. Other modules might generate bogus queries too.

Hm, I'm still not sure about doing this for Drupal 7 but not 8 - however there isn't consensus on doing it in Drupal 8 and it's clear we need it much more for Drupal 7 given the practical effects, so let's do it.

Committed to 7.x - thanks!

FWIW, I ran into this in my own custom module work by trying to use EntityFieldQuery and propertyOrderBy with MySQL 5.7.11 on a local dev box. I downgraded to 5.6.29 and the error went away.

    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'node')
      ->entityCondition('bundle', array('story', 'feature'))
      ->propertyCondition('status', 1)
      ->fieldCondition('field_homepage_image', 'fid', NULL, 'IS NOT NULL')
      ->fieldCondition('taxonomy_vocabulary_2', 'tid', $targets, 'IN') // $targets is an array of taxonomy tids
      ->propertyOrderBy('created', 'DESC')
      ->range(0, 50);
    $result = $query->execute();
    $nids = array_keys($result['node']);

@c4ilus: This issue was committed and added to a release months ago. Since then, there have been other core security patches. I hope you are not running a version of D7 so old that this patch still applies?

#2616488-23: [meta] MySQL 5.7 / MariaDb 10.1.* support