Search docs.molo17.com
    Configuration guide Connectors

    Set up MySQL and MariaDB CDC and fix binlog errors

    This guide takes a MySQL 8 or a MariaDB database from "binary logging is not ready" to a running Gluesync pipeline that copies two tables to PostgreSQL with a snapshot and live changes. It shows the binlog settings Gluesync needs, the grants of a least-privilege user, and how to read and fix the errors that customers report most often: a missing grant, a binlog format that is not ROW, binlogs that were purged while an entity was stopped, errors after a restart or a schema change, and zero dates such as 0000-00-00.

    In this guide

    You will learn how to check and set the binlog options (log_bin, binlog_format, binlog_row_image, server_id, retention), create a user with only REPLICATION SLAVE, REPLICATION CLIENT (BINLOG MONITOR on MariaDB) and SELECT, create a MySQL or MariaDB source and a PostgreSQL target, run a snapshot followed by CDC, check inserts, updates and deletes with SQL, recover when the position in the binlog is lost, handle zero and invalid dates, and use a table that maps each symptom to its cause and fix.

    Quick information

    Goal

    Replicate a MySQL or MariaDB schema to PostgreSQL with binlog-based CDC, using a user that has only the required grants, and know how to recover from binlog errors

    Audience

    Database administrators and Gluesync users who operate MySQL or MariaDB sources

    Level

    Intermediate

    Time required

    About 45 minutes

    Area

    MySQL or MariaDB server → Control Plane → Data pipelines → MySQL CDC or MariaDB CDC source

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a MySQL 8 or a MariaDB 10.11 or later server and an account that can change its configuration (my.cnf or 50-server.cnf) and create users (the guide uses a database shop with the tables customers and orders, and the host names mysql-source and mariadb-source);

    2. The server can be restarted: some binlog options need a restart (see the optional step on the sample tables if you want to build a lab);

    3. The tables to replicate have a primary key (the examples use id);

    4. You have a PostgreSQL target that Core Hub can reach, with a schema that already contains empty tables with matching columns (the examples use the schemas mysql_cdc and mariadb_cdc);

    5. You can sign in to the Gluesync Control Plane with a role that can create pipelines;

    6. You can run SQL against the source and the target, for example with mysql, mariadb and psql.

    The recovery experiments in this guide (purging binlogs, resetting binlog numbering, changing the binlog format) lose changes on purpose. Run them only on a test database.

    The two agents are very close: the options, the grants and the recovery are the same unless the guide says otherwise. The differences are collected in What is different on MariaDB.

    Guide steps

    Optional: create the sample tables

    SQL

    Skip this step if you already have the tables to replicate. It creates the schema shop with 200 customers and 1,000 orders in MySQL (the same SQL runs on MariaDB), and the empty target tables in PostgreSQL.

    1. Connect to the source as an administrator and run the first script;

    2. Connect to the PostgreSQL target and run the second script.

    -- MySQL or MariaDB
CREATE DATABASE IF NOT EXISTS shop;
USE shop;

CREATE TABLE customers (
  id           INT NOT NULL PRIMARY KEY,
  full_name    VARCHAR(100) NOT NULL,
  email        VARCHAR(150),
  country      CHAR(2),
  tier         VARCHAR(10),
  status       VARCHAR(10),
  credit_limit DECIMAL(12,2),
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id          INT NOT NULL PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      DECIMAL(12,2),
  currency    CHAR(3),
  status      VARCHAR(10),
  order_date  DATE
);

INSERT INTO customers (id, full_name, email, country, tier, status, credit_limit)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 200)
SELECT i, CONCAT('Customer ', i), CONCAT('customer', i, '@example.com'),
  ELT(1 + MOD(i,8), 'DE','US','FR','ES','GB','JP','BR','IT'),
  ELT(1 + MOD(i,4), 'silver','gold','platinum','bronze'),
  IF(MOD(i,7)=0, 'suspended', IF(MOD(i,5)=0, 'inactive', 'active')),
  1000 + MOD(i,10)*500
