[MDEV-21232] mysqldump import fails with Streaming Replication Created: 2019-12-05  Updated: 2023-05-03  Resolved: 2021-12-23

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.4.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 2
Labels: None


 Description   

Importing a dump into a cluster with streaming replication enabled fails with:

ERROR 1235 (42000) at line 31: Streaming replication not supported with binlog_format=STATEMENT

Enabling the General Log it has been found that this statement causes the problem:

"LOCK TABLES <table_name> WRITE"

As workaround using " --skip-add-locks " helps.

I could not find anything related to this in the documentation.

 Comments   
Comment by Ranjan Ghosh [ 2020-11-27 ]

This only helps when you remember to do this while backing up your db. If you have an existing dump, I had success doing it like this:

{ echo "SET SESSION wsrep_trx_fragment_size=0;"; | cat "mydump.sql"; } | mysql

Hope this is helpful as a workaround.

Comment by Ranjan Ghosh [ 2020-11-27 ]

The command I posted in my previous comment is necessary, if you didn't use "--skip-add-locks" during a dump and want to import such a file later.
In addition, I found that I need "-skip-lock-tables" to prevent this bug from happening in the first place. "-skip-add-locks" only helped me to make this file importable without that mentioned workaround later. Hope this information is helpful for others.

Comment by Jan Lindström (Inactive) [ 2021-12-23 ]

Binlog format STATEMENT is not supported by Galera.

Generated at Thu Feb 08 09:05:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.