Drupal: Force MySQL to run in ANSI compatability mode

Submitted by Peter Majmesku on Tue, 11/21/2017 - 03:56

Since Drupal (8) is supporting various database management systems (DBMS) like MySQL and PostgreSQL, not all database queries are behaving like directly in the DBMS, let's say MySQL.

E.g. Drupal is only supporting "full group by" which can be annoying. Because this removes the ability to group result rows on one specific database column. You will get errors like this:

ServiceCompetence.idServiceCompetence' isn't in GROUP BYSELECT *, GROUP_CONCAT(DISTINCT Services_serviceKeyFROM ServiceCompetence  
  WHERE Authorities_idAuthorities 

Such a query won't work in Drupal:

SELECT FROM ServiceCompetence sc
        WHERE Authorities_idAuthorities 
        GROUP BY sc

But luckily you can force Drupal to ANSI compatibility mode, for supporting features like single group by. Just use this 1 line of code:

  * @var Drupal\Core\Database\Database
$database->query('SET SESSION sql_mode = "ANSI,TRADITIONAL"'); 

More details can be found in this issue posting on Drupal.org: https://www.drupal.org/project/drupal/issues/344575#comment-1146016