按出生日期查询报错

Tao · 2019 年10 月 10 日 06:24

为提高效率，提问时请尽量提供详细背景信息，问题描述清晰可优先响应。以下信息点请尽量提供：

【系统版本 & kernel 版本】 CentOS Linux release 7.6.1810 (Core) Linux tidb-1 3.10.0-957.el7.x86_64
【TiDB 版本】 5.7.25-TiDB-v3.0.3
【磁盘型号】
【集群节点分布】
【数据量 & region 数量 & 副本数】
【问题描述（我做了什么）】表结构如下 CREATE TABLE uaa_user ( id bigint NOT NULL, name VARCHAR(50) NOT NULL COMMENT ‘患者名称’, birthday DATETIME COMMENT ‘出生日期’, PRIMARY KEY (id) … ) 执行以下SQL报错： select * from uaa_user where birthday<= ‘2012-01-01’ 错误信息：

[Code: 1105, SQL State: HY000] Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: ‘1941-3-15 0:0:0.000000000’ is not a valid datetime in specified time zone")

【关键词】按出生日期查询报错

Lucien · 2019 年10 月 10 日 06:42

原因：

日志报错中 “‘1941-3-15 0:0:0.000000000’ is not a valid datetime in specified time zone"，说明 time zone 可能存在问题，需要检查 timezone 是不是存储数据的 timezone 存在异常。

解决办法：

排查 timezone 是否正确，如果默认是东8区，需要确认 timezone 是否为 UTC，文档中有具体的排查方法

https://pingcap.com/docs-cn/v2.1/how-to/configure/time-zone/#时区支持
2. 另外报错中已经明确具体数据，需要验证数据是否可以查，另外需要完成的 TiDB 日志。

Tao · 2019 年10 月 10 日 06:54

查看了下时区， @@global.time_zone @@session.time_zone

SYSTEM SYSTEM

[root@tidb-1 ~]# echo $TZ
[root@tidb-1 ~]# ll /etc/localtime
lrwxrwxrwx. 1 root root 35 Jul 1 08:39 /etc/localtime -> …/usr/share/zoneinfo/Asia/Shanghai

执行这条SQL语句的时候 tidb.log 的日志如下： [2019/10/10 14:52:36.184 +08:00] [WARN] [conn.go:668] [“dispatch error”] [conn=293023] [connInfo=“id:293023, addr:10.10.68.90:64638 status:2, collation:utf8_general_ci, user:health_transfer”] [sql=">u0000u0000u0000ufffdu0013u0000u0000"] [err="[tikv:1105]Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: ‘1941-3-15 0:0:0.000000000’ is not a valid datetime in specified time zone")"]

Tao · 2019 年10 月 10 日 06:55

不加出生日期查询条件的时候，这条数据（出生日期为 1941-03-15）是能查询出来的

Tao · 2019 年10 月 10 日 07:02

CREATE TABLE uaa_user ( id bigint NOT NULL, name VARCHAR(50) NOT NULL COMMENT ‘患者名称’, birthday DATETIME COMMENT ‘出生日期’, PRIMARY KEY (id));

INSERT INTO uaa_user (id, name, birthday) VALUES (128145918393739604, ‘测试’, ‘1941-03-15 00:00:00’);

Lucien · 2019 年10 月 10 日 07:26

测试没有异常

[tidb@node232 bin]$ mysql -u root -h 127.0.0.1 -P 4003
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.25-TiDB-v3.0.3 MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
| uaa_user       |
+----------------+
2 rows in set (0.00 sec)

mysql> show create table uaa_userG
*************************** 1. row ***************************
       Table: uaa_user
