Beware of MySQL Data Truncation

February 8, 2009

Author

Peter Zaitsev

Insight for DBAs

Share this Post:

Here is nice gotcha which I’ve seen many times and which can cause just a minefield for many reasons.
Lets say you had a system storing articles and you use article_id as unsigned int. As the time goes and you see you may get over 4 billions of articles you change the type for article_id to bigint unsigned but forget linked tables.

<br>mysql> create table article_comment(article_id int unsigned not null, comment_id int unsigned not null, key(article_id));<br>Query OK, 0 rows affected (0.11 sec)<br><br>mysql> insert into article_comment values(4300000000,1);<br>Query OK, 1 row affected, 1 warning (0.00 sec)<br><br>mysql> insert into article_comment values(4300000001,1);<br>Query OK, 1 row affected, 1 warning (0.00 sec)<br><br>mysql> insert into article_comment values(4300000002,1);<br>Query OK, 1 row affected, 1 warning (0.00 sec)<br><br>mysql> select * from article_comment;<br>+------------+------------+<br>| article_id | comment_id |<br>+------------+------------+<br>| 4294967295 |          1 |<br>| 4294967295 |          1 |<br>| 4294967295 |          1 |<br>+------------+------------+<br>3 rows in set (0.00 sec)<br>

mysql> create table article_comment(article_id int unsigned not null, comment_id int unsigned not null, key(article_id)); Query OK, 0 rows affected (0.11 sec) mysql> insert into article_comment values(4300000000,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article_comment values(4300000001,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article_comment values(4300000002,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from article_comment; +------------+------------+ | article_id | comment_id | +------------+------------+ | 4294967295 | 1 | | 4294967295 | 1 | | 4294967295 | 1 | +------------+------------+ 3 rows in set (0.00 sec)

For table “article” itself you would notice the issue quickly if you would forget to alter it as inserts will fail because of primary key collisions. But for linked tables like above inserts work you just get a warning:

<br>mysql> show warnings;<br>+---------+------+--------------------------------------------------------------+<br>| Level   | Code | Message                                                      |<br>+---------+------+--------------------------------------------------------------+<br>| Warning | 1264 | Out of range value adjusted for column 'article_id' at row 1 |<br>+---------+------+--------------------------------------------------------------+<br>1 row in set (0.06 sec)<br>

mysql> show warnings; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'article_id' at row 1 | +---------+------+--------------------------------------------------------------+ 1 row in set (0.06 sec)

And in my experience very few people mind to check the warnings promptly.

So two things happen, one is nastier than the other. First the comments end up associated to the wrong article (4294967295) which can be quite a problem in particular if these are private comments. The other thing which tends to happen – you get A LOT of comments associated with this article and few systems are designed with YouTube capacity to handle millions of comments per item.

Note the question in this case is not like there is just one article which has insane number of comments but a as a all articles outside of allowed range have it:

<br>mysql> explain select count(*) from article_comment where article_id=4300000000;<br>+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+<br>| id | select_type | table           | type | possible_keys | key        | key_len | ref   | rows | Extra                    |<br>+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+<br>|  1 | SIMPLE      | article_comment | ref  | article_id    | article_id | 4       | const |    3 | Using where; Using index |<br>+----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+<br>1 row in set, 1 warning (0.00 sec)<br><br>mysql> show warnings;<br>+---------+------+--------------------------------------------------------------+<br>| Level   | Code | Message                                                      |<br>+---------+------+--------------------------------------------------------------+<br>| Warning | 1264 | Out of range value adjusted for column 'article_id' at row 1 |<br>+---------+------+--------------------------------------------------------------+<br>1 row in set (0.00 sec)<br>

MySQL optimizer behaves rather strange in such case (at least in MySQL 5.0) – instead of instantly saying there is no such value (because value is out of range what could be stored in the column) it truncates the value, performs index lookup, scanning all rows (just 3 in my trivial example) and when filtering them out because value really does not match the where clause.

This makes such error even harder to catch – you will not see wrong comments for a lot articles, you will see 0 while queries will just run insanely slow.

So how to avoid this problem ?

It is actually quite easy – you just need to enable strict mode:

<br>mysql> set sql_mode=STRICT_ALL_TABLES;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> insert into article_comment values(4400000000,1);<br>ERROR 1264 (22003): Out of range value adjusted for column 'article_id' at row 1<br>

1	<br>mysql> set sql_mode=STRICT_ALL_TABLES;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> insert into article_comment values(4400000000,1);<br>ERROR 1264 (22003): Out of range value adjusted for column 'article_id' at row 1<br>

Note however strict mode should not be taken lightly – it is good for reliability but many applications will need to be fixed before they start working properly with this mode.