前言
在 MySQL 中，timestamp 采用 4 字节进行存储，取值范围是 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC [1]。如果时间来到 2038 年，而官方没有采取行动，那么 MySQL 将无法正常使用。
问题复现
查看 MySQL PID
(base) [root@ip-10-0-0-22 logs]# pidof mysqld
8763
查看版本并运行些 SQL 语句
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-10-10 00:00:42 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into student_score select * from student_score limit 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
查看当前连接的processlist id 和 对应的 OS_ID
mysql> show full processlist;
+----+------+-----------+--------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+----------+-----------------------+
| 3 | root | localhost | rudonx | Query | 0 | starting | show full processlist |
+----+------+-----------+--------+---------+------+----------+-----------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.threads where processlist_id=3\G;
*************************** 1. row ***************************
THREAD_ID: 28
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: rudonx
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: select * from performance_schema.threads where processlist_id=3
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 8797
1 row in set (0.00 sec)
ERROR:
No query specified
修改系统时间
mysql> system date -s '2038-10-10';
Sun Oct 10 00:00:00 UTC 2038
查看错误日志，发现 MySQL会立刻挂掉，理由也很简单，当前不支持 2038 年以后的日期
2022-10-10T00:00:06.002600Z 0 [Note] InnoDB: Buffer pool(s) load completed at 221010 0:00:06
2038-10-10T00:00:00.000799Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 504921488258ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2038-10-10T00:00:08.371723Z 3 [Warning] Current time has got past year 2038. Validating current time with 5 iterations before initiating the normal server shutdown process.
2038-10-10T00:00:08.371952Z 3 [Warning] Iteration 1: Current time obtained from system is greater than 2038
2038-10-10T00:00:08.372164Z 3 [Warning] Iteration 2: Current time obtained from system is greater than 2038
2038-10-10T00:00:08.372375Z 3 [Warning] Iteration 3: Current time obtained from system is greater than 2038
2038-10-10T00:00:08.372585Z 3 [Warning] Iteration 4: Current time obtained from system is greater than 2038
2038-10-10T00:00:08.372804Z 3 [Warning] Iteration 5: Current time obtained from system is greater than 2038
2038-10-10T00:00:08.372949Z 3 [ERROR] This MySQL server doesn't support dates later then 2038
……
2038-10-10T00:00:09.544779Z mysqld_safe mysqld from pid file /home/mysql3306/mysql.pid ended
问题分析
使用 strace 进行分析，在每次运行语句时，都会调用 gettimeofday()，其中的 tv_sec 表示自 1970 年到语句执行时经过的秒数，如下：
8797 00:00:42.645223 recvfrom(45, "\3select now()", 13, MSG_DONTWAIT, NULL, NULL) = 13 <0.000032>
8797 00:00:42.645339 gettimeofday({tv_sec=1665360042, tv_usec=645375}, NULL) = 0 <0.000036>
8797 00:01:23.828475 recvfrom(45, "\3insert into student_score selec"..., 62, MSG_DONTWAIT, NULL, NULL) = 62 <0.000030>
8797 00:01:23.828556 gettimeofday({tv_sec=1665360083, tv_usec=828575}, NULL) = 0 <0.000025>
8797 00:01:33.596279 recvfrom(45, "\3show full processlist", 22, MSG_DONTWAIT, NULL, NULL) = 22 <0.000029>
8797 00:01:33.596356 gettimeofday({tv_sec=1665360093, tv_usec=596375}, NULL) = 0 <0.000024>
8797 00:01:38.749061 recvfrom(45, "\3select * from performance_schem"..., 64, MSG_DONTWAIT, NULL, NULL) = 64 <0.000030>
8797 00:01:38.749141 gettimeofday({tv_sec=1665360098, tv_usec=749161}, NULL) = 0 <0.000025>
```
`
查看 pstack，会发现每次运行 SQL 语句都走到了/sql/sql_parse.cc 的逻辑中
```shell
#9 0x0000000000bf885b in Protocol_classic::get_command (this=0x7f290c001d90, com_data=0x7f2976c7cde0, cmd=0x7f2976c7cdd0) at /usr/local/mysql-5.7.19/sql/protocol_classic.cc:965
#10 0x0000000000c9523c in do_command (thd=thd@entry=0x7f290c000d40) at /usr/local/mysql-5.7.19/sql/sql_parse.cc:938
#11 0x0000000000d4f680 in handle_connection (arg=arg@entry=0x7f2918000b20) at /usr/local/mysql-5.7.19/sql/conn_handler/connection_handler_per_thread.cc:300
```
查看源码发现，sql_parse.cc 中的 dispatch_command 有如下注释，其中说明，在语句执行性会调用 gettimeofday() 系统函数，用于检查时间是否大于 2038，并进行重试 5 次，然后接着就会 shutdown MySQL [2]。
````undefined
if (is_time_t_valid_for_timestamp(thd->query_start_in_secs()) == false) {
/*
If the time has gone past end of epoch we need to shutdown the server. But
there is possibility of getting invalid time value on some platforms.
For example, gettimeofday() might return incorrect value on solaris
platform. Hence validating the current time with 5 iterations before
initiating the normal server shutdown process because of time getting
past 2038.
*/
if (tries > max_tries) {
/*
If the time has got past epoch, we need to shut this server down.
We do this by making sure every command is a shutdown and we
have enough privileges to shut the server down
TODO: remove this when we have full 64 bit my_time_t support
*/
LogErr(ERROR_LEVEL, ER_UNSUPPORTED_DATE);
ulong master_access = thd->security_context()->master_access();
thd->security_context()->set_master_access(master_access | SHUTDOWN_ACL);
error = true;
kill_mysql();
}
}
```
`
从 strace 中也可以看到 调用了 tgkill 来 shutdown mysqld
```shell
8797 00:00:08.372856 write(2, "2038-10-10T00:00:08.372804Z 3 [W"..., 108) = 108 <0.000028>
8797 00:00:08.372929 gettimeofday({tv_sec=2170281608, tv_usec=372949}, NULL) = 0 <0.000027>
8797 00:00:08.373000 write(2, "2038-10-10T00:00:08.372949Z 3 [E"..., 94) = 94 <0.000029>
8797 00:00:08.373080 getpid() = 8763 <0.000027>
8797 00:00:08.373148 tgkill(8763, 8792, SIGTERM) = 0 <0.000035>
```
# 解决方案
从 TODO 来看，MySQL 官方后续会支持 my_time_t 64 位的版本，截止到目前，还有 16 年之久，留给 MySQL 的时间还有很多。
# 参考文档
[1] [https://dev.mysql.com/doc/refman/5.7/en/datetime.html](https://dev.mysql.com/doc/refman/5.7/en/datetime.html)
[2] [Source Code /sql/sql_parse.cc](https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/sql/sql_parse.cc)
**如果您有其他问题，欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)
