When What You See Is Not What You Get
"If it's not supported, do the next best thing. Silently." – MySQL design philosophy
Anyone who has some experience in IT knows that all operating systems, programming languages, and software products come with their own set of idiosyncrasies. Some optimists like to refer to these as features, but the rest of us know them best as Gotchas. In today’s article, I’m going to highlight a gotcha that pertains to MySQL’s overflow handling of numeric values that are outside the permissible range of the column data type. In the coming months, I plan on covering a few of the more common Gotchas so that you can better avoid the pitfalls associated with each.
Just so that we’re on the same page here, a gotcha is not the same as a bug. The latter is a flaw in a program or system that causes it to produce incorrect and/or unexpected results. Moreover, bugs tend to act as a portal for nefarious programs and can thus be considered to be security vulnerabilities. A "gotcha", on the other hand, is a feature or function, which does indeed work as the author intended, but not as we the Database Administrators (DBAs) might expect it to.
Today’s Gotcha Exposed
A lot of database tables are populated by means of an automated script or application. Inevitably, one of these will attempt to insert an invalid value into a column. This event can be handled by the database in one of several ways:
1. It can throw an error so that your script or application may deal with it by either proceeding or aborting.
2. It can truncate the value.
3. It can guess at what might be a better and/or more acceptable value and insert it instead.
In case you haven’t guessed it, MySQL takes the second course of action (more or less). While both the second and third options make for more hassle-free batch processing, both can come back to bite you when you revisit the stored data at a later time.
Consider the following table that stores information about menu items, including its ID, name, price, description, and caloric value.
CREATE TABLE `menu_items` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`price` decimal(5,2) DEFAULT NULL,
`description` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`calories` smallint(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
The following sample insert statement contains data that is typical for a breakfast menu, except for the price, which is off the charts at over $50,000 and seven (?!) decimal points. In most other databases, that would cause the record to be rejected.
INSERT INTO menu_items VALUES (
'Two of our famous Belgian Waffles with plenty of real maple syrup',
But not MySQL. It accepts the new row, but does issue a warning:
1 row(s) affected, 1 warning(s):
1264 Out of range value for column 'price' at row 1
A quick SELECT ALL on the menu_items table shows that the price was set to 999.99, which is presumably the largest possible value for the field:
SELECT * FROM `test`.`menu_items`;
1 row(s) returned
6, Belgian Waffles, 999.99, Two of our famous Belgian Waffles with plenty of real maple syrup, 650
And this is exactly what the makers of MySQL intended. As described in the docs:
“If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.”
Can We Force MySQL to Throw an Error Instead?
There are in fact a couple of ways to force MySQL to throw an error when the data does not conform to the field specs. MySQL can operate in different SQL modes so that its behavior matches a site’s global operating requirements, or to that of specific applications. Setting the sql_mode variable affects MySQL’s flexibility on SQL syntax as well as the data validation checks it performs.
Now, there is quite a long list of individual and combined sql_mode values, but the following are arguably the most important:
• ANSI: Changes SQL syntax and storage engine behavior to conform more closely to standard SQL.
• STRICT_TRANS_TABLES: If a value cannot be inserted as given into the table, then abort the statement.
• TRADITIONAL: Makes MySQL behave more like a “traditional” SQL database system. Setting the sql_mode to this value will raise an error instead of a warning when inserting an invalid value into a column.
The sql_mode can either be set at server startup or at runtime. To set the SQL mode at server startup, use the --sql-mode="[modes]" option on the command line, or sql-mode="[modes]" in a config file such as myconfig.cnf on Unix or myconfig.ini on Windows, where “[modes]” is a comma-delimited list of the available modes.
The SQL mode can be cleared at any time by setting it to an empty string, as in --sql-mode="" on the command line, or sql-mode="" in a config file. It may also be changed at either the global or session level at runtime via the SET statement:
SET GLOBAL sql_mode = '[modes]';
SET SESSION sql_mode = '[modes]';
Setting the GLOBAL variable affects the database behavior for all clients that connect from then on whereas setting the SESSION variable affects only the current client. Note that the default scope is for the SESSION so that the following statement would apply to the current user only:
SET sql_mode = 'STRICT_TRANS_TABLES';
We can determine the sql_mode value at both the current global and session level by using the following statements:
After applying the 'STRICT_TRANS_TABLES' mode to the current session, issuing an SQL statement with an invalid value as we did earlier now fails with the following error:
Error Code: 1264. Out of range value for column 'price' at row 1
The innodb_strict_mode for InnoDB
Another way to make MySQL behave more like other databases – at least when running the InnoDB storage engine – is to use the innodb_strict_mode setting. Like sql_mode, innodb_strict_mode controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.
Like the sql_mode, innodb_strict_mode may be set via a config file or the command prompt. However, the innodb_strict_mode may only contain a Boolean value of 0 for OFF or 1 for ON. Hence, SET innodb_strict_mode = 1; would active strict mode.
In today’s article, we saw how MySQL’s handling of numeric data overflows by truncating values to the largest acceptable value can lead to potential gotchas. Furthermore, we learned how to turn off MySQL’s default handling of overflow conditions to match that of other database providers. In the coming months, we’ll encounter other MySQL Gotchas.
See all articles by Rob Gravelle