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

Bug #112262	tablespace is missing for table innodb_undo_002
Submitted:	5 Sep 2023 10:43	Modified:	7 Mar 2024 23:09
Reporter:	hao dai	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.19,8.0.26,8.0.30,8.0.34	OS:	Any
Assigned to:		CPU Architecture:	x86
Tags:	Contribution, regression

Description:
ON MySQL innodb cluster,One master and one standby instance，and set super_read_only = 1 on the standby machine.
When the standby machine triggers undo truncate, it always fails when updating dd。
And this problem occurs on multiple platforms，such as centos7,centos8,ubuntu20.04.docker。

select  FILE_ID, tablespace_name, file_name from information_schema.files where file_name like "%undo%";
result:
[Warning] [MY-012111] [InnoDB] Trying to access missing tablespace 4294967278

and The trunc.log left in the environment affects the normal use of the xtrabackup.

How to repeat:
# mysql8.0.30 master-slave
# In a primary and one standby instance, if we set the standby database to super_read_only = 1, 
# then the standby database will fail to update DD when truncate occurs.
# As a result, we will execute the SQL ：
#       "select  FILE_ID, tablespace_name, file_name from information_schema.files where file_name like "%undo%";"
# will appear "Tablespace is missing for table innodb_undo_002."
# and leave over trunc.log

--source include/master-slave.inc

--echo # 1: set slave parament
connection slave;
set global innodb_max_undo_log_size=11534336; 	
set global innodb_purge_rseg_truncate_frequency=1;	
set global super_read_only=1; 

--echo 
--echo # 2: Create database and insert data
connection master;

create database aa; 
use aa; 
create table t1(id int); 
delimiter ;; 
create procedure idata()
begin
 declare i int;
 set i=1;
 while(i<=500000) do
    insert into t1(id) values(i);
	set i=i+1;
 end while;
end;;
delimiter ;

begin;
call idata();
commit;

Suggested fix:
The MySQL truncate undo operation is performed by the background thread (purge thread). When the background thread executes the truncate undo operation, the data dictionary needs to be updated,

But when updating the data dictionary, a check needs to be executed The check_readonly function, when the slave is set to super_read_only=ON, check_ Readonly will return error.

But from the thread type defined in the database, should the background thread be subject to super when updating data dictionary What are the limitations of super_read_only=ON?  I don't think it should be,

So it is recommended to Add to the check_readonly function to determine whether the thread is a system thread. If so, return false.

so fix patch:
diff --git a/sql/auth/sql_authorization.cc b/sql/auth/sql_authorization.cc
--- a/sql/auth/sql_authorization.cc
+++ b/sql/auth/sql_authorization.cc
@@ -1822,9 +1822,10 @@ bool check_readonly(THD *thd, bool err_if_readonly) {
                        DBUG_TRACE;
                        
                        /* read_only=OFF, do not prohibit operation: */
                        if (!opt_readonly) return false;
                        
                        /*
                          Thread is replication slave or skip_read_only check is enabled for the
                          command, do not prohibit operation.
                        */
-                        if (thd->slave_thread || thd->is_cmd_skip_readonly()) return false;
+                        if (thd->slave_thread || thd->is_cmd_skip_readonly() || thd->is_system_thread()) return false;

Hello hao dai,

Thank you for the report and feedback.
I tried with 8.0.34 release build but not seeing any issues. This seems to be most likely fixed in 8.0.26 per change log. 

Quoting from 8.0.26 change log " A race condition occurred between a purge thread that was truncating an undo tablespace and a server thread that queried the INFORMATION_SCHEMA.FILES table. As a result, the truncated undo tablespace did not appear in the INFORMATION_SCHEMA.FILES table when queried, which in turn caused a MySQL Enterprise Backup failure due to a dependency on the INFORMATION_SCHEMA.FILES table for undo tablespace file locations. (Bug #32104924, Bug #32654667)", see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-26.html

Request to check in the latest GA(8.0.34) and let us know with exact mtr test case which you are using. Thank you.

Sincerely,
Umesh Shastry

1. ---Execute on the slave (version is 8.0.34):
mysql> set global innodb_max_undo_log_size=12582912;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_purge_rseg_truncate_frequency=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)

2. ---Execute on the master(version is 8.0.34):
mysql> create database aa; 
Query OK, 1 row affected (0.01 sec)

mysql> use aa; 
Database changed
mysql> create table t1(id int); 
delimiter ;; 
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;; 
mysql> create procedure idata()
    -> begin
    ->  declare i int;
    ->  set i=1;
    ->  while(i<=500000) do
    ->     insert into t1(id) values(i);
    -> set i=i+1;
    ->  end while;
    -> end;;
delimiter ;
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> call idata();
commit;

Query OK, 1 row affected (10.80 sec)

mysql> commit;
Query OK, 0 rows affected (7.08 sec)

3. ---select on the slave:
mysql> select  FILE_ID, tablespace_name, file_name from information_schema.files where file_name like "%undo%";
+------------+-----------------+------------+
| FILE_ID    | TABLESPACE_NAME | FILE_NAME  |
+------------+-----------------+------------+
| 4294967152 | innodb_undo_001 | ./undo_001 |
| 4294967278 | innodb_undo_002 | ./undo_002 |
+------------+-----------------+------------+
2 rows in set (0.00 sec)

mysql> select SPACE, NAME, ROW_FORMAT, SERVER_VERSION from information_schema.INNODB_TABLESPACES where name like '%undo%';  
+------------+-----------------+------------+----------------+
| SPACE      | NAME            | ROW_FORMAT | SERVER_VERSION |
+------------+-----------------+------------+----------------+
| 4294967279 | innodb_undo_001 | Undo       | 8.0.34         |
| 4294967278 | innodb_undo_002 | Undo       | 8.0.34         |
+------------+-----------------+------------+----------------+
2 rows in set (0.00 sec)

4. ---on the slave node
[root@test01-002 ~]#  cd /var/lib/mysql
[root@test01-002 /var/lib/mysql]#  
[root@test01-002 /var/lib/mysql]#  ll undo*
-rw-r----- 1 mysql mysql 16777216 Sep  7 09:38 undo_001
-rw-r----- 1 mysql mysql 16777216 Sep  7 09:38 undo_002
-rw-r----- 1 mysql mysql    16384 Sep  7 09:38 undo_1_trunc.log

----Summary:

You can see from 3 and 4 that the data dictionary related to undo has not been updated completely, and you can see the legacy undo_1_trunc.log in the data directory of the node where the slave is located

I downloaded and checked the source code of MySQL 8.0.34 in Check_readonly function does not include processing of MySQL system threads.

I can solve this problem by modifying the source code to recompile and installing tests, without affecting Restrictions on non system threads in super_read_only mode.

Please refer to Suggested Fix for the plan！

Hello hao dai,

Thank you for the feedback.
Verified as described.

regards,
Umesh Shastry

patch

Attachment: fix.patch (application/octet-stream, text), 599 bytes.

hello Umesh:

Above, I have remade the patch based on MySQL 8.0.30 and completed compilation and testing verification in a testing environment. This solution can fix the above issues.

Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.4.0 and 9.0.0 releases, and here's the proposed changelog entry from the documentation team:

The MySQL truncate undo operation (purge thread) did not remove the
undo_{space_number}_trunc.log file when attempting to truncate the undo
tablespace.

Thank you for the bug report.