MySQL 5 Strict Mode

MySQL 5 "strict " mode applies when either STRICT_TRANS_TABLES or STRICT_ALL_TABLES is included in the list of options supplied to the sql_mode configuration variable. STRICT_ALL_TABLES will cause any attempt to set a column to an invalid value to fail with an error. STRICT_TRANS_TABLES has the same effect, but only if the table is transactional.

If neither of these settings is in effect, MySQL will either accept the update or do a "best fit" of the invalid value into a legal column value. For instance, if you try to assign a string value into an integer column, MySQL may set the value of the column to 0. A warning will be generated whenever such a "truncation" occurs.

Strict mode will also cause errors to occur for missing columns in an INSERT statement, unless that column has an associated DEFAULT clause.

STRICT_ALL_TABLES can have some dubious side effects when you are performing multirow updates or inserts into nontransactional tables. Because there is no rollback capability for a nontransactional table, the error may occur after a certain number of valid row updates have occurred. This means that in the event of a strict-mode error on a nontransactional table, the SQL statement may partially succeed. This is rarely desirable behavior, and for this reason the default setting in MySQL 5.0 is STRICT_TRANS_TABLES.

You can change your strict mode at any time with a SET statement:

SET sql_mode='STRICT_ALL_TABLES'

The strict mode also determines how stored programs deal with attempts to assign invalid values to variables. If either of the strict modes is in effect, then an error will be generated whenever an attempt to assign an invalid value to a variable occurs. If no strict modes are in effect, then only warnings are generated.

Note that this behavior is controlled by the sql_mode settings that are in effect when the program is created, not when it is run. So once a strict stored program is created, it remains strict, even if the sql_mode settings are relaxed later on. In the same way, programs that are created when none of the strict modes are in effect will continue to generate warnings rather than errors when invalid data is assigned, regardless of the sql_mode that is in effect when the program runs.

3.6.1. Stored Program Behavior and Strict Mode

All variables in a MySQL stored program must be declared before usewith the exception of "user" variables, which are prefixed by the @ symbol and may be defined outside of the stored program. Furthermore, variables in MySQL stored programs must be assigned an explicit data type, and this data type cannot change during program execution. In this respect, the MySQL stored program language resembles "strongly typed" languages such as C, Java, and C# rather than dynamically typed languages such as Perl and PHP.

When created in strict mode, as explained in the previous section, stored programs will reject with an error any attempt to assign an invalid or inappropriate value to a variable. Such rejected assignments will include attempts to assign strings to numeric data or attempts to assign values that exceed the storage limitations declared for the variable.

However, when a stored program is created in non-strict mode, MySQL will perform a best attempt to convert invalid data and will generate a warning rather than an error. This allows you tofor instanceassign a string value to a variable defined as an integer. This non-strict behavior can lead to unexpected results or subtle bugs if you do not carefully ensure that you always use variables in ways that are appropriate for their data type. For these reasons it is usually best to create stored programs in strict mode and generate an error that you cannot possibly fail to notice during program testing or execution.

3.6.2. Program Examples

We'll illustrate these differences with an example that compares the behavior of the MySQL stored program in non-strict mode with several other programming languages.

Example 3-17 shows a Java program that intends to concatenate an integer value to a string value with the intention of printing the string "99 bottles of beer on the wall". Unfortunately for the beer, the programmer accidentally declared variable c as an int, rather than as a String. The Java compiler detects this error during compile time when it detects an attempt to assign a string expression to an integer variable, and the program fails to compileno harm done.

Example 3-17. Type checking in a Java program

Now let's look at an equivalent example (in a dynamically typed languagein this case, PHP). In PHP and Perl, variable data types change on the fly as required. In Example 3-18, the variable c started as a number, but when subjected to a string assignment, the data type dynamically changed to a string. The program therefore works as required.

Example 3-18. Dynamic variable typing in PHP