【系统版本 & 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")
[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]$
[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")