Tag: mysqlbinlog

When trying to read the MariaDB binlog files, the reading may be interrupted with an error message, and the reading will stop:

ERROR: Found out of order GTID. Got 0-3-855835750755 after 0-1-855835750756

Some positions are shown but after 24 lines of 1236849 file, the mariadb-binlog / mysqlbinlog program interrupts with an error.

It turns out the problem is that the mariadb-binlog / mysqlbinlog reads the binlog files with GTID strict mode, because it is enabled by default! But the servers may have turned it off, so the binlog files are in the wrong format for the strict mode reading. The mode to read the binlog files should be the same as the MySQL / MariaDB server mode that created the binlog files.

root@srv binlog # mariadb-binlog  mysql-bin.52349 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230820 10:53:45 server id 1  end_log_pos 256 CRC32 0xffa88ac1        Start: binlog v 4, server v 11.0.2-MariaDB-1:11.0.2+maria~ubu2204-log created 230820 10:53:45
BINLOG '
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwwwwwwwwwwwwww
'/*!*/;
# at 256
#230820 10:53:45 server id 1  end_log_pos 379 CRC32 0xd434c2c6        Gtid list [0-5-76636859354,
# 0-4-847593757253,
# 0-2-857258567526,
# 0-3-855835750755,
# 0-1-855835750756,
# 1-4-8637504]
# at 379
#230820 10:53:45 server id 1  end_log_pos 423 CRC32 0xc23ef7af        Binlog checkpoint mysql-bin.52348
ERROR: Found out of order GTID. Got 0-3-855835750755 after 0-1-855835750756

Show the slave status to determine, which mode is in use:

root@srv binlog # mysql
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 371408455
Server version: 11.0.2-MariaDB-1:11.0.2+maria~ubu2204-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@gtid_strict_mode;
+--------------------+
| @@gtid_strict_mode |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

When the binlog files are generated with gtid_strict_mode = 0 the “–skip-gtid-strict-mode” option should be used with mariadb-binlog / mysqlbinlog to read the binlog files without errors. More on gtid_strict_mode for MariaDB – https://mariadb.com/docs/skysql-dbaas/ref/mdb/system-variables/gtid_strict_mode/.
Related errors GTID slave position – MariaDB/MySQL replication error – Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos. More on MariaDB on this site with tag mariadb tag.