Create Table: CREATE TABLE `uaa_user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(50) NOT NULL COMMENT 'aaa',
  `birthday` datetime DEFAULT NULL COMMENT 'bbbb',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> select * from uaa_user;
+--------------------+------+---------------------+
| id                 | name | birthday            |
+--------------------+------+---------------------+
| 128145918393739604 | dddd | 1941-03-15 00:00:00 |
+--------------------+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from uaa_user;
+--------------------+------+---------------------+
| id                 | name | birthday            |
+--------------------+------+---------------------+
| 128145918393739604 | dddd | 1941-03-15 00:00:00 |
+--------------------+------+---------------------+
1 row in set (0.01 sec)

mysql>  select * from uaa_user where birthday<= '2012-01-01';
+--------------------+------+---------------------+
| id                 | name | birthday            |
+--------------------+------+---------------------+
| 128145918393739604 | dddd | 1941-03-15 00:00:00 |
+--------------------+------+---------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
1 row in set (0.01 sec)

mysql> quit
Bye
[tidb@node232 bin]$ date
Thu Oct 10 15:25:33 CST 2019
[tidb@node232 bin]$

Lucien · 2019 年10 月 10 日 07:58

问题可复现，应该是 TiKV 组件的问题，原因待确认

[tidb@node232 ~]$ mysql  -uroot -p -P14000 -h172.16.4.238
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 31360
Server version: 5.7.25-TiDB-v3.0.3 MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>
mysql>
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_action_info  |
| test1          |
| test2          |
+----------------+
3 rows in set (0.00 sec)

mysql> select tidb_verison)(G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 20 near ")("
mysql> select tidb_verison()G
ERROR 1305 (42000): FUNCTION tidb_verison does not exist
mysql> select tidb_version()G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.3
Git Commit Hash: 836982c617fbaa42d74616eb9ba9ed4cae46c46b
Git Branch: HEAD
UTC Build Time: 2019-08-30 02:42:42
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.01 sec)

mysql> CREATE TABLE `uaa_user` (
    ->   `id` bigint(20) NOT NULL,
    ->   `name` varchar(50) NOT NULL COMMENT 'aaa',
    ->   `birthday` datetime DEFAULT NULL COMMENT 'bbbb',
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.51 sec)

mysql> INSERT INTO uaa_user (id, name, birthday) VALUES (128145918393739604, , 1941-03-15 00:00:00);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 71 near ", 1941-03-15 00:00:00)"
mysql>
mysql>
mysql> INSERT INTO uaa_user (id, name, birthday) VALUES (128145918393739604, 'aaa', '1941-03-15 00:00:00');
Query OK, 1 row affected (0.07 sec)

mysql> select * from uaa_user where birthday<= '2012-01-01';
ERROR 1105 (HY000): Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: \'1941-3-15 0:0:0.000000000\' is not a valid datetime in specified time zone")
mysql>
mysql>
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from uaa_userG
*************************** 1. row ***************************
      id: 128145918393739604
    name: aaa
birthday: 1941-03-15 00:00:00
1 row in set (0.01 sec)

mysql>
mysql> set @@session.sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from uaa_userG
*************************** 1. row ***************************
      id: 128145918393739604
    name: aaa
birthday: 1941-03-15 00:00:00
1 row in set (0.01 sec)

mysql> select * from uaa_user where birthday<= '2012-01-01';
ERROR 1105 (HY000): Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: \'1941-3-15 0:0:0.000000000\' is not a valid datetime in specified time zone")
mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> set @@session.sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql>

[2019/10/10 15:51:00.867 +08:00] [WARN] [conn.go:668] ["dispatch error"] [conn=31360] [connInfo="id:31360, addr:172.16.4.232:34058 status:2, collation:latin1_swedish_ci, user:root"] [sql="select * from uaa_user where birthday<= '2012-01-01'"] [err="[tikv:1105]Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: \'1941-3-15 0:0:0.000000000\' is not a valid datetime in specified time zone")"]

mysql> explain select * from uaa_user where birthday<= '2012-01-01';
+---------------------+-------+------+-------------------------------------------------------------------+
| id                  | count | task | operator info                                                     |
+---------------------+-------+------+-------------------------------------------------------------------+
| TableReader_7       | 0.33  | root | data:Selection_6                                                  |
| └─Selection_6   | 0.33  | cop  | le(test.uaa_user.birthday, 2012-01-01 00:00:00.000000)            |
|   └─TableScan_5 | 1.00  | cop  | table:uaa_user, range:[-inf,+inf], keep order:false, stats:pseudo |
+---------------------+-------+------+-------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> explain analyze select * from uaa_user where birthday<= '2012-01-01';
ERROR 1105 (HY000): Other("[src/coprocessor/codec/mysql/time/mod.rs:93]: \'1941-3-15 0:0:0.000000000\' is not a valid datetime in specified time zone")

tiancaiamao · 2019 年10 月 10 日 09:02

@zz-jason-PingCAP @breeswish

看起来 coprocessor 那边的实现还是基于 rust 的时间库计算的

可能需要 port mysql 的实现方式

Lucien · 2019 年10 月 10 日 09:22

原因：

https://pingcap.com/docs-cn/v3.0/reference/mysql-compatibility/#时区

办法：

目前不支持，需要等修复。

Lucien · 2019 年10 月 10 日 09:50

临睡解决方案：
建议直接把Asia/Shanghai 时区改成东八区。这么改的副作用是部分日期与 timestamp 的比较计算或者跨时区的计算会有 1 小时左右的偏差。受影响的日期主要包括 1941、1942 以及 1986 ~ 1991 之间的夏令时。

注意：

仅限对 datetime 使用单一的场景

Kongdom · 2020 年4 月 29 日 06:52

请问，这个问题现在版本4.0解决了么？

wish-PingCAP · 2020 年5 月 18 日 06:50

@Kongdom 您好， 4.0 下此类 SQL 应当能正常查询，如果有问题可以反馈

Kongdom · 2020 年5 月 25 日 00:56

好的，谢谢

yilong · 2020 年5 月 25 日 01:34

system · 2022 年10 月 31 日 19:10

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。