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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select count(*) from article_comment where article_id=4300000000; +----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | article_comment | ref | article_id | article_id | 4 | const | 3 | Using where; Using index | +----+-------------+-----------------+------+---------------+------------+---------+-------+------+--------------------------+ 1 row in set, 1 warning (0.00 sec) 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.00 sec) |
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:
1 2 3 4 5 | mysql> set sql_mode=STRICT_ALL_TABLES; Query OK, 0 rows affected (0.00 sec) mysql> insert into article_comment values(4400000000,1); ERROR 1264 (22003): Out of range value adjusted for column 'article_id' at row 1 |
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.
Nice to know, and indeed, I hardly ever check the warnings…
However, this seems like a bit of a futile problem.
In the case comments can have the same id (i.e. comment #1 can exist on several articles) it would seem logical to me to simply create a unique key on the columns ‘article_id’ and ‘comment_id’ in the table preventing this issue.
But more importantly; if the database were to be designed coherently, the article with ID 4300000000 would not get created in the first place, so how are people going to comment on an article that never got created in the first place? 😉
uh oh…. typed to quickly…
“you change the type for article_id to bigint unsigned but forget linked tables.”
I would never do that, of course. Please forget my second remark
🙂
Onno,
In consulting we do not always deal with perfectly designed systems plus this is of course simplified example
It does not matter what comment_id is – quite common comment_id is unique by itself (so we can fetch data by comment id easily) which may reduce the need of such index. Not to mention people hunting for performance may be easy on unique indexes because (in innodb) it blocks insert buffer.
right you are 🙂