Details
-
Bug
-
Resolution: Not a Bug
-
Major
-
None
-
None
-
None
-
False
-
False
Description
Hello,
Debezium MySQL is not able to capture the transition of a nullable tinyint(1) field from a non-null value to null.
Steps to reproduce:
1- Use the same components versions as in https://github.com/debezium/debezium-examples/tree/main/tutorial
2- Create a MySQL table with a nullable tinyint(1)
CREATE TABLE `order_actors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `is_active` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`), KEY `IDX_1CA4E0301B5771DD` (`is_active`) ) ENGINE=InnoDB AUTO_INCREMENT=26339625 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3- Insert a row with is_active = 1
insert into order_actors (id, is_active) values (1,1);
4- Debezium will capture the event correctly
{"schema":{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"}],"optional":false,"name":"dbserver1.inventory.order_actors.Key"},"payload":{"id":1}} {"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int16","optional":true,"default":1,"field":"is_active"}],"optional":true,"name":"dbserver1.inventory.order_actors.Value","field":"before"},{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int16","optional":true,"default":1,"field":"is_active"}],"optional":true,"name":"dbserver1.inventory.order_actors.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"version"},{"type":"string","optional":false,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"ts_ms"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false,incremental"},"default":"false","field":"snapshot"},{"type":"string","optional":false,"field":"db"},{"type":"string","optional":true,"field":"sequence"},{"type":"string","optional":true,"field":"table"},{"type":"int64","optional":false,"field":"server_id"},{"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":"int64","optional":true,"field":"thread"},{"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"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"total_order"},{"type":"int64","optional":false,"field":"data_collection_order"}],"optional":true,"field":"transaction"}],"optional":false,"name":"dbserver1.inventory.order_actors.Envelope"},"payload":{"before":null,"after":{"id":1,"is_active":1},"source":{"version":"1.8.0.Final","connector":"mysql","name":"dbserver1","ts_ms":1640163350000,"snapshot":"false","db":"inventory","sequence":null,"table":"order_actors","server_id":223344,"gtid":null,"file":"mysql-bin.000003","pos":813,"row":0,"thread":null,"query":null},"op":"c","ts_ms":1640163350780,"transaction":null}}
5- Update table to set is_active = null
update order_actors set is_active = null where id = 1;
6- Debezium won't detect the new null value and still report is_active to be 1 ("after":{"id":1,"is_active":1})
{"schema":{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"}],"optional":false,"name":"dbserver1.inventory.order_actors.Key"},"payload":{"id":1}} {"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int16","optional":true,"default":1,"field":"is_active"}],"optional":true,"name":"dbserver1.inventory.order_actors.Value","field":"before"},{"type":"struct","fields":[{"type":"int32","optional":false,"field":"id"},{"type":"int16","optional":true,"default":1,"field":"is_active"}],"optional":true,"name":"dbserver1.inventory.order_actors.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"version"},{"type":"string","optional":false,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"ts_ms"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false,incremental"},"default":"false","field":"snapshot"},{"type":"string","optional":false,"field":"db"},{"type":"string","optional":true,"field":"sequence"},{"type":"string","optional":true,"field":"table"},{"type":"int64","optional":false,"field":"server_id"},{"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":"int64","optional":true,"field":"thread"},{"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"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"total_order"},{"type":"int64","optional":false,"field":"data_collection_order"}],"optional":true,"field":"transaction"}],"optional":false,"name":"dbserver1.inventory.order_actors.Envelope"},"payload":{"before":{"id":1,"is_active":1},"after":{"id":1,"is_active":1},"source":{"version":"1.8.0.Final","connector":"mysql","name":"dbserver1","ts_ms":1640163602000,"snapshot":"false","db":"inventory","sequence":null,"table":"order_actors","server_id":223344,"gtid":null,"file":"mysql-bin.000003","pos":1117,"row":0,"thread":null,"query":null},"op":"u","ts_ms":1640163602953,"transaction":null}}
Many thanks for your efforts looking into this
