[Maria-discuss] MEMORY tables and binlog
Hi All, I'm facing a weird error. We're migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster's master. When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid - containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is - let's say - corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first). new-node01/02 running 10.3.27-MariaDB-0+deb10u1-log on latest Debian 10.9 (replication from old-node is OK), replication is GTID based. HW is a new HP Gen10 DL380. When checking the binlog on the new-node01 with mysqlbinlog, I get the following: # at 2361 #210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans /*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/; START TRANSACTION /*!*/; # at 2403 #210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id=341297541 exec_time=318 error_code=0 SET TIMESTAMP=1617786814/*!*/; DELETE FROM `mydb1`.`collect_output`e<FB>#<98> /*!*/; # at 2527 #210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`.`collect_output` mapped to number 1330 Please notice the illegal chars at the end of the "DELETE FROM" line. (DELETE FROM is normal, since new-node01 is starting up from shutdown and mydb1.collect_output is a MEMORY table -> will delete the contents, so that the replications slaves have the same data). Is this a bug or a known issue? Is there any other way to solve this, than exclude the MEMORY tables from the replication? Thanks, Tom
Am 07.04.21 um 14:46 schrieb Dajka Tamás:
Hi All,
I’m facing a weird error. We’re migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster’s master.
When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid – containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is – let’s say – corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first)
wow - 10 years after stop using memory tables at all because again and again broken replication they are still a problem?
On Wednesday 07 April 2021 at 14:48:55, Reindl Harald wrote:
Am 07.04.21 um 14:46 schrieb Dajka Tamás:
Hi All,
I’m facing a weird error. We’re migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster’s master.
When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid – containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is – let’s say – corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first)
wow - 10 years after stop using memory tables at all because again and again broken replication they are still a problem?
Does that explain illegal characters such as
DELETE FROM `mydb1`.`collect_output`e<FB>#<98>
? Antony. -- Tinned food was developed for the British Navy in 1813. The tin opener was not invented until 1858. Please reply to the list; please *don't* CC me.
Am 07.04.21 um 14:51 schrieb Antony Stone:
On Wednesday 07 April 2021 at 14:48:55, Reindl Harald wrote:
Am 07.04.21 um 14:46 schrieb Dajka Tamás:
Hi All,
I’m facing a weird error. We’re migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster’s master.
When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid – containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is – let’s say – corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first)
wow - 10 years after stop using memory tables at all because again and again broken replication they are still a problem?
Does that explain illegal characters such as
DELETE FROM `mydb1`.`collect_output`e<FB>#<98>
nope - in our case it was random "record don't exist" errors when replicate a delete-statement i finally had enough and converted them all to MyISAM for the sake of sanity
Hi, Dajka! This looks like a bug. Could you report it at jira.mariadb.org? Do you know when it happen, can you reproduce it at will? On Apr 07, Dajka Tamás wrote:
Hi All,
When checking the binlog on the new-node01 with mysqlbinlog, I get the following:
# at 2361 #210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans /*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/; START TRANSACTION /*!*/; # at 2403 #210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id=341297541 exec_time=318 error_code=0 SET TIMESTAMP=1617786814/*!*/; DELETE FROM `mydb1`.`collect_output`e<FB>#<98> /*!*/; # at 2527 #210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`.`collect_output` mapped to number 1330
Is this a bug or a known issue? Is there any other way to solve this, than exclude the MEMORY tables from the replication?
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi Sergei! Sure, I'll report it. Yes, I think it's correctly reproducable. Cheers, Tom
-----Original Message----- From: Sergei Golubchik [mailto:serg@mariadb.org] Sent: Wednesday, April 7, 2021 3:11 PM To: Dajka Tamás <viper@vipernet.hu> Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] MEMORY tables and binlog
Hi, Dajka!
This looks like a bug. Could you report it at jira.mariadb.org? Do you know when it happen, can you reproduce it at will?
On Apr 07, Dajka Tamás wrote:
Hi All,
When checking the binlog on the new-node01 with mysqlbinlog, I get the following:
# at 2361 #210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans /*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/; START TRANSACTION /*!*/; # at 2403 #210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id=341297541 exec_time=318 error_code=0 SET TIMESTAMP=1617786814/*!*/; DELETE FROM `mydb1`.`collect_output`e<FB>#<98> /*!*/; # at 2527 #210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`.`collect_output` mapped to number 1330
Is this a bug or a known issue? Is there any other way to solve this, than exclude the MEMORY tables from the replication?
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Issue created as MDEV-25358.
-----Original Message----- From: Maria-discuss [mailto:maria-discuss- bounces+viper=vipernet.hu@lists.launchpad.net] On Behalf Of Dajka Tamás Sent: Wednesday, April 7, 2021 3:18 PM To: 'Sergei Golubchik' <serg@mariadb.org> Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] MEMORY tables and binlog
Hi Sergei!
Sure, I'll report it. Yes, I think it's correctly reproducable.
Cheers,
Tom
-----Original Message----- From: Sergei Golubchik [mailto:serg@mariadb.org] Sent: Wednesday, April 7, 2021 3:11 PM To: Dajka Tamás <viper@vipernet.hu> Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] MEMORY tables and binlog
Hi, Dajka!
This looks like a bug. Could you report it at jira.mariadb.org? Do you know when it happen, can you reproduce it at will?
On Apr 07, Dajka Tamás wrote:
Hi All,
When checking the binlog on the new-node01 with mysqlbinlog, I get the following:
# at 2361 #210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans /*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/; START TRANSACTION /*!*/; # at 2403 #210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id=341297541 exec_time=318 error_code=0 SET TIMESTAMP=1617786814/*!*/; DELETE FROM `mydb1`.`collect_output`e<FB>#<98> /*!*/; # at 2527 #210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`.`collect_output` mapped to number 1330
Is this a bug or a known issue? Is there any other way to solve this, than exclude the MEMORY tables from the replication?
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Dajka Tamás <viper@vipernet.hu> writes:
Hi All,
I’m facing a weird error. We’re migrating our old 10.1 multi-master setup to a new 10.3 cluster (everything is running on Debian). New-node01 is connected as a slave to the old cluster’s master.
When I tried to set up the replication between the 2 new nodes (with data files copy), the slave thread stopped nearly immediatelly stating, that the command is invalid – containing illegal characters. After some investigation, it turned out, that binlog on the master (new-node01) is – let’s say – corrupted. After a few iterations/checking I saw, that the error always happens with some MEMORY tables (I know I can exclude them from the replica, but want to understand the issue first).
new-node01/02 running 10.3.27-MariaDB-0+deb10u1-log on latest Debian 10.9 (replication from old-node is OK), replication is GTID based. HW is a new HP Gen10 DL380.
When checking the binlog on the new-node01 with mysqlbinlog, I get the following:
# at 2361
#210407 11:13:34 server id 4 end_log_pos 2403 CRC32 0x90080d27 GTID 100-4-37336878143 trans
/*!100001 SET @@session.gtid_seq_no=37336878143*//*!*/;
START TRANSACTION
/*!*/;
# at 2403
#210407 11:13:34 server id 4 end_log_pos 2527 CRC32 0x27183d2f Query thread_id= 341297541 exec_time=318 error_code=0
SET TIMESTAMP=1617786814/*!*/;
DELETE FROM `mydb1`.`collect_output`e<FB>#<98>
Looks very much related to binlog checksum. Cannot say anthing more, maybe you could develop it further knowing details that unreported here? Cheers, Andrei
/*!*/;
# at 2527
#210407 11:13:34 server id 4 end_log_pos 2598 CRC32 0x427f0ed7 Table_map: `mydb1`. `collect_output` mapped to number 1330
Please notice the illegal chars at the end of the „DELETE FROM” line.
participants (5)
-
andrei.elkin@pp.inet.fi
-
Antony Stone
-
Dajka Tamás
-
Reindl Harald
-
Sergei Golubchik