Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5473

innodb_system tablespace information missing from I_S innodb_tablespaces view

Details

    Description

      1. I am trying to explore system/default innodb tablespace encryption (ibdata1).
      2. Start percona server instance with ibdata1 encrypted.
      3. Create table that resides in ibdata1.
      4. As we can see from the running queries below it is not possible to trace if given table (in our case t1) is encrypted or not. (There is no information in tablespace that suggest innodb_system tablespace is encrypted).
      5. Also, now that encrypted = Y/N is tablespace property, querying "i_s.tables" for create_option is of no use.
      mysql> create database test;
      Query OK, 1 row affected (0.01 sec)
      
      mysql> use test;
      Database changed
      mysql> create table t (i int, primary key pk(i)) engine=innodb;
      Query OK, 0 rows affected (0.05 sec)
      
      mysql> set global innodb_file_per_table=0;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> create table t1 (i int, primary key pk(i)) engine=innodb;
      Query OK, 0 rows affected (0.03 sec)
      
      mysql> select * from information_schema.innodb_tablespaces;
      +------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
      | SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
      +------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
      | 4294967294 | mysql | 18432 | Any | 16384 | 0 | General | 4096 | 25165824 | 25165824 | 8.0.15 | 1 | N | normal |
      | 4294967293 | innodb_temporary | 4096 | Compact or Redundant | 16384 | 0 | System | 0 | 0 | 0 | 8.0.15 | 1 | N | normal |
      | 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 0 | 0 | 0 | 8.0.15 | 1 | N | active |
      | 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 0 | 0 | 0 | 8.0.15 | 1 | N | active |
      | 1 | sys/sys_config | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.15 | 1 | N | normal |
      | 2 | test/t | 16417 | Dynamic | 16384 | 0 | Single | 4096 | 114688 | 81920 | 8.0.15 | 1 | N | normal |
      +------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
      6 rows in set (0.00 sec)
      
      mysql> select * from information_schema.tables where TABLE_NAME LIKE 't%' AND TABLE_SCHEMA = "test";
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
      | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
      | def | test | t | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-03-12 12:11:13 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
      | def | test | t1 | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 6291456 | NULL | 2019-03-12 12:11:23 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
      2 rows in set (0.00 sec)
      
      mysql>
      

      Attachments

        Issue Links

          Activity

            People

              satya.bodapati Satya Bodapati
              krunal.bauskar Krunal Bauskar (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 day, 13 minutes
                  1d 13m

                  Smart Checklist