FROM n;

INSERT INTO orders (id, customer_id, amount, currency, status, order_date)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i < 1000)
SELECT i, MOD(i,200)+1, ROUND(10 + MOD(i*37,490) + MOD(i,100)/100, 2),
  ELT(1 + MOD(i,3), 'EUR','USD','GBP'),
  ELT(1 + MOD(i,5), 'paid','shipped','delivered','cancelled','new'),
  DATE_SUB(CURDATE(), INTERVAL MOD(i,90) DAY)
FROM n;
    -- PostgreSQL target
CREATE SCHEMA IF NOT EXISTS mysql_cdc;

CREATE TABLE mysql_cdc.customers (
  id integer PRIMARY KEY, full_name varchar(100) NOT NULL, email varchar(150),
  country char(2), tier varchar(10), status varchar(10),
  credit_limit numeric(12,2), created_at timestamp
);

CREATE TABLE mysql_cdc.orders (
  id integer PRIMARY KEY, customer_id integer NOT NULL, amount numeric(12,2),
  currency char(3), status varchar(10), order_date date
);
    Expected result

    SELECT COUNT(*) FROM shop.customers returns 200 and SELECT COUNT(*) FROM shop.orders returns 1000 in the source. The two PostgreSQL tables exist and are empty.

    With a PostgreSQL target, if you pick a target table that does not exist, the field mapping shows Target table has no columns and you cannot continue. Create the tables first, as above. For MariaDB, create the same tables in a second schema (the examples use mariadb_cdc).
    Check and set the binlog options

    SQL

    Gluesync reads the binary log as if it were a replica. The log must exist, it must record the full row and it must be kept for as long as an entity can be stopped.

    1. Read the current values;

    2. Compare them with the table below;

    3. If something differs, add the options to the server configuration file and restart the server.

    -- MySQL
SELECT @@version, @@log_bin, @@binlog_format, @@binlog_row_image,
       @@server_id, @@gtid_mode, @@binlog_expire_logs_seconds;

-- MariaDB
SELECT @@version, @@log_bin, @@binlog_format, @@binlog_row_image,
       @@server_id, @@binlog_expire_logs_seconds;
    Option Value for Gluesync Why

    log_bin

    ON (MySQL 8: on by default; MariaDB: off by default, set log_bin=<name>)

    Without it the entity does not start CDC and the notification reads Cannot start CDC for entity … because BinLog is not enabled on your database.

    binlog_format

    ROW

    Only row events carry the changed values. With MIXED or STATEMENT changes are not delivered, and Gluesync does not always tell you (see The binlog format is not ROW).

    binlog_row_image

    FULL

    With MINIMAL an update carries only the changed columns, and the event cannot be matched with the entity (see The row image is MINIMAL).

    server_id

    A number that is not used by any other server or replica of the same topology

    Required by the replication protocol. Gluesync does not use the value of your server: each connection of Gluesync registers itself with a random server id.

    binlog_expire_logs_seconds

    At least the longest time an entity can stay stopped, plus a margin

    Binlog files older than this are deleted. If the file with the position of an entity is deleted, the entity cannot resume (see The position in the binlog is lost). MySQL 8 keeps 30 days by default, MariaDB 11.4 keeps 10 days.

    gtid_mode

    Either value

    Gluesync keeps a file name and a position, not a GTID. With gtid_mode=ON the lab pipeline replicated and resumed after a stop.

    For MySQL, put the options in the [mysqld] section:

    [mysqld]
server_id=100
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL
binlog_expire_logs_seconds=1209600

    For MariaDB, put them in the [mariadb] section. expire_logs_days is the older name of the same retention:

    [mariadb]
