Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
2.2.14
-
None
-
Maxscale 2.2.14 connecting to MariaDB 10.1 for CDC
Description
I have a MariaDB 10.1 database and I've installed and configured Maxscale 2.2.14 to try BinlogRouter, Avrorouter, and CDC Protocol.
The problem is with JSON output from CDC Protocol. It seems that null values are not coming out as nulls but rather zeroes and empty strings on insert, and when value is updated to null, after_update event has old value reported instead of null or 0.
Below are sample table and DML statements, relevant CDC Protocol output, maxscale.cnf and binlog/master.ini.
Any help would be appreciated.
-------------------------------------------------------------
Sample table and DMLs:
-------------------------------------------------------------
CREATE TABLE `test1` (
|
`test1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`some_id` int(10) unsigned DEFAULT NULL,
|
`desc` varchar(50) DEFAULT NULL,
|
`some_date` timestamp NULL DEFAULT NULL,
|
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`test1_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
INSERT INTO test1(some_id,`desc`,some_date)
|
VALUES
|
(1,NULL,NULL),
|
(NULL,'value1',NOW()),
|
(NULL,NULL,NULL);
|
|
|
UPDATE test1
|
SET some_id = NULL, `desc` = 'value2', some_date = NOW()
|
WHERE test1_id = 1;
|
UPDATE test1
|
SET some_id = 35, `desc` = NULL, some_date = NULL
|
WHERE test1_id = 2;
|
-------------------------------------------------------------
CDC Output:
-------------------------------------------------------------
{"namespace": "MaxScaleChangeDataSchema.avro", "type": "record", "name": "ChangeRecord", "fields": [{"name": "domain", "type": "int"}, {"name": "server_id", "type": "int"}, {"name": "sequence", "type": "int"}, {"name": "event_number", "type": "int"}, {"name": "timestamp", "type": "int"}, {"name": "event_type", "type": {"type": "enum", "name": "EVENT_TYPES", "symbols": ["insert", "update_before", "update_after", "delete"]}}, {"name": "test1_id", "type": "int", "real_type": "int", "length": 10}, {"name": "some_id", "type": "int", "real_type": "int", "length": 10}, {"name": "desc", "type": "string", "real_type": "varchar", "length": 50}, {"name": "some_date", "type": "string", "real_type": "timestamp", "length": -1}, {"name": "updated", "type": "string", "real_type": "timestamp", "length": -1}]}
|
{"domain": 0, "server_id": 11009, "sequence": 4552, "event_number": 1, "timestamp": 1539306025, "event_type": "insert", "test1_id": 1, "some_id": 1, "desc": "", "some_date": "", "updated": "2018-10-11 19:00:25"}
|
{"domain": 0, "server_id": 11009, "sequence": 4552, "event_number": 2, "timestamp": 1539306025, "event_type": "insert", "test1_id": 2, "some_id": 1, "desc": "value1", "some_date": "2018-10-11 19:00:25", "updated": "2018-10-11 19:00:25"}
|
{"domain": 0, "server_id": 11009, "sequence": 4552, "event_number": 3, "timestamp": 1539306025, "event_type": "insert", "test1_id": 3, "some_id": 1, "desc": "value1", "some_date": "2018-10-11 19:00:25", "updated": "2018-10-11 19:00:25"}
|
{"domain": 0, "server_id": 11009, "sequence": 5282, "event_number": 1, "timestamp": 1539306107, "event_type": "update_before", "test1_id": 1, "some_id": 1, "desc": "", "some_date": "", "updated": "2018-10-11 19:00:25"}
|
{"domain": 0, "server_id": 11009, "sequence": 5282, "event_number": 2, "timestamp": 1539306107, "event_type": "update_after", "test1_id": 1, "some_id": 1, "desc": "value2", "some_date": "2018-10-11 19:01:47", "updated": "2018-10-11 19:01:47"}
|
{"domain": 0, "server_id": 11009, "sequence": 5552, "event_number": 1, "timestamp": 1539306135, "event_type": "update_before", "test1_id": 2, "some_id": 0, "desc": "value1", "some_date": "2018-10-11 19:00:25", "updated": "2018-10-11 19:00:25"}
|
{"domain": 0, "server_id": 11009, "sequence": 5552, "event_number": 2, "timestamp": 1539306135, "event_type": "update_after", "test1_id": 2, "some_id": 35, "desc": "value1", "some_date": "2018-10-11 19:00:25", "updated": "2018-10-11 19:02:15"}
|
-------------------------------------------------------------
masxscale.cnf:
-------------------------------------------------------------
[vagrant@localhost ~]$ cat /etc/maxscale.cnf
|
# MaxScale documentation:
|
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/
|
|
|
# Global parameters
|
#
|
# Complete list of configuration options:
|
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-configuration-usage-scenarios/
|
|
|
[maxscale]
|
threads=auto
|
|
|
# Server definitions
|
#
|
# Set the address of the server to the network
|
# address of a MariaDB server.
|
#
|
|
|
[server1]
|
type=server
|
address=bpdb.co1.nar.beatportcorp.net
|
port=3306
|
protocol=mariadbbackend
|
|
|
# Monitor for the servers
|
#
|
# This will keep MaxScale aware of the state of the servers.
|
# MariaDB Monitor documentation:
|
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-monitor/
|
|
|
[MaxAdmin-Service]
|
type=service
|
router=cli
|
|
|
[MaxAdmin-Listener]
|
type=listener
|
service=MaxAdmin-Service
|
protocol=maxscaled
|
socket=default
|
|
|
[replication-service]
|
type=service
|
router=binlogrouter
|
router_options=server_id=11009,
|
master_id=11009,
|
user=testuser,
|
password=testpass,
|
binlogdir=/var/lib/maxscale/binlog/,
|
mariadb10-compatibility=1
|
user=testuser
|
passwd=testpass
|
|
|
|
|
[avro-service]
|
type=service
|
router=avrorouter
|
source=replication-service
|
router_options=avrodir=/var/lib/maxscale/avro/,
|
filestem=master-bin,
|
start_index=1
|
user=testuser
|
passwd=testpass
|
|
|
[replication-listener]
|
type=listener
|
service=replication-service
|
protocol=MySQLClient
|
port=5308
|
|
|
[avro-listener]
|
type=listener
|
service=avro-service
|
protocol=CDC
|
port=4001
|
-------------------------------------------------------------
[root@localhost binlog]# cat master.ini
|
[binlog_configuration]
|
master_host=bpdb.co1.nar.beatportcorp.net
|
master_port=3306
|
master_user=testuser
|
master_password=testpass
|
filestem=master-bin
|
master_heartbeat_period=300
|
master_connect_retry=60
|