xtrabackup using --history option on --read-only database fails
Description
Environment
Smart Checklist
Activity
Valentin SAROT July 7, 2021 at 4:38 PM
Thanks a lot !
Marcelo Altmann July 7, 2021 at 4:34 PM
Hi Valentin,
Thanks for the further explanation. It makes sense to validate the database/table before executing. I will keep this as a Feature Request.
Valentin SAROT July 7, 2021 at 12:28 PM
Actually, I consider the read-only mode interesting for "production" tables, it should not concern technical tables.
Percona schema is kind of a technical table, philosophicaly: it gives information on backup executions (start time + duration).
We run the backup with a specific user which has CONNECTION_ADMIN privilege, so is able to perform inserts in tables of PERCONA_SCHEMA.
Marcelo Altmann July 7, 2021 at 11:44 AM
Hi ,
--history is meant to write data to a summary/history table.
After trying to create the database, it will try to create the table and then try to insert a new entry to PERCONA_SCHEMA.xtrabackup_history . Having the server as read_only will fail on all three operations above.
If you are running the backup on a slave with read_only / super_read_only why use --history in the first place?
Details
Details
Assignee
Satya BodapatiReporter
Valentin SAROTLabels
Affects versions
Priority
LowSmart Checklist
Open Smart Checklist
Smart Checklist
As mentioned by John here : https://forums.percona.com/t/feature-request-slave-backup-failure-if-read-only-and-not-running-super-privilege/7158
It appears that xtrabackup, when using the --history option, will insert data in PERCONA_SCHEMA at the end of the process.
The xtrabackup first tries to create database PERCONA_SCHEMA if it does not exist.
On slave instances, we generaly set --read-only to ensure users are not able to modify schemas and break replication.
As a result, the xtrabackup process will fail with return code 1 because it is not able to create database in read only mode:
Error: failed to execute query 'CREATE DATABASE IF NOT EXISTS PERCONA_SCHEMA': 1290 (HY000) The MySQL server is running with the --read-only option so it cannot execute this statement
In read only mode, this statement should not be executed and the process would crash only when PERCONA_SCHEMA is not previously created.
With --read_only and CONECTION_ADMIN parameter, the backup user can still write to history table.