server_id=100
log_bin=mariadb-bin
binlog_format=ROW
binlog_row_image=FULL
binlog_expire_logs_seconds=1209600
    Expected result

    After the restart the query returns log_bin = 1, binlog_format = ROW, binlog_row_image = FULL and the retention you chose (1209600 seconds are 14 days). SHOW BINARY LOGS lists at least one file.

    Choose the file name of log_bin once and keep it. Gluesync stores the file name and the position of the last event it read. If the base name changes (for example after a rebuild of the server), the stored file no longer exists (see The position in the binlog is lost).
    Create a user with only the grants Gluesync needs

    SQL

    1. Connect as an administrator;

    2. Create the user and grant the replication privileges on all databases and SELECT on the database to replicate;

    3. Show the grants to confirm that nothing else is granted.

    CREATE USER 'gluesync'@'%' IDENTIFIED BY '<strong password>';
GRANT SELECT ON shop.* TO 'gluesync'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'gluesync'@'%';
SHOW GRANTS FOR 'gluesync'@'%';
    Grant What Gluesync does with it

    REPLICATION SLAVE

    Opens the binlog stream (the same connection a replica opens). Without it CDC does not deliver changes.

    REPLICATION CLIENT (shown as BINLOG MONITOR on MariaDB)

    Reads the current binlog position and the list of binlog files (SHOW MASTER STATUS, SHOW BINARY LOGS). On MariaDB a start from a new position (first start, or after Reset pipeline checkpoint) fails without it. On MySQL CDC keeps running without it, but Gluesync can no longer tell that a binlog was purged (see The position in the binlog is lost).

    SELECT on the replicated database

    Reads the rows of the snapshot and the table definitions. Without it the snapshot fails.
    Expected result

    On MySQL SHOW GRANTS lists GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* and GRANT SELECT ON shop.*. On MariaDB the first line reads GRANT REPLICATION SLAVE, BINLOG MONITOR ON *.*, because MariaDB shows REPLICATION CLIENT under its new name. The user has no other privilege.

    The reference pages MySQL CDC and MariaDB CDC use WITH GRANT OPTION and a SELECT grant on mysql.innodb_table_stats. The lab did not need either: the three grants above were enough for the snapshot and for CDC.
    Create the MySQL source and the PostgreSQL target

    UI

    1. Open the Control Plane and select Data pipelines, then the + (Create pipeline) button;

    2. In Setup source select the MySQL CDC agent (or the MariaDB agent);

    3. Enter Host name / Address, Port, Database name, Username and Password;

    4. If the server is MySQL 8 with the default caching_sha2_password plugin and you do not use TLS, switch Allow public key retrieval on;

    5. Leave Zero date time behavior on CONVERT_TO_NULL for now (see Zero dates and invalid dates);

    6. Select Test and continue;

    7. In Setup target(s) create the PostgreSQL target connection (host, port, database, user and password of the target);

    8. In Ready enter the pipeline name (for example MySQL CDC demo) and select Finish.

    MySQL source form of the Agents setup wizard with host
    Figure 1. The MySQL source form. Annotations 1 to 5 are the connection fields, 6 is Allow public key retrieval, 7 is Zero date time behavior and 8 is Test and continue.

    For MariaDB the form is the same. Allow public key retrieval stays off because MariaDB does not use the MySQL 8 authentication plugin:

    MariaDB source form of the Agents setup wizard with host
    Figure 2. The MariaDB source form, with Allow public key retrieval off.
    Expected result

    Test and continue moves you to Setup target(s), and after Finish the pipeline MySQL CDC demo opens.

    With MySQL 8 and no TLS, a connection that fails with Public Key Retrieval is not allowed (the notification Host Credentials Update Failed reads Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up.) needs Allow public key retrieval switched on. To protect the password with TLS instead, see Enable HTTPS and TLS to databases.

    When you save a connection, the agent checks the binlog format. If it is not ROW, the save is refused with The connection was saved, but agent … did not accept the new credentials: BinLog format is not 'ROW'. Fix the server first (see The binlog format is not ROW).
    Select the tables and map them

    UI

    1. In the pipeline, select Entities and then +;

    2. In the object browser open the database shop;

    3. Select the tables customers and orders;

    4. Select the mapping icon next to each table. In Field mapping choose Target Schema (mysql_cdc) and Target Table (customers, then orders) and select Confirm;

    5. Select Confirm and continue.

    Listing database objects for shop with customers and orders selected and mapped to the schema mysql_cdc
    Figure 3. The object browser with the two tables selected. Annotation 1 is the selection, 2 the selected entities and 3 Confirm and continue.
    Expected result

    The two entities shop.customers and shop.orders appear in the pipeline, not started.

    Start the snapshot and CDC

    UI

    1. Select the play icon of the entity (Start sync);

    2. In Sync changes (CDC), under Start from, choose Full copy. Under Data already in the target keep Keep and merge;

    3. Select Start entity. Repeat for the second entity.

    Sync changes (CDC) dialog for shop.customers with Full copy and Keep and merge selected and Start entity highlighted
    Figure 4. The start dialog. Annotation 1 is Full copy, 2 is Keep and merge and 3 is Start entity.
    Pipeline MySQL CDC demo with the entities customers
    Figure 5. The pipeline after the first start: both entities are active and the Last rows column shows the rows copied.
    Expected result

    The notifications Snapshot finished for shop.customers and CDC started for shop.customers appear, and in the target SELECT COUNT(*) FROM mysql_cdc.customers returns 200 and SELECT COUNT(*) FROM mysql_cdc.orders returns 1000. On the source SHOW PROCESSLIST lists a Binlog Dump connection of the user gluesync.

    Check inserts, updates and deletes

    SQL

    1. On the source, insert a row, update it and delete it, one statement at a time;

    2. After each statement, wait a few seconds and run the query on the target.

    -- source
