MySQL Bugs: #105525: [MY-010995] [Server] Error in renaming mysql_index

Bug #105525	[MY-010995] [Server] Error in renaming mysql_index_stats.ibd.
Submitted:	11 Nov 2021 4:13	Modified:	11 Nov 2021 14:57
Reporter:	Pham Khuong	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
- Upgrade mysql from 5.7.36 to 8.0.26
- Set the option:
[mysqld]
innodb_file_per_table = off
- Upgrade MySQL

-> Got the warning: [Warning] [MY-010995] [Server] Error in renaming mysql_index_stats.ibd.

Logs:
2021-11-09T10:12:48.781429-00:00 1 [Note] [MY-012976] [InnoDB] 8.0.27 started; log sequence number 19522006
2021-11-09T10:12:48.924150-00:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-11-09T10:12:48.952931-00:00 1 [Note] [MY-011088] [Server] Data dictionary initializing version '80023'.
2021-11-09T10:12:49.250471-00:00 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade
2021-11-09T10:12:49.250516-00:00 1 [Warning] [MY-010995] [Server] Error in renaming mysql_index_stats.ibd.
2021-11-09T10:12:49.250531-00:00 1 [Warning] [MY-010995] [Server] Error in renaming mysql_index_stats.ibd.
2021-11-09T10:12:49.277928-00:00 0 [Note] [MY-011332] [Server] Plugin mysqlx reported: 'IPv6 is available'
2021-11-09T10:12:49.279337-00:00 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
2021-11-09T10:12:49.279360-00:00 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/tmp/mysqlx.sock''
2021-11-09T10:13:01.674416-00:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2021-11-09T10:13:01.678731-00:00 2 [Note] [MY-011008] [Server] Finished migrating TABLE statistics data.
2021-11-09T10:13:01.795354-00:00 2 [Note] [MY-011008] [Server] Finished migrating TABLE statistics data.
2021-11-09T10:13:02.533496-00:00 2 [Note] [MY-010006] [Server] Using data dictionary with version '80023'.
2021-11-09T10:13:02.767039-00:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' started.
2021-11-09T10:13:02.767281-00:00 5 [Note] [MY-013386] [Server] Running queries to upgrade MySQL server.
2021-11-09T10:13:04.596001-00:00 5 [Note] [MY-013387] [Server] Upgrading system table data.
2021-11-09T10:13:04.869918-00:00 5 [Note] [MY-013385] [Server] Upgrading the sys schema.
2021-11-09T10:13:05.157210-00:00 5 [Note] [MY-013400] [Server] Upgrade of help tables started.
2021-11-09T10:13:05.253787-00:00 5 [Note] [MY-013400] [Server] Upgrade of help tables completed.

How to repeat:
- Install mysql 5.7.36
- Set the option:
[mysqld]
innodb_file_per_table = off
- Upgrade mysql from 5.7.36 to 8.0.26
- Start mysql

Hi Mr. Khuong,

Thank you for your bug report.

However, it is not a bug.

You should first do the entire upgrading procedure, then you should restart 8.0 and only then you should change the settings.

Not a bug.

Sorry because the steps are not detailed.

But with the following steps, i got the error warning:

- Install mysql 5.7.36
- In mysql 5.7 i set the option: innodb_file_per_table = off
- Restart mysql 5.7
--> It means i used mysql 5.7 with option innodb_file_per_table = off before upgrading

Before upgrade to mysql 8.0.26
- Stop mysql
- Upgrade to mysql 8.0.26
- Start mysql 

-> Got the warning: [Warning] [MY-010995] [Server] Error in renaming mysql_index_stats.ibd.

Hi Mr. Khuong,

Sorry, but mysql_index_stats is not a table that we create nor maintain. It is not a part of our installation.

Are you sure you were and are  using our  5.7 and 8.0 binaries ???

Hi MySQL Verification Team

Yes, i used the MySQL Community Server 8.0.26 (Linux - Generic: https://dev.mysql.com/downloads/mysql/) 

I tested with two binaries
- mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
- mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

Thank you.

Hi Mr. Khuong,

The cause of your problem is quite simple.

Disabling innodb_file_per_table causes InnoDB to create tables in the system tablespace. Since you do not have a system tablespace created and there are tables (that are using file-per-table option) then renaming is not possible.

Hence, if you want to prevent innodb_file_per_table option, you have to manage your installation with lots of manual work, that is explained in our Reference Manual.

Not a bug.

Hi MySQL Verification Team

I got it.

Yes, this problem is quite simple. But i think when i used the option innodb_file_per_table = off, all *.ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

So i think if the option innodb_file_per_table = off, the warning "[Warning] [MY-010995] [Server] Error in renaming mysql_index_stats.ibd." should not be shown.

Thank you.

You are truly welcome.