when sync to mysql, data may not consitent with timestamp data type if explicit_defaults_for_timestamp var diffreent
Open
Nobody has claimed this yet.
- Dominant language
- Go
- Stars
- 291
- Forks
- 131
- Avg merge
- 5m
- Merged PRs (30d)
- 2
Description
in tidb will like this explicit_defaults_for_timestamp is on as default
mysql> create table tm(id int auto_increment, t_timestamp TIMESTAMP, primary key(id));
Query OK, 0 rows affected (0.24 sec)
mysql> show create table tm;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tm | CREATE TABLE `tm` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> insert into tm(t_timestamp) values(null);
Query OK, 1 row affected (0.13 sec)
mysql> select * from tm;
+----+-------------+
| id | t_timestamp |
+----+-------------+
| 1 | NULL |
+----+-------------+
1 row in set (0.01 sec)
in mysql if explicit_defaults_for_timestamp is off will like this
for mysql Default Value is ON(>= 8.0.2) or OFF(<= 8.0.1)
mysql> create table tm(id int auto_increment, t_timestamp TIMESTAMP, primary key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> show create table tm;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tm | CREATE TABLE `tm` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> insert into tm(t_timestamp) values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tm;
+----+---------------------+
| id | t_timestamp |
+----+---------------------+
| 1 | 2018-05-31 12:55:51 |
+----+---------------------+
1 row in set (0.01 sec)
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the two explicit_defaults_for_timestamp table definitions and insert results against TiDB and MySQL. Then trace the synchronization path for MySQL TIMESTAMP defaults and define done as consistent handling of the differing NULL, default, and ON UPDATE behavior; no source file or test is named in the issue.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- data-engineering, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 28/100