INSERT INTO shop.customers (id, full_name, email, country, tier, status, credit_limit)
VALUES (1001, 'Cdc Insert', 'cdc1@example.com', 'IT', 'gold', 'active', 5000.50);

UPDATE shop.customers SET tier = 'platinum', credit_limit = 9999.99 WHERE id = 1001;

DELETE FROM shop.customers WHERE id = 1001;

-- target, after each statement
SELECT * FROM mysql_cdc.customers WHERE id = 1001;
    Expected result

    After the insert the target returns the row with gold and 5000.50. After the update it returns platinum and 9999.99. After the delete it returns no row. The same checks pass on MariaDB.

    Reproduce and fix a missing grant

    SQL

    Do this on a test server. It shows what the two failures look like.

    1. Stop the pipeline in the Control Plane;

    2. On the source revoke REPLICATION SLAVE from the user and start the pipeline again;

    3. Change a row on the source;

    4. Grant the privilege again.

    REVOKE REPLICATION SLAVE ON *.* FROM 'gluesync'@'%';
UPDATE shop.customers SET credit_limit = 4444.44 WHERE id = 9;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'gluesync'@'%';

    The entities start and show CDC started, but the change does not reach the target. The notification BinLog client communication failure reads Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation. About a minute after the GRANT, Gluesync reconnects without a restart and delivers the change that was made in the meantime.

    On MariaDB, remove BINLOG MONITOR and then use Reset pipeline checkpoint (see Reset the pipeline checkpoint) and start the pipeline: it does not start, and the notification Fetch last read BinLog TransactionId error reads Access denied; you need (at least one of) the BINLOG MONITOR privilege(s) for this operation.

    Notifications hub filtered on Fetch last read BinLog
    Figure 6. The notification for the missing BINLOG MONITOR privilege. Annotation 1 is the notification and 2 the full message.

    A missing SELECT shows up differently. The snapshot of the entity fails and the notification is SQL Transient Connection with Connection is not available, request timed out after 30004ms: the text does not name the privilege. If a snapshot fails like this and the connection test passes, run SHOW GRANTS FOR 'gluesync'@'%' and grant SELECT on the database. Then run a Full copy of the entity.

    Expected result

    After the grant is back, new changes reach the target again and the entities stay Active. A change made while the privilege was missing is delivered once the connection is back, because the position was not lost.

    Recover when the position in the binlog is lost

    SQL / UI

    Gluesync resumes an entity from the file and the position of the last event it read. If that file is deleted while the entity is stopped, Gluesync cannot resume. This is the most common binlog error. It happens when the retention is shorter than the stop (binlog_expire_logs_seconds), when someone runs PURGE BINARY LOGS, RESET MASTER (RESET BINARY LOGS AND GTIDS on MySQL 8.4) or when the log base name changes.

    1. Stop the pipeline in the Control Plane;

    2. On the source, write a row, rotate the logs and purge all but the newest file, then write another row. This reproduces the failure;

    3. Start the pipeline.

    INSERT INTO shop.customers (id, full_name, email, country, tier, status, credit_limit)
