MDEV-22343 Remove SYS_TABLESPACES and SYS_DATAFILESbb-10.6-MDEV-23497

The InnoDB internal tables SYS_TABLESPACES and SYS_DATAFILES as well as the
INFORMATION_SCHEMA views INNODB_SYS_TABLESPACES and INNODB_SYS_DATAFILES
were introduced in MySQL 5.6 for no good reason in
mysql/mysql-server/commit/e9255a22ef16d612a8076bc0b34002bc5a784627
when the InnoDB support for the DATA DIRECTORY attribute was introduced.

The file system should be the authoritative source of information on files.
Storing information about file system paths in the file system (symlinks,
or even the .isl files that were unfortunately chosen as the solution) is
sufficient. If information is additionally stored in some hidden tables
inside the InnoDB system tablespace, everything unnecessarily becomes
more complicated, because more copies of data mean more opportunity
for the copies to be out of sync, and because modifying the data in
the system tablespace in the desired way might not be possible at all
without modifying the InnoDB source code. So, the copy in the system
tablespace basically is a redundant, non-authoritative source of
information.

We will stop creating or accessing the system tables SYS_TABLESPACES
and SYS_DATAFILES.

We will also remove the view
INFORMATION_SCHEMA.INNODB_SYS_DATAFILES along with SYS_DATAFILES.

The view
INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES will be repurposed
to directly reflect fil_system.space_list. The column
PAGE_SIZE, which would always contain the value of
the GLOBAL read-only variable innodb_page_size, is
removed. The column ZIP_PAGE_SIZE, which would actually
contain the physical page size of a page, is renamed to
PAGE_SIZE. Finally, a new column FILENAME is added, as a
replacement of SYS_DATAFILES.PATH.

This will also
address MDEV-21801 (files that were created before upgrading
to MySQL 5.6 or MariaDB 10.0 or later were never registered
in SYS_TABLESPACES or SYS_DATAFILES) and
MDEV-21801 (information about the system tablespace is not stored
in SYS_TABLESPACES or SYS_DATAFILES).

1 files changed, 24 insertions, 24 deletions

diff --git a/mysql-test/suite/innodb_zip/r/restart.result b/mysql-test/suite/innodb_zip/r/restart.result
index 59d739db5b5..b0cc44c73b5 100644
--- a/mysql-test/suite/innodb_zip/r/restart.result
+++ b/mysql-test/suite/innodb_zip/r/restart.result
@@ -216,9 +216,9 @@ test/t2_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t2_restart.ibd
 test/t3_restart	DEFAULT	2048	MYSQLD_DATADIR/test/t3_restart.ibd
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
 test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
-test/t6_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
-test/t6_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
-test/t6_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
+test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
+test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
+test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
 test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
 test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
 test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
@@ -401,9 +401,9 @@ test/t2_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t2_restart.ibd
 test/t3_restart	DEFAULT	2048	MYSQLD_DATADIR/test/t3_restart.ibd
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
 test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
-test/t6_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
-test/t6_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
-test/t6_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
+test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
+test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
+test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
 test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
 test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
 test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
@@ -420,12 +420,12 @@ ALTER TABLE t7_restart TRUNCATE PARTITION p1;
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
-test/t6_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
-test/t6_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
+test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
+test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
 test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
 test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
 test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
-test/t6_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
+test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
 test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
 test/t7_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
 INSERT INTO t5_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
@@ -525,12 +525,12 @@ innodb_file_per_table	ON
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
-test/t6_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
-test/t6_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
+test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
+test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
 test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
 test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
 test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
-test/t6_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
+test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
 test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
 test/t7_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
 SELECT count(*) FROM t5_restart;
@@ -626,12 +626,12 @@ RENAME TABLE t7_restart TO t77_restart;
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
-test/t66_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
-test/t66_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
+test/t66_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
+test/t66_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
 test/t77_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd
 test/t77_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd
 test/t55_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd
-test/t66_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
+test/t66_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
 test/t77_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd
 test/t77_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd
 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
@@ -724,12 +724,12 @@ innodb_file_per_table	ON
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
-test/t66_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
-test/t66_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
+test/t66_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
+test/t66_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
 test/t77_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd
 test/t77_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd
 test/t55_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd
-test/t66_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
+test/t66_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
 test/t77_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd
 test/t77_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd
 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
@@ -858,12 +858,12 @@ t77_restart#p#p1#sp#s3.ibd
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t4_restart.ibd
-test/t66_restart#p#p0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd
-test/t66_restart#p#p1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd
+test/t66_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd
+test/t66_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd
 test/t77_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s0.ibd
 test/t77_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s1.ibd
 test/t55_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t55_restart.ibd
-test/t66_restart#p#p2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p2.ibd
+test/t66_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p2.ibd
 test/t77_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s2.ibd
 test/t77_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s3.ibd
 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
@@ -996,12 +996,12 @@ t77_restart.par
 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
 Space_Name	Page_Size	Zip_Size	Path
 test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
-test/t66_restart#p#p0	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t66_restart#p#p0.ibd
-test/t66_restart#p#p1	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t66_restart#p#p1.ibd
+test/t66_restart#p#p0	DEFAULT	2048	MYSQLD_DATADIR/test/t66_restart#p#p0.ibd
+test/t66_restart#p#p1	DEFAULT	2048	MYSQLD_DATADIR/test/t66_restart#p#p1.ibd
 test/t77_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s0.ibd
 test/t77_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s1.ibd
 test/t55_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t55_restart.ibd
-test/t66_restart#p#p2	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t66_restart#p#p2.ibd
+test/t66_restart#p#p2	DEFAULT	2048	MYSQLD_DATADIR/test/t66_restart#p#p2.ibd
 test/t77_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s2.ibd
 test/t77_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s3.ibd
 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);