Enforcing SQL modes

MySQL Server has an extensive collection of SQL modes which control a range of behavior ranging from compatibility modes for other RDBMS dialects (ANSI_QUOTES, PIPES_AS_CONCAT) to security (NO_AUTO_CREATE_USER) to explicit storage engine selection (NO_ENGINE_SUBSTITUTION) to restricting lossy implicit data conversions (STRICT_TRANS_TABLES, etc.).  These SQL modes allow users to customize behavior in various ways, but all suffer from a common problem – there is no mechanism to restrict which behaviors users can select for themselves.  Any user can make choices which can influence data quality, select inappropriate storage engines, or even accidentally undermine security.  While flexibility has been a key feature of MySQL in this area, additional controls allowing administrators to restrict these options to meet organizational requirements are needed.

This blog post will review recent changes made to SQL modes, propose a possible enhancement in this area, and solicit feedback on the usefulness and usability of this proposal or alternatives.

SQL Modes:  A quick history

SQL modes were introduced as a means for users to tailor MySQL behavior to their needs – people familiar with other syntax from other RDBMS could enable similar syntax in MySQL; users who were not fans of MySQL’s willingness to accept out-of-range values for dates or numeric types could restrict that behavior; security-conscious administrators could prevent accidental creation of a password-less user account from a mistyped GRANTcommand.  Much of MySQL’s default behavior was very permissive – users who wanted a more restrictive experience had to explicitly set the SQL mode.  This started to change with the release of MySQL 5.6, and MySQL 5.7 made significant changes to better align default behavior with RDBMS standards.

In MySQL 5.1 and earlier, the default SQL mode was blank.  This was changed in MySQL 5.6 to default to NO_ENGINE_SUBSTITUTION, and MySQL 5.7 now sets the following as default:

• 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

This results in a more standards-compliant default experience, but still allows flexibility for users demanding legacy-compatible behavior.

Proposal:  Allowed and Required SQL Modes

To help administrators eliminate usage with SQL mode settings which are problematic, we’re proposing adding two new server configuration options:  --sql_modes_allowed and --sql_modes_required.  By using these two options together, DBAs would be able to both restrict usage of problematic SQL modes (--sql_modes_allowed) and enforce usage of specific SQL modes for all users (--sql_modes_required).  A blank value for --sql_modes_allowed/ would mean any SQL mode could be selected by end users (legacy behavior), while a blank value for --sql_modes_required would mean no SQL mode is required for end users (also consistent with legacy behavior).

To ensure consistency, the server would produce errors whenever:

• --sql_mode does not contain all values found in --sql_modes_required.
• --sql_mode contains any value not found in a non-blank --sql_modes_allowed value.
• --sql_modes_required and --sql_modes_allowed are both not blank, and any values exist for --sql_modes_required that are not present in --sql_modes_allowed.

By ensuring --sql_mode has all configured required SQL modes, and only modes found in allowed modes (when defined), new connections will align with organization policies on SQL modes.