- 🇺🇸United States greg.1.anderson
Fix typo in issue summary ("TRADITIONAL" instead of "NO_ENGINE_SUBSTITUTION" in Mariadb list of modes implied by TRADITIONAL). Add bolding for differing modes.
The mysql module sets the SQL mode to 'ANSI,TRADITIONAL' when creating a database connection to mysql, mariadb, or percona database servers, however the ANSI and TRADITIONAL SQL Modes are not implemented consistently across these three database servers resulting in queries that will work on one, but not another.
Mysql interprets ANSI as REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY (https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-combo)
Mariadb interprets ANSI as REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE (https://mariadb.com/kb/en/sql-mode/#ansi)
Mysql interprets TRADITIONAL as STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION (https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_traditional)
Mariadb interprets TRADITIONAL as STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, NO_AUTO_CREATE_USER. (https://mariadb.com/kb/en/sql-mode/#traditional)
Connect to a mysql database server with a working drupal database, set the sql_mode to 'ANSI,TRADITIONAL' and issue a database query that should fail on ONLY_FULL_GROUP_BY. The query will fail as expected.
mysql> SET sql_mode='ANSI,TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> select nid, type from node group by type;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY
clause and contains nonaggregated column 'smus.node.nid' which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by
Connect to a mariadb database server with a working drupal database, set the sql_mode to 'ANSI,TRADITIONAL' and issue a database query that should fail on ONLY_FULL_GROUP_BY. The database query will not fail as expected.
MariaDB [db]> SET sql_mode='ANSI,TRADITIONAL';
Query OK, 0 rows affected (0.000 sec)
MariaDB [db]> select nid, type from node group by type;
+-----+------------------------+
| nid | type |
+-----+------------------------+
| 547 | home_page |
| 447 | landing_page |
| 537 | rad_news |
| 262 | rad_page |
| 758 | staff_and_contributors |
+-----+------------------------+
5 rows in set (0.001 sec)
The mysql driver (now module) used to explicitly list the SQL modes, however in #3185231: Use combination SQL modes instead of explicit modes → this was changed to use the ANSI and TRADITIONAL Combined SQL Modes.
Rather than using Combination SQL Modes (ANSI, TRADITIONAL) my suggestion would be to go back to explicitly listing all of the desired SQL Modes so that they are consistent across mysql, mariadb, and percona database servers.
Determine which SQL modes are desired.
Needs work
11.0 🔥
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
Fix typo in issue summary ("TRADITIONAL" instead of "NO_ENGINE_SUBSTITUTION" in Mariadb list of modes implied by TRADITIONAL). Add bolding for differing modes.