{{toc numerate=1}}
===Determine the current SQL_MODE value===
%% SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;%%
===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.
===Setting SQL_MODE===
**my.cnf:**
%%[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION%%
===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 ]>
#|
*| 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## ||
|#
===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:
%%(hl sql)
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
%%(hl sql)
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/refman/5.7/en/performance-schema-quick-start.html
2. https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade
===Strict sql mode and errors===
%%(hl sql)
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