MySQL silently truncating your data

MySQL in its standard configuration has this wonderful “feature” of
truncating your data if it can’t fit in the field.

mysql> create table foo (bar varchar(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (bar) values ("12345");
Query OK, 1 row affected, 1 warning (0.00 sec)

In comparison, PostgeSQL does:

psql=> create table foo (bar varchar(4));
CREATE TABLE
psql=> insert into foo (bar) values ('12345');
ERROR:  value too long for type character varying(4)

You can make MySQL do the right thing by setting the SQL
Mode
option to
include STRICT_TRANS_TABLES or STRICT_ALL_TABLES. The difference is that the
former will only enable it for transactional data storage engines. As much as
I’m loathed to say it, I don’t recommend using STRICT_ALL_TABLES, as an error
during updating a non-transational table will result in a partial
update, which is probably worse than a truncated field. Setting the mode
to TRADITIONAL includes both these and a couple of related issues
(NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO) You can set the
mode using:

  • On the command line:

    --sql-mode="TRADITIONAL"
  • In /etc/mysql/my.cnf:

    sql-mode="TRADITIONAL"
  • At runtime:

    SET GLOBAL sql_mode="TRADITIONAL"
    SET SESSION sql_mode="TRADITIONAL"

Just say no to databases that happily throw away your data

4 thoughts on “MySQL silently truncating your data

  1. Rob Wilderspin
    on said:

    It’s wrong to call it ‘silent truncation’ when in your example MySQL points out that there was a warning.

    mysql> create table foo (bar varchar(4));
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into foo (bar) values (“12345”);
    Query OK, 1 row affected, 1 warning (0.00 sec)

    mysql> show warnings;
    +———+——+——————————————+
    | Level  | Code | Message  |
    +———+——+——————————————+
    | Warning | 1265 | Data truncated for column ‘bar’ at row 1 |
    +———+——+——————————————+
    1 row in set (0.00 sec)

    Perhaps it’s more accurate to call it a ‘rarely noticed truncation’…

    Your point I guess is that you don’t like MySQL defaulting to not setting the TRADITIONAL sql mode, and there are arguments for and against that.

    I wish it was set, personally, which would push it back as an error to the application, but in truth you should really be doing data validification at the application level already.  Garbage in, garbage out, as they say.

  2. It was not truncated “silently”, there was a warning. If you don’t like MySQL, don’t use it, but please stop spreading FUD.

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.

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=""> <s> <strike> <strong>