Read more

Understanding SQL compatibility modes in MySQL and MariaDB

Henning Koch
March 13, 2018Software engineer at makandra GmbH

MySQL and MariaDB have an SQL mode setting Show archive.org snapshot which changes how MySQL behaves.

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

The SQL mode value is comprised of multiple flags like "STRICT_TRANS_TABLES, NO_ZERO_IN_DATE". Each flag activates or disables a particular behavior.

The default SQL mode varies widly between versions of MySQL and MariaDB. In general, more recent versions of MySQL and MariaDB have stricter settings than older versions, and MySQL has stricter settings than the more liberal MariaDB.

If your app explodes after changing SQL servers, you might be able to fix it by setting the SQL mode to the value in your previous server version.

Important SQL mode flags

These SQL modes will probably give you the most headaches:

STRICT_TRANS_TABLES, STRICT_ALL_TABLES

Introduced in MySQL 5.7, these enable Strict SQL mode Show archive.org snapshot . In SQL Strict mode MySQL is likely to throw an error if an INSERT or UPDATE has invalid or missing values. Without strict mode, MySQL will try more to not throw an error.

For instance, if a STRING exceeds the length of a field, MySQL will throw an error in strict mode. Without strict mode it would truncate the string to the maximum column size.

ONLY_FULL_GROUP_BY

With this enabled (default in MySQL 5.7), you can no longer SELECT values from a group without an aggregate function.

E.g. you will probably find queries like these in old apps:

SELECT name, address, MAX(age) FROM users GROUP BY name;

With ONLY_FULL_GROUP_BY this now explodes with:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

You need to make sure that any selected value is either (1) the value you group by or (2) passed to an aggregate function like MIN or MAX. If you don't care about the aggregate function, you can use ANY_VALUE:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

TRADITIONAL

This is a shortcut that activates multiple SQL mode flags.

Which SQL mode flags it activates varies between MySQL, MariaDB and their individual versions (see below).

Note that TRADITIONAL does not necessarily make the SQL server behave more liberally in what it accepts. In particular, it enables strict mode, but disables ONLY_FULL_GROUP_BY in all versions we've seen.

Because of this you might want to list individual flags instead of using TRADITIONAL.

Default SQL mode settings

In general, more recent versions of MySQL and MariaDB have stricter settings than older versions, and MySQL has stricter settings than the more liberal MariaDB.

Maria DB 10 Traditional MariaDB 10.0 default MariaDB 10.2 default MySQL 5.7 Traditional MySQL 5.7 Default MySQL 5.6 Default MySQL 5.6 Traditional
STRICT_TRANS_TABLES yes - yes yes yes - yes
STRICT_ALL_TABLES yes - - yes yes - yes
NO_ZERO_IN_DATE yes - - yes yes - yes
NO_ZERO_DATE yes - - yes yes - yes
ERROR_FOR_DIVISION_BY_ZERO yes - yes yes yes - yes
NO_AUTO_CREATE_USER yes - yes yes yes - yes
NO_ENGINE_SUBSTITUTION - - yes yes yes yes yes
ONLY_FULL_GROUP_BY - - - - yes - -

Changing the SQL mode

Globally

To change the SQL for your entire database server, edit /etc/mysql/my.cnf and add this to the [mysqld] section:

[mysqld]
sql_mode=""

Now restart MySQL:

sudo service mysql restart

For a single Rails project

If you only want to change the SQL mode for one project, you can add this to your config/database.yml:

development:
  adapter: mysql2
  database: myproject
  ...
  variables:
    sql_mode: "NO_AUTO_CREATE_USER, STRICT_TRANS_TABLES"

If you make any changes to your database.yml, remember to also change it here:

  • database.sample.yml
  • all colleagues that have a local database.yml (which is in .gitignore)
  • staging servers
  • production servers
Henning Koch
March 13, 2018Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2018-03-13 15:39)