Difference between strict_all_tables and strict_trans_tables

MySQL has default behavior that’s just plain wrong. I’ve covered some quirks with MySQL not null behavior and offered up using the SQL Mode strict_all_tables as a solution.

As a review, SQL Mode changes the way MySQL executes SQL statements and it’s often used to make MySQL behave. There are several switches that I would turn on by default, like only_full_group_by. You can change settings for yourself (just your connection) or for everybody (change it at the server level).

There’s another SQL Mode option, strict_trans_tables, that’s similar in intent but not in behavior to strict_all_tables. Both strict_all_tables and strict_trans_tables are meant to prevent invalid values from being inserted into your columns (such as preventing null values in a not null column). The difference between the two happens when three criteria are met:

The table that you are inserting into or updating is not transactional (in other words, you’re not using InnoDB)
You’re updating or inserting multiple rows
Your first row is valid but one after that is not

In this circumstance, strict_trans_tables allows you to insert rows, issuing a warning if there’s an invalid value, but silently converting it. The option strict_all_tables will stop inserting or updating as soon as there’s an invalid row, and it will throw an error. Let’s take a look at the difference.

First, we create MyISAM (non-transactional) table:

Create Table WidgetsMyISAM
(
     WidgetID Int Not Null
    ,Name varchar(50) Not Null
)
Engine=MyISAM;

Set our SQL mode:

Set Sql_Mode = 'strict_all_tables';

Now, let’s see what happens when we try to insert some data:

Insert Into WidgetsMyISAM (WidgetID, Name)
Values
 (1,'Widget XYZ')
,('a','Widget ABC');

We get a big fat error:

ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'WidgetID' at row 2

However, one row was still inserted:

mysql> Select * From WidgetsMyISAM;
+----------+------------+
| WidgetID | Name       |
+----------+------------+
|        1 | Widget XYZ |
+----------+------------+
1 row in set (0.00 sec)

Let’s reset and try the other SQL mode:

Truncate Table WidgetsMyISAM;
Set Sql_Mode = ‘strict_trans_tables’;

What happens when we insert now?

Insert Into WidgetsMyISAM (WidgetID, Name)
Values
 (1,'Widget XYZ')
,('a','Widget ABC');

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> Show Warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'WidgetID' at row 2 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Select * From WidgetsMyISAM;

+----------+------------+
| WidgetID | Name       |
+----------+------------+
|        1 | Widget XYZ |
|        0 | Widget ABC |
+----------+------------+
2 rows in set (0.00 sec)

All of the records were inserted, we got a warning, and now we have bad data, because WidgetID was silently converted from ‘a’ to 0. You can avoid this problem by always using InnoDB with either SQL mode. In that case, no records would be inserted if any of the rows had invalid values.

If you must use MyISAM, then you should avoid modifying multiple rows at a time (since this problem only arises for modifications of more than one row). If this is not possible, then you must decide which is worse: a partial update (strict_all_tables) or invalid values (strict_trans_tables).

MySQL is full of quirks and things that aren’t obvious. Let me help you through some of these pitfalls. I’m writing a book to get people like you up to speed with MySQL. Sign-up below to get updates.

Want to hear more about what I’ve learned about MySQL? You can subscribe below and I’ll send you updates on a new book I’m writing that helps you find the goodies in MySQL. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.



 No spam


Tagged with:
Posted in Databases, IT
One comment on “Difference between strict_all_tables and strict_trans_tables
  1. Brett Carter says:

    Yes, but what happens when *both* are enabled as in ‘TRADITIONAL’ mode?

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>