MySQL Gotcha : Where 1,000,000 = 1.00

As I always said, when come to open source databases, don't use MySQL with default settings for anything involves money. If you're stuck with MySQL and can't switch to a real database system like PostgreSQL , set the default MySQL server sql mode to traditional.

Yesterday, I was hit by the default MySQL very forgiving auto data truncation when inserting data to a table. The bloody database suppose to save one million but store one instead ! Let's illustrate this with a simple example.

1. Create a sample table in your MySQL console.
mysql > CREATE TABLE foo (total DECIMAL(15,2) NULL) COLLATE='utf8_general_ci' ENGINE=InnoDB;

mysql > desc foo;
+-----+-----------------+----+---+-------+-----+-------+
| Field | Type          | Null | Key | Default | Extra |
+-----+-----------------+----+---+-------+-----+-------+
| total | decimal(15,2) | YES  |     | NULL    |       |
+-----+-----------------+----+---+-------+-----+-------+
1 row in set (0.01 sec)

2. Insert 1 million into the sample table.
mysql > INSERT INTO foo values('1,000,000');
Query OK, 1 row affected, 1 warning (0.09 sec)

3. Check what have we inserted. The value of 1 million has became 1.
SELECT * FROM foo;
+-----+
| total |
+-----+
|  1.00 |
+-----+
1 row in set (0.00 sec)

4. What is going on here ? Let's check the default MySQL sql mode for this session. No mode was set, which is the default behaviour.
mysql > SELECT @@SESSION.sql_mode;
+------------------+
|@@SESSION.sql_mode|
+------------------+
|                    |
+------------------+
1 row in set (0.00 sec)

5. To prevent this, we need to change the default MySQL mode so it will behave like a traditional database system by switching to traditional mode.
mysql > SET sql_mode = 'TRADITIONAL';                                                                                                  
Query OK, 0 rows affected (0.00 sec)

6. Try to reinsert 1 million again.
mysql > INSERT INTO foo values('1,000,000');
ERROR 1366 (HY000): Incorrect decimal value: '1,000,000' for column 'total' at row 1

7. To make the traditional as default mode permanently, add this line to the my.cnf configuration file under the [mysqld] section. In Ubuntu 13.04,
$ sudo vi /etc/mysql/my.cnf

[mysqld]
sql_mode = TRADITIONAL

$ sudo service mysql restart

Should you switch your MySQL server to traditional mode ? Yes and no. If you're running an existing legacy application, leave it as it, otherwise, it will break the application. But on your local development machine, go ahead as you can catch the bug earlier.

No comments:

Post a Comment