Month: 1 月 2018

MySQL 低版本对 TIMESTAMP 字段 DEFAULT 值设置的一个问题

by Orange

今天在线上改表的时候，遇到了一个问题。改表完成之后，MyBatis Select 改表前插入的数据会抛异常。排查后发现是因为一个 TIMESTAMP  类型的字段值被写成了 0000-00-00 00:00:00  ，无法被转换成 java 的 java.sql.Timestamp 类型，导致出错。

但是之前在使用 MySQL 的时候一直没有遇到过这个问题，于是搜索了一下，发现了这么一个链接：

https://bugs.mysql.com/bug.php?id=68040

这里反馈了一个 MySQL 本身的 Bug，会导致 ALTER table  的时候，TIMESTAMP  类型的值没有按照预期的正确设置，而是被设置成了 0000-00-00 00:00:00  。该问题在 MySQL 5.6.11 中被修复了。官方的 Release Note 在这里：

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html#mysqld-5-6-11-bug

  • ALTER TABLE tbl_name ADD COLUMN col_name TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP inserted 0000-00-00 00:00:00 rather than the current timestamp if the alteration was done in place rather than by making a table copy. (Bug #68040, Bug #16076089)

为了确认该问题，特地找了两台不同版本的 MySQL 来尝试一下：

没有问题的版本：

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------+
| Variable_name           | Value              |
+-------------------------+--------------------+
| innodb_version          | 5.6.28             |
| protocol_version        | 10                 |
| slave_type_conversions  |                    |
| version                 | 5.6.28             |
| version_comment         | 20170228           |
| version_compile_machine | x86_64             |
| version_compile_os      | Linux              |
+-------------------------+--------------------+
7 rows in set (0.01 sec)

mysql> ALTER TABLE time_test ADD COLUMN update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM time_test;
+----+------+---------------------+
| id | name | update_time         |
+----+------+---------------------+
|  1 | 111  | 2018-01-01 00:00:01 |
|  2 | 222  | 2018-01-01 00:00:01 |
+----+------+---------------------+
2 rows in set (0.01 sec)

mysql>

有问题的版本：

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.24                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.07 sec)

mysql> ALTER TABLE time_test ADD COLUMN update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM time_test;
+----+------+---------------------+
| id | name | update_time         |
+----+------+---------------------+
|  1 | 111  | 0000-00-00 00:00:00 |
|  2 | 222  | 0000-00-00 00:00:00 |
|  3 | 333  | 0000-00-00 00:00:00 |
|  4 | 444  | 0000-00-00 00:00:00 |
+----+------+---------------------+
4 rows in set (0.09 sec)

mysql>