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 
4

Such a query won't work in Drupal:

SELECT FROM ServiceCompetence sc
        WHERE Authorities_idAuthorities 
$authorityId
        GROUP BY sc
.Services_serviceKey

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

Tags

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.