MySQL Bugs: #103926: tablespace is missing for table innodb_undo

Bug #103926	tablespace is missing for table innodb_undo_001
Submitted:	7 Jun 2021 12:58	Modified:	30 Jun 2021 9:59
Reporter:	Ting-Lin Chu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Backup	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Red Hat
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
ON MySQL innodb cluster,
We using mysql enterprise backup on read node but show backup failed due to no undo tablespace.

ERROR: Unexpected number of rows from MySQL query 'SELECT FILE_ID, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG'
AND ENGINE = 'InnoDB AND FILE_ID IS NOT NULL': got 0, expected >= 1.

MySQL read node has issue with Warning 1812

Tablespace is missing for table innodb_undo_001.
Tablespace is missing for table innodb_undo_002.

It likes Bug 28658118
Warning 1812 - Tablespace Is Missing For Table Innodb_system. (Doc ID 2459409.1)
But we don't have set innodb_data_home_dir.

I have confirmed the undo files still exist.
And have undo_1_trunc.log ,undo_2_trunc.log in the directory.

When reboot the read node, the undo file records will back to information_schema.files. Every thing is normal.

In my opinion, it related undo truncate operation because writer didn't have undo information missing issue and don't have undo_1_trunc.log ,undo_2_trunc.log.

Because undo information missing, MySQL enterprise backup also failed. It needs reboot reader to fix this issue manually.

How to repeat:
don't know how to repeat, but happen many times on mysql innnodb cluster reader node.

Hi,

What is your settings for innodb_data_home_dir ?

I'm not reproducing this with blank innodb_data_home_dir.

Thanks

mysql> show variables like 'innodb_data_home_dir';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_data_home_dir |       |
+----------------------+-------+
1 row in set (0.00 sec)

We don't have set innodb_data_home_dir

Hi,
I will need more information as with just this I can not reproduce the problem.

- what setup you have (is this group replication / innodb cluster setup or, master-slave setup or .. since you mention "read node")
- how exactly are you doing the backup? What is the exact command line you use with MySQL enterprise backup
- do you experience the same issue with mysqldump or getting dump from MySQL shell ?

Thanks

- what setup you have (is this group replication / innodb cluster setup
or, master-slave setup or .. since you mention "read node")

Innodb cluster with 3 nodes.

mysql enterprise backup on read node 1
mysqldump backup on read node 2

Both read nodes have missing undo tablespace information. but MySQL are working fine. No error logs.

- how exactly are you doing the backup? What is the exact command line
you use with MySQL enterprise backup

ON Read node 1:
$ mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/backup/tp-db-02-data.mbi_`date +%d%m_%H%M` --backup- dir=/backup --compress --no-history-logging --user=root -p backup-to-image

$ mysqlbackup --defaults-file=/etc/my.cnf --incremental=optimistic --start-lsn=3494881944146 --backup-dir=/backup//incr1 -- backup-image=/backup/tp-db-02-data-incrl.mbi_`date +%d%m_%H%M` backup-to-image

On read node 2:
/usr/bin/mysqldump -uroot -p  --skip-lock-tables --order-by-primary --single- transaction --quick --master-data=2 --routines --triggers

- do you experience the same issue with mysqldump or getting dump from
MySQL shell ?
I experienced the same issue with the read node that run mysqldump, but I don't have using MySQL shell.

mysqldump command also work as expected.

Hi,

I did not manage to reproduce this. Is this a system that was originally installed ad 8.0.22 or was it upgraded from 5.x or some earlier 8.0 ?

This system that is originally installed mysql-commercial-server-8.0.22-1.1.el7.x86_64 and mysql-commercial-backup-8.0.22-1.1.el7.x86_64.

I will attaching undo_1_trunc.log and undo_2_trunc.log . But they seem like are binary files.

undo 1 trunc log

Attachment: undo_1_trunc.log (application/octet-stream, text), 16.00 KiB.

undo 2 trunc log

Attachment: undo_2_trunc.log (application/octet-stream, text), 16.00 KiB.

Hi,

This is a duplicate of 32654667 and is fixed in 8.0.26

Thanks
Bogdan