Skip to content

# sql_mode: a suggestion

MySQL sql_mode January 11, 2009January 11, 2009

I thought this deserves more than a comment on my previous post on the subject, in which I expressed the opinion that sql_mode is undesired.

Back to reality: sql_mode is here right now. What else can be done?

#### Suggestion

Is there anything to do about sql_mode? I believe so: make it strict by default.

I wish to stress the following:

- sql_mode is empty by default (which means it is very permissive).
- It is not documented enough. Many DBAs don’t really know anything about it.
- As a MySQL instructor, I have repeatedly witnessed the look of surprise on a student’s face when he learns that in the default configuration, an overflowed value is silently truncated to match the data type. My personal observation: people don’t like the default configuration.
- Stricter is better, if you code your application this way right from the start (naturally, trying to make changes after 3 years of development invites trouble).

The fact that sql_mode is empty by default, and has been like that for the past years, means that many applications have been built which are not strict. The developers and DBAs never changed the sql_mode (remember they didn’t know about sql_mode). Many of the applications would not work under stricter sql_mode.

Please imagine the following: assume we keep the sql_mode feature as it is, but make it ‘TRADITIONAL’ by default. Now imagine we did that 5 years ago.

Most, if not all of today’s applications were strict. Those who would disable STRICT_TRANS_TABLES or ERROR_FOR_DIVISION_BY_ZERO would be the exception, and they would have to do it knowingly, understanding the impact of their actions.

May I offer my suggestion, then, that future MySQL installations come with a strict sql_mode. At the very least allow the user (in the Windows installer, for example, or via ncurses, dpkg-reconfigure, other) to choose her level of strictess. The windows installer already asks about encodings and engines.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
1
Leave a Reply

1 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
1 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Roland Bouman

“May I offer my suggestion, then, that future MySQL installations come with a strict sql_mode. ” On windows, they do. The typical Next/Next/Finish installation procedure leaves you with this setting: # Set the SQL mode to strict sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” STRICT_TRANS_TABLES is “a strict sql_mode”. If you do the “Detailed Configuration” procedure you are prompted for it, and you can *uncheck* strict mode (it is on by default and explicitly disrecommended to disable it, see:) What always blew my mind is how different things are on Linux. That said I can’t say it has posed much of a problem to me because… Read more »

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.