Hello!
We are in progress of upgrading from PS5.7.31 async replication to 3-node PXC5.7.44 with this replication chain:
PS-M – MIXED → PS-S – ROW → PXC-1 (WSREP to PXC-2 and PXC-3)
The PS-S writes binlog for all DBs, while the PXC-1 has Replicate_Do_DB set to one DB only for easier testing.
Writes on PS-M are replicated down to and within the PXC, but on PXC-1 the log is flooded with warnings:
WSREP: Pending to replicate MySQL GTID event (probably a stale event). Discarding it now.
GTIDs are not used and in the relay log on PCX-1 all GTIDs appear as anonymous.
We also turned of the two other nodes PXC-2 and PXC-3 to see if WSREP itself causes issues, but it seems to be processing the binlogs.
How do we identify the source of these GTIDs and avoid these warnings?
Many thanks,
Martin
You should fix this. You can enable GTID on your PS-M PS-S in an online fashion.
Thank you Matthew - we’ll look into enabling GTID.
Since the current primary has myisam tables and we’d like to use this PXC migration to switch to innodb, I’m not sure if GTIDs work out.
Our setup consists of multiple replicas, we planned to use one of them to move the data into PXC for an online data migration.
Can also only this replica (the mentioned PS-S we use for data migration) create GTIDs to avoid the risk of enabling GTIDs on all the other nodes in the production setup?
GTID has nothing to do with your engine. Replication is engine agnostic. You will need to change those MyISAM tables to InnoDB before moving to PXC. The tables can be InnoDB on PXC and replication will handle still work even though they are MyISAM on the source.
Yes, your dedicated replica can have GTID enabled.
I thought GTID relies on transactions and therefore may be problematic with non-transactional storage engines.
We already tested the replication chain (without GTID and with MyISAM on the source and InnoDB on PXC) and this is working as expected. Also we have some other mixed setup in use (like TokuDB on the replicas).
The issue with the WSREP warning occurs only, when we start to replicate production data into PXC.
Yes, your dedicated replica can have GTID enabled.
Hm, tried this out but when setting the GTID mode to ON on the replica only, it refuses the anonymous GTIDs from the primary.
I’d like to understand why PXC sees a pending GTID event, when only anonymous GTIDs are used.
Don’t use Toku. It is a dead engine. There is no longer any support for it.
GTIDs are created by the binlog, not by the table engine:
mysql [localhost:5738] {msandbox} (foo) > CREATE TABLE mytable (id int unsigned primary key auto_increment, name char(4)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:5738] {msandbox} (foo) > INSERT INTO mytable VALUES (0, 'Bard');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5738] {msandbox} (foo) > INSERT INTO mytable VALUES (0, 'Bart');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5738] {msandbox} (foo) > INSERT INTO mytable VALUES (0, 'Barp');
Query OK, 1 row affected (0.00 sec)
drmac@blacklion:~/dbdeployer/sandboxes/msb_5_7_38/data$ ~/dbdeployer/opt/mysql/5.7.38/bin/mysqlbinlog -vv --base64-output=decode-rows blacklion-bin.000004 | grep GTID
#240506 16:16:03 server id 4 end_log_pos 194 CRC32 0xba793fe7 Previous-GTIDs
#240506 16:17:12 server id 4 end_log_pos 259 CRC32 0x026b73c0 GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= '3d10276c-86f0-4096-9bee-6edc6152c0c4:142'/*!*/;
#240506 16:17:42 server id 4 end_log_pos 489 CRC32 0x33da9104 GTID last_committed=1 sequence_number=2 rbr_only=yes
SET @@SESSION.GTID_NEXT= '3d10276c-86f0-4096-9bee-6edc6152c0c4:143'/*!*/;
#240506 16:17:46 server id 4 end_log_pos 794 CRC32 0x8f9b5f6d GTID last_committed=2 sequence_number=3 rbr_only=yes
SET @@SESSION.GTID_NEXT= '3d10276c-86f0-4096-9bee-6edc6152c0c4:144'/*!*/;
#240506 16:17:50 server id 4 end_log_pos 1099 CRC32 0x5b287dfb GTID last_committed=3 sequence_number=4 rbr_only=yes
SET @@SESSION.GTID_NEXT= '3d10276c-86f0-4096-9bee-6edc6152c0c4:145'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
Ok, thanks for the explanations. Then I mixed up the transactionId with transactional storage engine.
Unfortunately in PS 5.7 it seem not possible to enable GTID on the replica only. So either we need to delay the migration until we fully understand the impact of enabling GTID on the primary, consider a migration to PS/PXC 8 - or find the reason for the problem.
That sounds strange. Can you give more information here? What commands did you run? What was in the error log?
Basically following this guide https://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html but only on the one replica (PS-S from my initial chain) we use to push data towards PXC.
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN
monitor the error logs on the replicate PS-S for some time and then
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
after this, running trough the steps
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
but stopped here, not setting gtid_mode to ON. When setting gtid_mode to ON, the replication from primary PS-M would stop because the primary still sends anonymous GTIDs (as we only want the replica to write GTIDs in it’s binlog because of log_slave_update).
gtid_mode ON_PERMISSIVE should mean:
ON_PERMISSIVE : New transactions use GTIDs; replicated transactions may be either GTID or anonymous
But from the binlog on the replica, we see that it still contains anonymous GTIDs.
Do you have any ideas how we could activate replication from the current PS5.7.31 to PXC5.7.44.
Is there a way to activate replication without GTIDs?
@elackner Since GTIDs are “new”, you can absolutely run replication without GTIDs; replication has existed in MySQL “since the beginning” and we certainly didn’t have GTIDs back then. You can use binlog filename:position to setup traditional replication. Many guides, and YT videos exist on this. Our documentation also has many examples.
@matthewb We’ve replication running since decades. Replication itself is not the issue. We are not using GTID because in our replication setup, we always have been fine knowing binlog host, file and position.
The issue is that we’d like to migrate from PS to PXC using replication and avoid shutting down and upgrading the data. In test scenario replication from PS-M to PS-S and then with log_slave_update from PS-S to PXC worked but in production we’re getting lots of warnings
WSREP: Pending to replicate MySQL GTID event (probably a stale event). Discarding it now
And we have no idea where this is coming from as GTIDs are not is use.
We don’t want to ignore these warnings, as also the replication to PXC is lagging behind PS-M to PS-S replication although the benchmarks we did on PXC were much faster than on the PS-M/PS-S setup.
WSREP/Galera/PXC use GTID internally that you cannot disable.
According to this bug report, the warning is caused by having different server_ids among the PXC nodes. Can you verify this in your config?
Also noted in another forum post one of our previous PXC engineers says it is due to the MIXED replication from PSM->PSS.
Can you switch to ROW replication?
I have now checked all server_ids and found one ID which was different to the other nodes.
mysql> SHOW GLOBAL VARIABLES LIKE ‘server_id’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 1 |
±--------------±------+
The server_ids are the same now on every PXC node…
Regarding binlog_format we have MIXED on PS-M and ROW on PS-S which should be replicated.
PS-S:
mysql> SHOW VARIABLES LIKE ‘binlog_format’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| binlog_format | ROW |
±--------------±------+
It is replicated, as whatever format it was written on PS-M. If a txn was written as STATEMENT on PS-M, it will replicate as such to PS-S and, even though PS-S is ROW, the incoming txn will be written as STATEMENT. There is no “conversion”.