Backup failed on Percona XtraDB Cluster

[ZetaMap]

The Percona Cluster, by default, deny the use of explicit table locking.
See: https://docs.percona.com/percona-xtradb-cluster/8.4/strict-mode.html

Workaroud is to set the global variable pxc_strict_mode to PERMISSIVE (SET GLOBAL pxc_strict_mode=PERMISSIVE;) or to start the server with the --pxc-strict-mode=PERMISSIVE argument.

But the best solution remains to use the solution provided by percona; locking the tables for backup (LOCK TABLES FOR BACKUP), see: https://docs.percona.com/percona-server/8.4/backup-locks.html

Is it possible or not to have a dump adaptation between mysql variants? Or a check specific to percona servers?

[deitch]

Can you describe the issue in a little more detail? This is not during dump, but trying to restore from a dump file, correct?

But the best solution remains to use the solution provided by percona; locking the tables for backup (LOCK TABLES FOR BACKUP), see: https://docs.percona.com/percona-server/8.4/backup-locks.html

Can you show a dump file that fails to back up (remove any sensitive data, maybe even just do a clean database), and then modify it to show how to get it to work?

[deitch]

Is it possible or not to have a dump adaptation between mysql variants? Or a check specific to percona servers?

I have been thinking about this for a long time. The original version of this was a script, run in a container, based on alpine. The alpine mysql-client package actually is just an alias to the mariadb one. That created issues.

Now it is all a single binary using libraries, which gives some flexibility. I do not yet know if the right solution is to make the single binary more capable, or to have variants like mysql-backup and mariadb-backup and percona-backup.

[ZetaMap]

Can you describe the issue in a little more detail? This is not during dump, but trying to restore from a dump file, correct?

The Percona XtraDB Cluster implements a new type of lock named BACKUP. According to the documentation:
[...]
Unlike FLUSH TABLES WITH READ LOCK, LOCK TABLES FOR BACKUP does not flush tables, i.e. storage engines are not forced to close tables and tables are not expelled from the table cache. As a result, LOCK TABLES FOR BACKUP only waits for conflicting statements to complete (i.e. DDL and updates to non-transactional tables). It never waits for SELECTs, or UPDATEs to InnoDB or MyRocks tables to complete, for example.
[...]

Also they explain that mysqldump has been extended to use this query when using --single-transaction (https://docs.percona.com/percona-server/8.0/backup-locks.html#the-mysqldump-command).

Can you show a dump file that fails to back up (remove any sensitive data, maybe even just do a clean database), and then modify it to show how to get it to work?

There is my script to generate some data, create a backup from a container, and make a restore to another container: mysql-backup.zip, you can run the ./backup-test.sh script.
To reproduce the issue, simply remove --pxc-strict-mode=PERMISSIVE from the last line of Dockerfile.

Now it is all a single binary using libraries, which gives some flexibility. I do not yet know if the right solution is to make the single binary more capable, or to have variants like mysql-backup and mariadb-backup and percona-backup.

In my opinion, it's better to have a single binary that adapt itself to the type of mysql variant and version.
Like for this tool: mydumper.

[deitch]

Let's see if we can figure out how to wire this in. The core mysql backup function takes place here. The actual lock is about 17 of those lines here:

	if data.LockTables && (len(tables) > 0 || len(views) > 0) {
		var b bytes.Buffer
		b.WriteString("LOCK TABLES ")
		for index, table := range append(tables, views...) {
			if index != 0 {
				b.WriteString(",")
			}
			b.WriteString("`" + table.Name() + "` READ /*!32311 LOCAL */")
		}

		if _, err := data.Connection.Exec(b.String()); err != nil {
			return err
		}

		defer func() {
			_, _ = data.Connection.Exec("UNLOCK TABLES")
		}()
	}

All it is doing is locking the tables as shown, LOCK TABLES followed b the list of tables and views, followed by READ. Are you suggesting that we have a variant on that, same execution, but that command is LOCK TABLES FOR BACKUP? Is the deferred function the same UNLOCK TABLES?

If so, how would you suggest determining which variant to use? A command-line flag to say, "this is Percona"? Or is there an easy way to figure it out?

[ZetaMap]

All it is doing is locking the tables as shown, LOCK TABLES followed b the list of tables and views, followed by READ. Are you suggesting that we have a variant on that, same execution, but that command is LOCK TABLES FOR BACKUP? Is the deferred function the same UNLOCK TABLES?

Yes

If so, how would you suggest determining which variant to use? A command-line flag to say, "this is Percona"? Or is there an easy way to figure it out?

After research, there is no official way to check if this is a personal server, but we can check for the existence of the pxc_strict_mode variable. If it exists, it is most likely a percona server.

mydumper used the version_comment to check if the term "percona" is present in it, but this is not a reliable way as it can be overridden when compiling the server.

Just like mydumper, it is possible to add arguments for different ways of locking, such as one argument for "flush tables with read lock", another for "lock tables", etc. This way we can choose or force the way we want.