SQL modes

1. Determine the current SQL_MODE value

 SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

2. Default SQL Modes

ONLY_FULL_GROUP_BY

Non-deterministic grouping queries will be rejected.

STRICT_TRANS_TABLES

Invalid and missing-value data exchanging statements will result in an error instead of a warning.

NO_ENGINE_SUBSTITUTION

Ensures that the server will not revert to a different storage engine for a table.

NO_AUTO_CREATE_USER

This is to prevent the GRANT statement from automatically creating new user accounts unless authentication information is specified.

3. Setting SQL_MODE

my.cnf:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

4. sql_mode strict - why it is a good idea

Unless you want some major backlash, and possibly to kill any possibility of major hosting companies upgrading to 5.7, you must provide an easy way for MySQL 5.7 to revert to backward-compatible, non-strict mode.

The choice between doing this in 5.7 or 8.0 is immaterial, in my opinion. It will be painful anytime you do it.

I think MySQL should reach out to the dev teams of the top apps that primarily use MySQL, and provide some free consulting to help them test against strict mode and fix problems that are discovered. -- Bill Karwin

4.1. MariaDB

MariaDB version	Default
<= 10.1.6
>= 10.1.7	`NO_ENGINE_SUBSTITUTION` `NO_AUTO_CREATE_USER`
10.2.4	`STRICT_TRANS_TABLES` `ERROR_FOR_DIVISION_BY_ZERO` `NO_AUTO_CREATE_USER` `NO_ENGINE_SUBSTITUTION`

4.2. MySQL

MySQL version	Default
<= 5.6.5
>= 5.6.6	`NO_ENGINE_SUBSTITUTION`
5.7	`ONLY_FULL_GROUP_BY` `STRICT_TRANS_TABLES` `NO_ZERO_IN_DATE` `NO_ZERO_DATE` `ERROR_FOR_DIVISION_BY_ZERO` `NO_AUTO_CREATE_USER` `NO_ENGINE_SUBSTITUTION`
>= 8.0	`ONLY_FULL_GROUP_BY` `STRICT_TRANS_TABLES` `NO_ZERO_IN_DATE` `NO_ZERO_DATE` `ERROR_FOR_DIVISION_BY_ZERO` `NO_ENGINE_SUBSTITUTION`

5. How To Prepare For SQL strict mode

If you enable SQL strict mode, this is a short summary what you can expect.

The MySQL performance_schema is a diagnostic feature which is enabled by default on MySQL 5.6 and above. Using the performance_schema, it's possible to write a query to return all the statements the server has encountered that have produced errors or warnings.

Enable the performance schema.

Assuming that the Performance Schema is available, it is disabled by default. To enable it, start the server with the performance_schema variable enabled. For example, use these lines in your my.cnf file:
```
[mysqld]
performance_schema	
```
To verify successful initialization, use this statement:

 mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Check for SQL STRICT mode violations

Using GROUP BY and selecting an ambiguous column
Inserting the non standard zero date into a datetime column
Inserting a 20 character string into a 10 character column
Division by zero
Inserting a negative value into an unsigned column

Hunt the poor queries:

report statements that produce errors or warnings:

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

report statements that produce only errors

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
 `SUM_ERRORS` > 0
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

sources:

6. Strict sql mode and errors

INSERT INTO stable_page SET 
version_id = 1, 
comment_on_id = 0, 
description = '', 
parent_id = 0, 
created = UTC_TIMESTAMP(), 
modified = UTC_TIMESTAMP(), 
commented = UTC_TIMESTAMP(), 
depth = 3, 
owner_id = 2, 
user_id = 2, 
title = 'far', 
tag = 'some/totally/far', 
supertag = 'some/totally/far', 
body = '<[The issue is bigger than any single scandal, I told him. As headlines have exposed the troubling inner workings of company after company, startup culture no longer feels like fodder for gentle parodies about ping pong and hoodies. It feels ugly and rotten. Facebook, the greatest startup success story of this era, isn’t a merry band of hackers building cutesy tools that allow you to digitally Poke your friends. It’s a powerful and potentially sinister collector of personal data, a propaganda partner to government censors, and an enabler of discriminatory advertising.]>', 
body_r = '<blockquote>The issue is bigger than any single scandal, I told him. As headlines have exposed the troubling inner workings of company after company, startup culture no longer feels like fodder for gentle parodies about ping pong and hoodies. It feels ugly and rotten. Facebook, the greatest startup success story of this era, isn’t a merry band of hackers building cutesy tools that allow you to digitally Poke your friends. It’s a powerful and potentially sinister collector of personal data, a propaganda partner to government censors, and an enabler of discriminatory advertising.</blockquote> ', 
body_toc = '', 
edit_note = '', 
minor_edit = 0, 
page_size = 576, 
latest = 1, 
ip = '0.0.0.0', 
page_lang = 'en';

1 row(s) affected, 2 warning(s):
1364 Field 'menu_tag' doesn't have a default value
1364 Field 'keywords' doesn't have a default value