Details
-
Bug
-
Resolution: Done
-
Major
-
0.8.1.Final
-
None
Description
1. Start the connector with the config -
{
"name": "demo-mysql-connector",
"config": {
"connector.class": "io.debezium.connector.mysql.MySqlConnector",
"database.history.kafka.bootstrap.servers": "localhost:9092",
"database.history.kafka.topic": "debz.schema-changes.demo",
"database.hostname": "localhost",
"database.user": "dbarr",
"database.password": "xxx",
"database.port": "3306",
"database.server.id": "999",
"database.server.name": "debz-demo",
"name": "demo-mysql-connector",
"database.whitelist": "demo",
"tasks.max": "1"
}
}
2. Create a table with json column NOT NULL field.
CREATE TABLE `ts_test` ( `str` varchar(100) DEFAULT NULL, `ts` datetime DEFAULT NULL );
3. Disable mysql strict mode if enabled.
4. Insert a row with datetime value as '0001-01-01 00:00:00'
INSERT INTO `demo`.`ts_test` (`str`, `ts`) VALUES ( 'row1', '0001-01-01 00:00:00');
5. Check kafka event
{"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"string","optional":true,"field":"str"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ts"}],"optional":true,"name":"debz_demo.demo.ts_test3.Value","field":"before"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"str"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ts"}],"optional":true,"name":"debz_demo.demo.ts_test3.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"version"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"server_id"},{"type":"int64","optional":false,"field":"ts_sec"},{"type":"string","optional":true,"field":"gtid"},{"type":"string","optional":false,"field":"file"},{"type":"int64","optional":false,"field":"pos"},{"type":"int32","optional":false,"field":"row"},{"type":"boolean","optional":true,"default":false,"field":"snapshot"},{"type":"int64","optional":true,"field":"thread"},{"type":"string","optional":true,"field":"db"},{"type":"string","optional":true,"field":"table"},{"type":"string","optional":true,"field":"query"}],"optional":false,"name":"io.debezium.connector.mysql.Source","field":"source"},{"type":"string","optional":false,"field":"op"},{"type":"int64","optional":true,"field":"ts_ms"}],"optional":false,"name":"debz_demo.demo.ts_test3.Envelope"},"payload":{"before":null,"after":{"str":"row5","ts":978307200000},"source":{"version":"0.8.1.Final","name":"debz-demo","server_id":1,"ts_sec":1550216193,"gtid":null,"file":"mysql-bin.000020","pos":2202,"row":0,"snapshot":false,"thread":5,"db":"demo","table":"ts_test3","query":null},"op":"c","ts_ms":1550216193991}}
As you can see, the value for ts = 978307200000. This ts translates to 2001-01-01 00:00:00.
I checked the mysql documentation for datetime column values and I found that the allowed range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
However, I realized that we are able insert values like '0001-01-01 00:00:00' into the table.
As I can see in the code, when debezium sees such a timestamp, it gets handled as '2001-01-01 00:00:00'.
See the below screenshot in a debug freeze :
