Bug #70462 I_S tables data types are not sized properly

• Submitted: 29 Sep 2013 8:01
• Modified: 13 Oct 2016 16:06
• Reporter: Justin Swanhart
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.6, 5.5, 5.1
• OS: Any
• CPU Architecture: Any
• Tags: memory consumption, performance

[29 Sep 2013 8:01] Justin Swanhart

Description:
This is the definition of the table:
CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

How to repeat:
--

Suggested fix:
Should probably be:
CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` CHAR(1) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` smallint unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` TINYTEXT,
  `IS_NULLABLE` CHAR(1) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(10) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` MEDIUMINT unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` MEDIUMINT unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` TINYINT unsigned DEFAULT NULL,
  `NUMERIC_SCALE` TINYINT unsigned DEFAULT NULL,
  `DATETIME_PRECISION` TINYINT unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` TEXT NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8

[29 Sep 2013 8:02] Justin Swanhart

Since access to these tables must be materialized as MyISAM temporary tables with fixed widths, it makes sense to keep them as small as possible for best performance, especially when there are many tables with many columns.

[30 Sep 2013 2:31] Davi Arnaut

The problem is the use of the static (fixed-length) row format. I believe that was fixed in MySQL 5.6.

[30 Sep 2013 4:24] Justin Swanhart

Even then, ORDINAL_POSITION need not be BIGINT.  It is not possible to exceed SMALLINT columns on a single table.

[30 Sep 2013 4:47] Davi Arnaut

It is not possible now. Anyway, I would surprised if the table structure is changed just for that.

[4 Oct 2013 8:50] MySQL Verification Team

Hello Justin,

Thank you for the report.
Verified as described.

Thanks,
Umesh

[26 Aug 2014 17:43] Justin Swanhart

Updating the synopsis, because some columns are too small, for example the CONSTRAINT_NAME is defined as 64 characters and truncated when longer constraint names are used.

[13 Oct 2016 16:06] Paul DuBois

Posted by developer:
 
Noted in 8.0.0 changelog.

Some INFORMATION_SCHEMA tables had suboptimal column types and sizes.
Such tables that are now views on data dictionary tables in the mysql
system database have more appropriate column definitions.