VALUES (3001, 'Lost while stopped', 'l1@example.com', 'IT', 'gold', 'active', 100);
FLUSH BINARY LOGS;
FLUSH BINARY LOGS;
SHOW BINARY LOGS;                         -- note the newest file, for example binlog.000005
PURGE BINARY LOGS TO 'binlog.000005';
INSERT INTO shop.customers (id, full_name, email, country, tier, status, credit_limit)
VALUES (3003, 'After purge', 'l3@example.com', 'IT', 'gold', 'active', 100);

    PURGE BINARY LOGS does not remove a file that a connected Binlog Dump thread still reads. In the lab the connection of Gluesync stayed open after the entities were stopped. If the purge leaves the old files, end the connection with KILL <id> (the ids are in SHOW PROCESSLIST) and run the purge again.

    The entities start, and the notification Binlog checkpoint outdated appears:

    Notifications hub filtered on Binlog checkpoint with the expanded message The last read binlog file is no longer available on the master
    Figure 7. The notification, expanded. Annotation 1 is the search box and 2 the full message.

    The message names the file that was lost, the oldest file that is available and the position that Gluesync picked instead: The last read binlog file (binlog.000003) is no longer available on the master (oldest available: binlog.000005). Automatically switched to the newest binlog (binlog.000005 at position 525). A re-sync of all entities is required to ensure data consistency. New changes flow from now on, but every change between the lost position and the new one is missing in the target, here the rows 3001 and 3003. Gluesync does not copy them for you.

    1. Select the play icon of each entity, choose Full copy with Keep and merge and select Start entity.

    Sync changes (CDC) dialog for shop.orders with the three Start from choices Where it stopped
    Figure 8. The start dialog of an entity that has a checkpoint. Annotation 1 is Where it stopped, 2 is Now, 3 is Full copy and 4 is Start entity.
    Expected result

    Each entity shows Snapshot finished and CDC started. The row counts of the source and the target are equal (SELECT COUNT(*), SUM(id) FROM shop.customers and the same query on mysql_cdc.customers), and a new insert on the source reaches the target.

    If the user has no REPLICATION CLIENT (MySQL), Gluesync cannot list the binlog files. It then does not switch automatically: the notification is BinLog client communication failure with Binlog position: binlog.000003:524 for the Entity: null, is no longer available on the master. Need to resync every entity of your Database, it repeats about every minute and the entities do not receive changes. The recovery is the same Full copy. A RESET MASTER while the entities are running gives the same two messages (and sometimes could not find next log).

    Reset the pipeline checkpoint

    If you do not need the lost changes, or if the message keeps coming back after a Full copy, reset the stored position of the whole pipeline:

    1. Stop the pipeline;

    2. Select the Settings (gear) icon in the header, then Danger zone;

    3. Under Pipeline checkpoint reset select the pipeline and select Reset pipeline checkpoint;

    4. Start the pipeline.

    Settings dialog on Danger zone with the pipeline MySQL CDC demo selected under Pipeline checkpoint reset and the Reset pipeline checkpoint button
    Figure 9. Danger zone. Annotation 1 is the pipeline list and 2 is Reset pipeline checkpoint.

    After the reset the entities follow only new changes. In the lab a row written before the start was lost and a row written after it arrived, so the source and the target differ by the rows written in between. Use Full copy when the target must match the source.

    Reset the checkpoint only when you accept that the changes since the last position are not replicated, or when you follow it with a Full copy. See Diagnose a stalled pipeline or entity for the other start choices.
    Reproduce and fix a binlog format that is not ROW

    SQL

    1. On the source set the format to MIXED (or STATEMENT) and select Test and continue on the connection, or save it again;

    2. Restart the pipeline and change a row;

    3. Set the format back to ROW.

    SET GLOBAL binlog_format = 'MIXED';
