Bug #103926 tablespace is missing for table innodb_undo_001
Submitted: 7 Jun 12:58 Modified: 30 Jun 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

[7 Jun 12:58] Ting-Lin Chu
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.
[7 Jun 21:16] MySQL Verification Team
Hi,

What is your settings for innodb_data_home_dir ?

I'm not reproducing this with blank innodb_data_home_dir.

Thanks
[8 Jun 2:53] Ting-Lin Chu
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
[8 Jun 9:26] MySQL Verification Team
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
[8 Jun 10:36] Ting-Lin Chu
- 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.
[23 Jun 8:52] MySQL Verification Team
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 ?
[24 Jun 9:37] Ting-Lin Chu
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.
[24 Jun 9:38] Ting-Lin Chu
undo 1 trunc log

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

[24 Jun 9:38] Ting-Lin Chu
undo 2 trunc log

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

[30 Jun 9:59] MySQL Verification Team
Hi,

This is a duplicate of 32654667 and is fixed in 8.0.26

Thanks
Bogdan