Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21801

Upstream bug #71725: after upgrading from 5.5, existing tables not present in INNODB_SYS_TABLESPACES

Details

    Description

      This upstream bug is verified, but not yet fixed:

      https://bugs.mysql.com/bug.php?id=71725

      Some users are reporting similar behavior in MariaDB 10.1.24, so this may be the cause. See here for an example:

      MariaDB [(none)]> use appdb;
      		 
      Database changed
      		 
      MariaDB [appdb]> SELECT t.TABLE_SCHEMA, t.TABLE_NAME, ist.SPACE,
      		 
      -> CASE ist.SPACE
      		 
      -> WHEN 0 THEN 'innodb_file_per_table=OFF'
      		 
      -> ELSE 'innodb_file_per_table=ON'
      		 
      -> END AS innodb_file_per_table_value
      		 
      -> FROM information_schema.INNODB_SYS_TABLES ist
      		 
      -> JOIN information_schema.TABLES t
      		 
      -> ON ist.NAME = CONCAT(t.TABLE_SCHEMA, '/', t.TABLE_NAME)
      		 
      -> AND t.ENGINE = 'InnoDB'
      		 
      -> WHERE t.TABLE_SCHEMA='appdb'
      		 
      -> AND t.TABLE_NAME in ('tab2','tab1');
      		 
      +--------------+--------------+--------+-----------------------------+
      		 
      | TABLE_SCHEMA | TABLE_NAME | SPACE | innodb_file_per_table_value |
      		 
      +--------------+--------------+--------+-----------------------------+
      		 
      | appdb | tab1 | 80590 | innodb_file_per_table=ON |
      		 
      | appdb | tab2 | 80586 | innodb_file_per_table=ON |
      		 
      +--------------+--------------+--------+-----------------------------+
      		 
      2 rows in set (0.03 sec)
      		 
       
      		 
      MariaDB [appdb]> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'appdb/tab2'\G
      		 
      *************************** 1. row ***************************
      		 
      TABLE_ID: 80598
      		 
      NAME: appdb/tab2
      		 
      FLAG: 1
      		 
      N_COLS: 17
      		 
      SPACE: 80586
      		 
      FILE_FORMAT: Antelope
      		 
      ROW_FORMAT: Compact
      		 
      ZIP_PAGE_SIZE: 0
      		 
      1 row in set (0.02 sec)
      		 
       
      		 
      MariaDB [appdb]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = 'appdb/tab2'\G
      		 
      Empty set (0.01 sec)
      		 
       
      		 
      MariaDB [appdb]> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'appdb/tab1'\G
      		 
      *************************** 1. row ***************************
      		 
      TABLE_ID: 80602
      		 
      NAME: appdb/tab1
      		 
      FLAG: 1
      		 
      N_COLS: 17
      		 
      SPACE: 80590
      		 
      FILE_FORMAT: Antelope
      		 
      ROW_FORMAT: Compact
      		 
      ZIP_PAGE_SIZE: 0
      		 
      1 row in set (0.01 sec)
      		 
       
      		 
      MariaDB [appdb]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = 'appdb/tab1'\G
      		 
      Empty set (0.01 sec)
      		 
       
      		 
      MariaDB [appdb]> SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE SPACE IN(80586, 80590)\G
      		 
      Empty set (0.01 sec)

      Attachments

        Issue Links

          is blocked by

          Task - A task that needs to be done. MDEV-22343 Remove SYS_TABLESPACES and SYS_DATAFILES

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11655 Transactional data dictionary

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20802 System tablespace is not listed in information_schema.INNODB_SYS_TABLESPACES

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Upstream bug

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I was aware of this problem already in 2015 or maybe even earlier, while still working at Oracle. The tables were introduced without my approval.

            In my opinion, it was a mistake to add the SYS_TABLESPACES and SYS_DATAFILES in the first place in MySQL 5.6. The table SYS_TABLESPACES is completely redundant; files are identified by SYS_TABLES anyway. Likewise, the only additional value that the table SYS_DATAFILES might bring is to register the location of data files when the DATA DIRECTORY attribute is being used. But, we store that information also in .isl files!

            In a support issue a couple of months ago, I noticed that orphan entries can accumulate in both these tables under some circumstances.

            In summary, adding file paths to the system tablespace is only adding problems, not solving any.

            Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions.

            Note: In MySQL 5.7, SYS_TABLESPACES became necessary for the multi-table tablespaces that can be created by CREATE TABLESPACE. MariaDB does not support CREATE TABLESPACE for InnoDB, in my opinion for good reasons.

            marko Marko Mäkelä added a comment - I was aware of this problem already in 2015 or maybe even earlier, while still working at Oracle. The tables were introduced without my approval. In my opinion, it was a mistake to add the SYS_TABLESPACES and SYS_DATAFILES in the first place in MySQL 5.6. The table SYS_TABLESPACES is completely redundant; files are identified by SYS_TABLES anyway. Likewise, the only additional value that the table SYS_DATAFILES might bring is to register the location of data files when the DATA DIRECTORY attribute is being used. But, we store that information also in .isl files! In a support issue a couple of months ago, I noticed that orphan entries can accumulate in both these tables under some circumstances. In summary, adding file paths to the system tablespace is only adding problems, not solving any. Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions. Note: In MySQL 5.7, SYS_TABLESPACES became necessary for the multi-table tablespaces that can be created by CREATE TABLESPACE . MariaDB does not support CREATE TABLESPACE for InnoDB, in my opinion for good reasons.
            marko Marko Mäkelä added a comment -

            What should the fix be? Make INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES reflect the InnoDB fil_system cache contents? But, then we would lose a way to query the contents of the SYS_TABLESPACES table?

            Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES?

            marko Marko Mäkelä added a comment - What should the fix be? Make INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES reflect the InnoDB fil_system cache contents? But, then we would lose a way to query the contents of the SYS_TABLESPACES table? Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES ?
            GeoffMontee Geoff Montee (Inactive) added a comment -

            Hi marko,

            Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions.

            OK, that makes sense. It sounds like we should tell users to only query the information_schema.INNODB_SYS_TABLES table for table and tablespace-related metadata, since the information_schema.INNODB_SYS_TABLESPACES table has known bugs, and since you plan to remove that table anyway.

            Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES?

            In the long term, it might be useful to have ways to view the contents of important data structures like that.

            GeoffMontee Geoff Montee (Inactive) added a comment - Hi marko , Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions. OK, that makes sense. It sounds like we should tell users to only query the information_schema.INNODB_SYS_TABLES table for table and tablespace-related metadata, since the information_schema.INNODB_SYS_TABLESPACES table has known bugs, and since you plan to remove that table anyway. Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES? In the long term, it might be useful to have ways to view the contents of important data structures like that.
            marko Marko Mäkelä added a comment -

            julien.fritsch, I do not really think that ‘upstream’ is a meaningful concept any more. We have diverged rather far from MySQL and definitely do not depend on them for bug fixes.

            GeoffMontee, would it suffice to fix this bug in 10.6 as part of MDEV-22343? By possibly providing something like INFORMATION_SCHEMA.INNODB_TABLESPACES?

            marko Marko Mäkelä added a comment - julien.fritsch , I do not really think that ‘upstream’ is a meaningful concept any more. We have diverged rather far from MySQL and definitely do not depend on them for bug fixes. GeoffMontee , would it suffice to fix this bug in 10.6 as part of MDEV-22343 ? By possibly providing something like INFORMATION_SCHEMA.INNODB_TABLESPACES ?
            GeoffMontee Geoff Montee (Inactive) added a comment -

            Hi marko,

            That sounds reasonable to me. Users can still use information_schema.INNODB_SYS_TABLES in existing GA versions to avoid the bugs in information_schema.INNODB_SYS_TABLESPACES.

            GeoffMontee Geoff Montee (Inactive) added a comment - Hi marko , That sounds reasonable to me. Users can still use information_schema.INNODB_SYS_TABLES in existing GA versions to avoid the bugs in information_schema.INNODB_SYS_TABLESPACES .
            marko Marko Mäkelä added a comment -

            MDEV-22343 in MariaDB Server 10.6.0 removes the system tables SYS_TABLESPACES and SYS_DATAFILES altogether and makes INNODB_SYS_TABLESPACES reflect the fil_system data structure.

            marko Marko Mäkelä added a comment - MDEV-22343 in MariaDB Server 10.6.0 removes the system tables SYS_TABLESPACES and SYS_DATAFILES altogether and makes INNODB_SYS_TABLESPACES reflect the fil_system data structure.

            People

              marko Marko Mäkelä
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.