[MDEV-15211] Mariabackups give "table 'X' doesn't exist in engine" when restoring backup Created: 2018-02-05  Updated: 2018-03-15  Resolved: 2018-03-04

Status: Closed
Project: MariaDB Server
Component/s: Backup
Affects Version/s: 10.1
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Stefan Midjich Assignee: Zdravelina Sokolovska (Inactive)
Resolution: Not a Bug Votes: 0
Labels: innodb, mariabackup, need_feedback
Environment:

CentOS 7
VMware VM
2 vCPUs
6G RAM
750G disk /var/databases used for backups and datadir
MariaDB-server 10.1.29-1.el7
MariaDB-backup same version
datadir=/var/database/mysql
/var/database/base_xtrabackup
/var/database/restored_xtrabackup

Attachments: File mysql_upgrade.out.log     File prepare.out.log    
Issue Links:
Relates
relates to MDEV-14976 Service mysql fail to start after res... Closed
relates to MDEV-15234 mariabackup gets corrupted data witho... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2018-02-05 ]

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.

Comment by Zdravelina Sokolovska (Inactive) [ 2018-02-05 ]

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 ?

Comment by Zdravelina Sokolovska (Inactive) [ 2018-02-05 ]

stemidneed feedback

Comment by Stefan Midjich [ 2018-02-05 ]

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?

Comment by Zdravelina Sokolovska (Inactive) [ 2018-02-06 ]

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 ?

Comment by Stefan Midjich [ 2018-02-06 ]

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.

mysql_upgrade.out.log

And no a select won't work either, same error; "1932 ... Table 'X' doesn't exist in engine".

Comment by Zdravelina Sokolovska (Inactive) [ 2018-02-07 ]

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

Comment by Marko Mäkelä [ 2018-03-15 ]

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".

Generated at Thu Feb 08 08:19:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.