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

Table performance_schema.session_status and other two tables are not shown in information_schema.tables for normal users

Details

    Description

      Table performance_schema.session_status is not shown in information_schema.tables for normal users, while the root user can access information about the table from information_schema.tables.

      Steps to produce (bash command + MariaDB client):

      bash$ docker run -itd --name mariadb_test --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:11.6.1-rc
      		 
       
      		 
      bash$ docker exec -it mariadb_test mariadb
      		 
       
      		 
      MariaDB [(none)]> CREATE USER test;
      		 
      Query OK, 0 rows affected (0.001 sec)
      		 
       
      		 
      MariaDB [(none)]> quit;
      		 
      Bye
      		 
       
      		 
      bash$ docker exec -it mariadb_test mariadb -u test
      		 
       
      		 
      MariaDB [(none)]> SELECT * FROM performance_schema.session_status;
      		 
      Empty set (0.001 sec)
      		 
       
      		 
      MariaDB [(none)]>  SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'session_status' AND TABLE_SCHEMA='performance_schema';
      		 
      Empty set (0.001 sec)

      From the output of MariaDB client, we can see that the user "test" can select from the performance_schema.session_status table, but cannot get the information about this table from information_schema.tables.

      However, the root user can retrieve the information:

      bash$ docker exec -it mariadb_test mariadb -u root
      		 
       
      		 
      MariaDB [(none)]> SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'session_status' AND TABLE_SCHEMA='performance_schema';
      		 
      +---------------+--------------------+----------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+----------------+---------------+------------------+-----------+
      		 
      | 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 | MAX_INDEX_LENGTH | TEMPORARY |
      		 
      +---------------+--------------------+----------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+----------------+---------------+------------------+-----------+
      		 
      | def           | performance_schema | session_status | BASE TABLE | PERFORMANCE_SCHEMA |      10 | Dynamic    |        261 |              0 |           0 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |               |                0 | N         |
      		 
      +---------------+--------------------+----------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+----------------+---------------+------------------+-----------+
      		 
      1 row in set (0.001 sec)

      In MySQL, a normal user can also retrieve the information:

      bash$ docker exec -it some-mysql mysql -utest
      		 
       
      		 
      mysql> SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'session_status' AND TABLE_SCHEMA='performance_schema';
      		 
      +---------------+--------------------+----------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
      		 
      | 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           | performance_schema | session_status | BASE TABLE | PERFORMANCE_SCHEMA |      10 | Dynamic    |        273 |              0 |           0 |               0 |            0 |         0 |           NULL | 2024-11-05 07:32:15 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |               |
      		 
      +---------------+--------------------+----------------+------------+--------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
      		 
      1 row in set (0.00 sec)

      The information returned by information_schema.tables (as well as information_schema.columns) for new users seems to be incomplete. Could this be a bug in information_schema?

      The tables performance_schema.global_status and performance_schema.session_account_connect_attrs exhibit similar issues.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36421 The results from information_schema.tables have changed in version 10.6.21

          • Major - Major loss of function.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              fuboat Jingzhou Fu
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.