Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.1(EOL)
Description
Short description; I restore a mariabackup base backup (or one with incrementals applied) and in any case the restored backup is missing innodb data because I cannot dump tables and I cannot run show create table because I get an error that says "Table 'X' doesn't exist in engine".
I've repeated the issue just for this bug report.
I start with one base backup that finished without errors and says backup_type = full-backuped in /var/database/base_xtrabackup/xtrabackup_checkpoints.
I empty /var/database/restored_xtrabackup and /var/database/mysql dirs.
I run rsync -a /var/database/base_xtrabackup/* /var/database/restored_xtrabackup/ as root.
I run mariabackup --prepare --target-dir=/var/database/restored_xtrabackup as root. See prepare.out.log in attachments for errors regarding one specific table. More about that below.
I run mariabackup --copy-back --target-dir=/var/database/restored_xtrabackup as root. No errors in output.
I then have a mariadb setup on this backup server only for restoring purposes, and I start it without issue.
At this point I'm able to connect to the mysql console and list database, select databases.
But if I try to run show create table on any table I get this error.
ERROR 1932 (42S02): Table 'X' doesn't exist in engine
If I run mysqldump with lock tables the error is;
mysqldump: Got error: 1932: "Table 'X' doesn't exist in engine" when using LOCK TABLES
Without lock tables it just skips the last part of the error.
Noteworthy is that there is some corrpution in the live database. Mysqldump will only work from the live DB node if I use --ignore-table=dbname.mdl_tag_correlation which is en empty table but if I try to dump that empty table mysqldump fails when it reaches that table.
I cannot try a test dump now because it would halt production but tomorrow I can add more info about how it fails and what is logged server side.
I'll have to plan a service window to delete and re-create that table.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
stemid what's the datadir where your table X returned show create table X ?
why you are using 1st rsync and then mariabackup --copy-back ?
marko yes that file exists and it's brought along from the base_xtrabackup dir, to restored_xtrabackup and the datadir.
As far as I know the table has not been renamed. It's not even used. Based on it not being used I assume it's been dormant for many years.
The only major change, and what might have caused this issue, is when we went from mysql 5.5 to mariadb 10 by setting up a replication slave that replicated all data from the old 5.5 system to the new 10.1 cluster.
Before that mysqldumps worked from the old system and did not halt at this table.
Would you advise just dropping it and re-creating it? Ensuring all ibd files are deleted when it's dropped.
winstone The datadir is specified in the environment part /var/database/mysql. I'm using rsync only to copy the base backup because I don't want to make changes to the original base backup. Does mariabackup have an alternative for this step?
marko
the backup taken from from 5.5 does not contain .ibd files but Stefan said that his data comes from 5.5 and there is .idb file . Where that file might derive from ?
also why v10.2 is returning error for not correct metadata but v10.1 does not ?
mariabackup 10.2 failed with Error This target does not have correct metadata
but mariabackup v10.1 did not returne that Error on the same data
# mariabackup -u root --prepare --target-dir /var/mariadb/backup5_5
|
mariabackup based on MariaDB server 10.2.12-MariaDB Linux (x86_64)
|
mariabackup: cd to /var/mariadb/backup5_5/
|
mariabackup: This target does not have correct metadata
|
stemid Did you perform mysql_upgrade --verbose --verbose other-options ?
also do you see the problem with only one definite table or it refers to all tables ?
is select * from X is working ?
winstone I've never run mysql_upgrade before but I tried that now on a running instance of mysqld using the restored datafiles from the backup.
Attached is the output. It seems to complain about tables not existing in engine for more than just my DB but also the mysql DB.
And no a select won't work either, same error; "1932 ... Table 'X' doesn't exist in engine".
stemid
Hello Stefan ,
please check ls -l of $datadir/DB_Name/Table_Name.*
or as from your error log
ls -l /var/database/novo_hermods_se.mdl_ham_survey_instance.*
I succeed to recreate the issue by changing the group owner of the files of one table X in datadir
then it's received the same error when perform show create table X or when force mysql_upgrade ;
please try to restore mysql ownership of the files
chown -R mysql:mysql ${datadir}/Database_the_table_X_belongs_to
In the mysql_upgrade.out.log
I only see that a few tables in the mysql.* schema are claimed not to exist in the storage engine (presumably InnoDB). I think that InnoDB should write quite a bit of output to the server error log in this case, but the error log is not included.
One more idea is that before MDEV-13807 (Mariabackup 10.1.27) it was not safe to restore multiple incremental backups on a base backup. But prepare.out.log says that the version number is 10.1.29.
winstone, Mariabackup 10.2 is only compatible with MariaDB 10.2. (Mariabackup 10.3 should work with 10.2 and 10.3.) The main reason for this is that MariaDB 10.2 broke redo log format compatibility with older versions, just like MySQL 5.7 that it is based on did. Older versions also did not have a redo log format version tag. Furthermore, Mariabackup 10.2 use a slightly different metadata file format (some fields have been removed). This should explain the error "This target does not have correct metadata".
stemid, was there a file novo_hermods_se/mdl_tag_correlation.ibd in the backup? Was this table renamed recently? Or is this the very table that was corrupted?
wlad and me have some suspicions that Mariabackup (or Xtrabackup) might have trouble tracking some DDL operations, especially renames.