Abnormal Insertion of Datetime Type in TiDB for Date Format 2024-03-10 02:00:00.xxx

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB的datetime时间类型,2024-03-10 02:00:00.xxx 格式日期插入异常

| username: TiDBer_yZ7iRfl0

[TiDB Usage Environment] Production Environment
[TiDB Version] v4.0.9
[Reproduction Path] None
[Encountered Problem:] 2024-03-10 02:00:00.xxx - 2024-03-10 02:59:59.xxx
TiDB database datetime type data
On March 10th, during the entire period at 2 o’clock, as long as xxx > 500, it cannot be written to the database. Other times are fine, and 2 o’clock on March 11th is also fine, but not on March 10th.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: xfworld | Original post link

Try it in a new version environment, it might have already been fixed.

| username: 小于同学 | Original post link

Encountered a bug, right?

| username: redgame | Original post link

Upgrade according to the expert’s advice.

| username: TiDBer_aaO4sU46 | Original post link

The version is a bit low.

| username: kelvin | Original post link

Upgrade to 6.5X or above, maybe the bug has already been fixed.

| username: zhaokede | Original post link

Is it really a BUG? I can’t connect to the database to verify it right now.

| username: Kongdom | Original post link

Huh? That’s unlikely. Shouldn’t it be datetime(3) type?

| username: TiDBer_yZ7iRfl0 | Original post link

Really, it’s been running in production for two or three years, and this happened in the early morning.

| username: Jellybean | Original post link

What is the definition of the time field in your table?

If you need to store milliseconds, you need to specify the decimal places for the time field definition.

| username: tidb菜鸟一只 | Original post link

Please share the table structure.

| username: TiDBer_yZ7iRfl0 | Original post link

There is no need to store millisecond-level data.

| username: TiDBer_5cwU0ltE | Original post link

The more I look at it, the more it seems like a BUG. If it can be reproduced, perhaps the manufacturer can provide a PATCH or other solution.

| username: TiDBer_yZ7iRfl0 | Original post link

Only need to store timestamps like 2023-11-30 16:15:22, data with millisecond values greater than or equal to 500 before 2-3 points cannot be stored, everything else is normal.

| username: TiDBer_yZ7iRfl0 | Original post link

The table structure is as follows, the create_time field, other fields have been casually processed, sorry.

CREATE TABLE `xxx` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '',
  `b` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '',
  `c` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '',
  `d` varchar(10000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '',
  `e` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '',
  `f` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '',
  `g` bigint(64) NOT NULL COMMENT '',
  `create_time` datetime NOT NULL COMMENT '',
  `h` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`),
  KEY `h4` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=xxxxxx DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
| username: TiDBer_yZ7iRfl0 | Original post link

It is now consistently reproducible. I will prepare a different version in the test environment to test it.

| username: tidb菜鸟一只 | Original post link

The issue does not exist in version 4.0, and it could not be reproduced in version 5.4.

| username: TiDBer_yZ7iRfl0 | Original post link

Back when I used it, it was only version 4.0. There haven’t been any issues over the years, so I didn’t upgrade the version. When the error occurred, I was completely baffled, but by 3 o’clock it resolved itself :rofl:

| username: No_Fear | Original post link

Version 4.x is too old, it’s better to upgrade.

| username: 连连看db | Original post link

You can try datetime(3).