SQL modes

1. Determine the current SQL_MODE value

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

2. Default SQL Modes

  1. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
  2. https://mariadb.com/kb/en/mariadb/sql_mode/

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.

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 5.8 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

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

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

  1. Using GROUP BY and selecting an ambiguous column
  2. Inserting the non standard zero date into a datetime column
  3. Inserting a 20 character string into a 10 character column
  4. Division by zero
  5. 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:

  1. https://dev.mysql.com/doc/refm[..]ema-quick-start.html
  2. https://www.digitalocean.com/c[..]ur-mysql-5-7-upgrade

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