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

INFORMATION_SCHEMA.INNODB_SYS_TABLES - N_COLS is incorrect

Details

    Description

      MariaDB [information_schema]> create table test.t( i int);
      Query OK, 0 rows affected (0.007 sec)
       
      MariaDB [information_schema]> create table test.t1( i int primary key);
      Query OK, 0 rows affected (0.005 sec)
       
      MariaDB [information_schema]> select * from INNODB_SYS_TABLES;
      +----------+----------------------------+------+--------+-------+------------+---------------+------------+
      | TABLE_ID | NAME                       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
      +----------+----------------------------+------+--------+-------+------------+---------------+------------+
      |       20 | test/t                     |   33 |      4 |     5 | Dynamic    |             0 | Single     |
      |       21 | test/t1                    |   33 |      4 |     6 | Dynamic    |             0 | Single     |
      

      Attachments

        Issue Links

          Activity

            InnoDB tables should always include 2 or 3 system columns, appended to the end of the column list: DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR. I do not remember it for sure, but it may be that these columns are always present in the internal representation. In that case, this would not be a bug.

            Not to be confused with the columns of the table are the fields of indexes. There always is a "clustered index", which actually used to refer to a never-implemented feature that the clustered index would contain records from multiple tables, possibly each one carrying a similar PRIMARY KEY.

            The "clustered index" refers to the index where the data is stored, along with the undo log information. Its key normally is the primary key (which can also be UNIQUE INDEX on NOT NULL columns). If no primary key has been defined, then the clustered index will be called GEN_CLUST_INDEX(DB_ROW_ID).

            For the tables in the Description, the clustered index fields should be as follows:

            table clustered index fields
            t DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR,i
            t1 i,DB_TRX_ID,DB_ROLL_PTR

            You can think that the DB_ROW_ID is only "materialized" in the indexes if there is no primary key or unique index on NOT NULL columns. It is a special kind of a hidden virtual column.

            marko Marko Mäkelä added a comment - InnoDB tables should always include 2 or 3 system columns, appended to the end of the column list: DB_ROW_ID , DB_TRX_ID , DB_ROLL_PTR . I do not remember it for sure, but it may be that these columns are always present in the internal representation. In that case, this would not be a bug. Not to be confused with the columns of the table are the fields of indexes. There always is a "clustered index", which actually used to refer to a never-implemented feature that the clustered index would contain records from multiple tables, possibly each one carrying a similar PRIMARY KEY . The "clustered index" refers to the index where the data is stored, along with the undo log information. Its key normally is the primary key (which can also be UNIQUE INDEX on NOT NULL columns). If no primary key has been defined, then the clustered index will be called GEN_CLUST_INDEX(DB_ROW_ID) . For the tables in the Description, the clustered index fields should be as follows: table clustered index fields t DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR,i t1 i,DB_TRX_ID,DB_ROLL_PTR You can think that the DB_ROW_ID is only "materialized" in the indexes if there is no primary key or unique index on NOT NULL columns. It is a special kind of a hidden virtual column.

            People

              greenman Ian Gilfillan
              danblack Daniel Black
              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.