pingcap / pingcap/tidb-binlog

when sync to mysql, data may not consitent with timestamp data type if explicit_defaults_for_timestamp var diffreent

Open
#284 0 comments 0 reactions 0 assignees View on GitHub

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)

about explicit_defaults_for_timestamp

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.