Feedback Request: Enforcing SQL Mode

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 GRANT command.  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:


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.

Feedback requested

Please let us know whether SQL mode restrictions would solve problems you encounter administering MySQL deployments or not.  We’re interested to hear whether the proposed solution meets needs you have in this area, or whether you have alternative ideas to better solve these problems.

10 thoughts on “Feedback Request: Enforcing SQL Mode

  1. An emphatic ‘Yes!’ to the ability to restrict users changing sql_mode. Regarding the proposed solution, is there a big use-case that an administrator would want to allow users to override the default sql_mode within a subset of allowed modes?

    A simpler (though not as flexible) solution would be to allow an administrator to disable the ability to override sql_mode.

  2. I think that this is a very good idea. Many SQL (G)UIs sent a custom session SQL mode – once SQL code lands in VCS, it’s mostly too late to refactor it.

    What I dislike is that it is a configuration variable…. I would tend to leave SQL mode globally in (remove session override), and make either an user permission that allows session override (less favored) or only administratively configurable session modes per user.

    Background is that some damn legacy projects need a way to disable strictness – but there are dozens oft projects who don’t. Hundreds of databases, each with per user configuration.

    Currently an administrator cannot enforce one setting so that all coders must obey (sad reality – enforcing is mostly the only way, otherwise the coders tend to forget it…) while leaving a possibility for backwards compatibility for only a few users.

  3. This new option looks promising.

    One thing it does not address is the new proposed modes of NO_ZERO_DATE and NO_ZERO_IN_DATE which breaks several systems I use because of the common until now usage of the “zero timestamp”.

    Both the zero timestamp (“0000-00-00 00:00:00”) and the zero date (“0000-00-00”) for all their bad characteristics were a way to avoid NULL values in a column (which is good) and provide a clear default setting. Currently with timestamp columns there is no easy to way provide an alternative “default value”. Additionally the values provided to a timestamp column are by default using the local timezone settings on the server. That is you can not explicitly provide a UTC value to prevent timezone conversion and ensure the value does not change if the server’s timezone varies etc.

    So only partly related to your topic but to help people come up with a way to provide such default settings it would be convenient when migrating away from zero-timestamps or zero-dates to have an alternative mechanism to define easy defaults, even if they may be a site- or server-specific setting.

    When I have tried to move away from the zero timestamp I have tried to use the very awkward “1970-01-01 01:00:01” value. That’s UTC “0” + 1 second in CET as CET is one hour after UTC. (in Dec/Jan). Being able to define a server- or db-wide “constant” such as define constant UTC_1 = _utc( “1970-01-01 00:00:01” ) and using UTC_1 as a timestamp default would be nicer.

    Once a setting like this is possible (and cleaner) then the application needs to be aware of this special value, and all tables modified to use this type of value rather than the current “zero” defaults you want to deprecate. Making it easier to specify such magic values will make it easier to move away from what was for many people a great “default value to set” even if it was not a valid date or timestamp…

    1. “Both the zero timestamp (“0000-00-00 00:00:00”) and the zero date (“0000-00-00”) for all their bad characteristics were a way to avoid NULL values in a column (which is good) and provide a clear default setting. Currently with timestamp columns there is no easy to way provide an alternative “default value”. ”

      Just out of curiosity – what is so bad about NULL in case of date -/ time fields?

      You have no overhead, defined behaviour and a unique, distinguishable value that is not a datetime / time value, so that no misinterpretation of the magic value is possible ( eg… in your case that this is a 0 unixtimestamp, or maybe a wrong cast from false, or maybe the result of a wrong conversion of a datetime value with another format… too many possibilities…)

      What you describe sounds like yet another nightmare to me – I understand what you try to achieve, but I fail to see the motive / reason…

    2. Hi Simon,

      Thanks for the feedback! I may be missing your point, but would it not work to define a function instead of a global variable? For example, UTC_1() could return 1970-01-01 01:00:01. I suppose you might intend for this to be available without worrying about privileges, or perhaps in some context where functions are not allowed. But in general, it seems at least possible.

  4. Hi Todd!
    That is a very good approach:
    It provides a means to enforce a strict policy for those who want to,
    without breaking compatibility for those who stick to old lazy habits.

  5. Please make it easier for developer to remove/add one sql mode on a per session basis (maybe with a function/procedure in sys?).

    like this:
    mysql> SET SESSION sql_mode += ‘NO_ZERO_IN_DATE’
    (note the += instead of the =)

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.