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

$cat simplejava.java
package simplejava;

public class SimpleJava {

 public static void main(String[] args) {
 String b;
 int a;
 int c;
 a=99;
 b="Bottles of beer on the wall";
 c=a+" "+c;

 System.out.println(c);

 }
}

$javac simplejava.java
simplejava.java:11: incompatible types
found : java.lang.String
required: int
 c=a+" "+c;
 ^
1 error

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

$cat simplephp.php
$c=0; #c is a number
 $c=$a." ".$b; #c is now a string

 print $c."
";
?>

$php simplephp.php
99 Bottles of beer on the wall

Now let's look at the equivalent non-strict MySQL stored program version of this logic, as shown in Example 3-19. This procedure has the same data type error as in the previous examplesthe variable c should be defined as a VARCHAR, but it is instead declared as an INT.

Example 3-19. MySQL stored program non-strict type checking

CREATE PROCEDURE strict_test( )
BEGIN

 DECLARE a INT;
 DECLARE b VARCHAR(20);
 DECLARE c INT;

 SET a=99;
 SET b="Bottles of beer on the wall";
 SET c=CONCAT(a," ",b);
 SELECT c;
END
 --------------

Query OK, 0 rows affected (0.01 sec)

mysql> call strict_test( );
+------+
| C |
+------+
| 99 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'c' at row 1 |
+---------+------+----------------------------------------+
2 rows in set (0.01 sec)

Without the strict mode, MySQL does not generate an error when the attempt to supply a string to an integer value occurs, nor does it dynamically convert the data type of the integer variables. Instead, it assigns only the numeric part of the string expression to the integerleading to an unexpected and erroneous result. However, if we had created the procedure when in strict mode, we would have generated a runtime error, as shown in Example 3-20.

Example 3-20. Stored program type checking in strict mode

mysql> CALL strict_test( );
ERROR 1406 (22001): Data too long for column 'b' at row 1

It's almost always preferable for your programs to operate in strict mode. While a non-strict program will sometimes be able to continue where a strict program would fail with an error, the risk that the non-strict program will exhibit unexpected and inappropriate behaviors is usually too high. Remember that the behavior of a stored program depends on the setting of the variable sql_mode when the program is created,not when the program is run.

Stored programs should almost always operate in strict mode to avoid unpredictable behavior when invalid data assignments occur. The strict mode for a stored program is determined by the setting of the sql_mode variable in effect when the program is created, not when the program is run.

As always, the onus is on the programmer to ensure that data types are used appropriately. As Bruce Eckel noted in his article "Strong Typing vs. Strong Testing" (http://www.mindview.net/WebLog/log-0025), strong typing in computer languages only provides an illusion of safetytrue validation of correct behavior can only be obtained through strong testing . You should not assume that by declaring a variable as being of a certain type you are implicitly performing validation of the data being applied to that variable.