-- ... save the connection, restart the pipeline, change rows ...
UPDATE shop.customers SET tier = 'silver' WHERE id = 6;
SET GLOBAL binlog_format = 'ROW';

    Saving the connection fails with BinLog format is not 'ROW'. A start of the pipeline does not check the format, so the entities show CDC started, but the insert and the update are not replicated and no error appears. After you set ROW again new changes arrive, and the rows written while the format was wrong are missing in the target. Run a Full copy to repair them.

    SET GLOBAL binlog_format applies to new sessions only. A session that was already open keeps writing in its old format until it reconnects, so write the setting in the configuration file and restart as well.
    Expected result

    SELECT @@global.binlog_format returns ROW, the connection can be saved, and an update made after the fix reaches the target.

    Fix a binlog row image that is MINIMAL

    SQL

    1. Set binlog_row_image to MINIMAL on the source and update a row of a replicated table;

    2. Set it back to FULL.

    SET GLOBAL binlog_row_image = 'MINIMAL';
UPDATE shop.customers SET credit_limit = 1234.56 WHERE id = 6;
SET GLOBAL binlog_row_image = 'FULL';

    The notification Error while processing BinLog event reads class java.math.BigDecimal cannot be cast to class java.lang.Integer: the event holds only the changed column, so the values do not match the columns of the entity. The entity stays Active, the change is not applied, and later updates made with FULL are replicated. Repair the row that was changed with MINIMAL by a Full copy or by updating it again.

    Expected result

    SELECT @@global.binlog_row_image returns FULL, and an update made after the fix reaches the target.

    Restart the server, rename the binlog or change the schema

    SQL

    1. Restart the source server (docker restart or systemctl restart) while the pipeline is running;

    2. Write a row after the server is back.

    After a plain restart nothing needs to be done: the server opens a new binlog file, the connection of Gluesync drops (the notification BinLog client communication failure reads Connection reset) and Gluesync reconnects by itself. In the lab the row written after the restart arrived about a minute later and no change was lost.

    Two other changes break the position and need the recovery above:

    • A new base name of the log (log_bin=mariadb-renamed instead of mariadb-bin, even with a different server_id) gives Binlog checkpoint outdated, because the file with the stored name no longer exists;

    • RESET MASTER restarts the file numbering and gives could not find next log or no longer available on the master.

    A schema change on the source while an entity is running has its own error: after ALTER TABLE shop.customers ADD COLUMN notes VARCHAR(50) the next change of that table fails with Error while processing BinLog event and Missing column at index 8 for table fullTableName: shop.customers, and changes of other tables continue. Stop and start the entity so that Gluesync reads the table definition again, then run a Full copy to repair the rows that were changed in the meantime (in the lab two updates were skipped). Changing the type of a column gives class java.lang.String cannot be cast to class java.math.BigDecimal: restore the type, or update the entity, and run a Full copy. See Unlock schema for source schema changes.

    Expected result

    After a plain restart the pipeline keeps replicating. After a rename, a RESET MASTER or a schema change, a Full copy brings the target back to the same row count and the same SUM(id) as the source.

    Zero dates and invalid dates

    MySQL and MariaDB can store 0000-00-00, 2024-00-00 or 2023-02-30 when the sql_mode allows it (the default sql_mode of MySQL 8 refuses them with NO_ZERO_DATE and NO_ZERO_IN_DATE; the default of MariaDB 11.4 does not contain those flags; in the lab the session sql_mode was set to ALLOW_INVALID_DATES to insert them). PostgreSQL has no such dates. The source form has the field Zero date time behavior with three values:

    Value What the snapshot of a MySQL source does in the lab

    CONVERT_TO_NULL (default)

    All the three bad values become NULL in the target. The target column must accept NULL. With a NOT NULL column the entity fails with Not null violation Error.

    ROUND

    The three values become 0001-01-01 (or 0001-01-01 00:00:00).

    EXCEPTION

    The snapshot fails with SQL Error: Code 0, State S1009, Message Zero date value prohibited and the entity stays in error until you change the value or the setting.

    To change the value, open the connection of the source, edit Zero date time behavior and save it, then run a Full copy of the affected entity.

    Live changes (CDC) read the value from the binlog and do not follow the setting. In the lab a zero DATETIME or DATE became NULL, a zero TIMESTAMP became 1970-01-01 00:00:00, and 2023-02-30 became 2023-03-02. A snapshot and CDC can therefore give different values for the same bad date. The reliable fix is to correct the data on the source (UPDATE … SET shipped_on = NULL WHERE shipped_on = '0000-00-00') and to give the target column a default or accept NULL.

    What is different on MariaDB

    Topic MariaDB 11.4 compared with MySQL 8

    Defaults

    log_bin is off and binlog_format is MIXED. Set log_bin, server_id and binlog_format=ROW before the first start. Without the log the notification is Cannot start CDC for entity … because BinLog is not enabled on your database, and with MIXED the connection cannot be saved (BinLog format is not 'ROW').

    Privilege name

    REPLICATION CLIENT is shown and enforced as BINLOG MONITOR. It is required: without it the pipeline does not start from a new checkpoint.

    Authentication

    Allow public key retrieval is not needed.

    Retention

    binlog_expire_logs_seconds defaults to 864000 (10 days). expire_logs_days is the old name of the same setting. MySQL 8 keeps 30 days.

    GTID

    MariaDB always has GTIDs. Gluesync still keeps a file name and a position, so no GTID setting is needed.

    Dates

    The snapshot converts only the pure zero date 0000-00-00 to NULL. A date such as 2024-00-00 or 2023-02-30 makes the snapshot fail with SQL Error: Code 0, State 22007, Message value '2024-00-00 00:00:00' cannot be decoded as LocalDateTime, and the value of Zero date time behavior did not change this in the lab. Correct such values on the source. CDC behaves as on MySQL.
    Verify the result

    You have successfully set up binlog CDC if:

    • log_bin is ON, binlog_format is ROW, binlog_row_image is FULL and the retention covers the longest stop;

    • SHOW GRANTS for the Gluesync user lists only REPLICATION SLAVE, REPLICATION CLIENT (BINLOG MONITOR on MariaDB) and SELECT on the replicated database;

    • The counts of the source and of the target tables are equal after the snapshot;

    • An insert, an update and a delete on the source reach the target within seconds;

    • SHOW PROCESSLIST on the source shows a Binlog Dump connection of the Gluesync user;

    • You know that Binlog checkpoint outdated is repaired with a Full copy.

    Troubleshooting

    Symptom Possible cause What to do

    Entity does not start CDC: Cannot start CDC for entity … because BinLog is not enabled on your database

    log_bin is off (the MariaDB default).

    Set log_bin and server_id in the configuration file, restart the server, check @@log_bin, then start the pipeline.

    Saving the connection fails: BinLog format is not 'ROW'

    binlog_format is MIXED or STATEMENT.

    Set binlog_format=ROW in the file and with SET GLOBAL, reconnect the sessions that write to the database, save the connection again, then run a Full copy for the changes written meanwhile.

    Entities are Active but nothing reaches the target, with no error

    The format is MIXED or STATEMENT on a running pipeline: only a save of the connection checks it.

    Check @@global.binlog_format. Set ROW, then run a Full copy.

    Connection test fails: Public Key Retrieval is not allowed or Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

    MySQL 8 with caching_sha2_password, no TLS, and Allow public key retrieval is off. Wrong password or host give the same text.

    Switch Allow public key retrieval on, or enable TLS. Check the user and host with mysql -u gluesync -h <host> -p.

    BinLog client communication failure: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation

    The user lacks REPLICATION SLAVE.

    Run GRANT REPLICATION SLAVE ON . TO 'gluesync'@'%'. Gluesync reconnects after about a minute.

    Fetch last read BinLog TransactionId error: Access denied; you need (at least one of) the BINLOG MONITOR privilege(s)

    MariaDB user without BINLOG MONITOR (REPLICATION CLIENT).

    Grant REPLICATION CLIENT (it becomes BINLOG MONITOR) and start the pipeline again.

    Snapshot fails: SQL Transient Connection with Connection is not available, request timed out after 30004ms

    Among other causes, the user has no SELECT on the database.

    Check SHOW GRANTS, grant SELECT ON shop.*, then run a Full copy.

    Error while processing BinLog event: class java.math.BigDecimal cannot be cast to class java.lang.Integer

    binlog_row_image is MINIMAL.

    Set binlog_row_image=FULL, repair the changed rows with a Full copy.

    Error while processing BinLog event: class java.lang.String cannot be cast to class java.math.BigDecimal (or the same with other types)

    The type of a column was changed on the source after the entity was created, or the entity holds another type than the binlog sends.

    Restore the column type or update the entity, stop and start the entity, then run a Full copy. If you saw this after upgrading Gluesync from an old version, update to the latest 2.3 build.

    Error while processing BinLog event: Missing column at index N for table fullTableName: …

    A column was added to the source table (or the table has columns that are not mapped) and the entity still holds the old definition.

    Stop and start the entity, then run a Full copy. See Unlock schema.

    Binlog checkpoint outdated

    The binlog file with the stored position was purged, expired, renamed or reset while the entities were stopped.

    Raise binlog_expire_logs_seconds, then run a Full copy of every entity. Reset the pipeline checkpoint only if you accept the gap.

    BinLog client communication failure: Binlog position … is no longer available on the master. Need to resync every entity of your Database, repeating every minute

    Same cause as above on a user without REPLICATION CLIENT, or after RESET MASTER.

    Grant REPLICATION CLIENT, then run a Full copy of every entity.

    BinLog client communication failure: Connection reset

    The server was restarted or the connection was killed.

    Nothing to do: Gluesync reconnects. Check that rows written after the restart arrive.

    Snapshot fails with Zero date value prohibited

    Zero date time behavior is EXCEPTION.

    Set CONVERT_TO_NULL or ROUND, or correct the source data, and run a Full copy.

    Entity fails with Not null violation Error after CONVERT_TO_NULL

    The target column is NOT NULL and the source holds 0000-00-00.

    Allow NULL on the target column, use ROUND, or correct the source data.

    MariaDB snapshot fails with value '2024-00-00 00:00:00' cannot be decoded as LocalDateTime or Invalid date 'FEBRUARY 30'

    The source holds an invalid date that is not the pure zero date.

    Correct the value on the source (UPDATE … SET col = NULL WHERE …), then run a Full copy.

    Dates differ between the snapshot and CDC (NULL after the snapshot, 1970-01-01 00:00:00 or 2023-03-02 after a change)

    The snapshot and CDC convert invalid dates in different ways.

    Correct the data on the source so that no zero or invalid date remains.

    Related guides

    Contact MOLO17 Support if you need assistance with MySQL or MariaDB change data capture.