MySQL Bugs: #88385: Incorrect CHARACTER_MAXIMUM_LENGTH for LONGTEXT data type in information

Bug #88385	Incorrect CHARACTER_MAXIMUM_LENGTH for LONGTEXT data type in information_schema
Submitted:	7 Nov 2017 10:23	Modified:	15 Nov 2017 13:05
Reporter:	Paul Campbell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Ubuntu (17.10)
Assigned to:		CPU Architecture:	Any

Description:
CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH for LONGTEXT data_type reported in information_schema.routines and information_schema.parameters (with Latin1 character set) reports as 

               DATA_TYPE: longtext
CHARACTER_MAXIMUM_LENGTH: 2147483647
  CHARACTER_OCTET_LENGTH: 2147483647

Both the routines and parameters tables define CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH as int(21) in contrast to information_schema.columns
which defines these columns as bigint(21) unsigned. 

LONGTEXT should be 4294967295 for latin1 charset so the value returned by these tables is truncated to half the expected size.

This appears to be a possible cause of an error I get when when querying information_schema.routines with strict_mode enabled which returns

mysql> select tap.has_function('tap','has_function','');
ERROR 1264 (22003): Out of range value for column 'CHARACTER_MAXIMUM_LENGTH' at row 1

mysql> select tap.has_function('tap','has_function','');
+------------------------------------------------+
| tap.has_function('tap','has_function','')      |
+------------------------------------------------+
| ok 58 - Function tap.has_function should exist |
+------------------------------------------------+
1 row in set (0.06 sec)

How to repeat:
mysql> show create table information_schema.routines\G
*************************** 1. row ***************************
       Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` int(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
  `COLLATION_NAME` varchar(64) DEFAULT NULL,
  `DTD_IDENTIFIER` longtext,
  `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) DEFAULT NULL,
  `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(64) DEFAULT NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `ROUTINE_COMMENT` longtext NOT NULL,
  `DEFINER` varchar(93) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table information_schema.parameters\G
*************************** 1. row ***************************
       Table: PARAMETERS
Create Table: CREATE TEMPORARY TABLE `PARAMETERS` (
  `SPECIFIC_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `SPECIFIC_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` int(21) NOT NULL DEFAULT '0',
  `PARAMETER_MODE` varchar(5) DEFAULT NULL,
  `PARAMETER_NAME` varchar(64) DEFAULT NULL,
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` int(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
  `COLLATION_NAME` varchar(64) DEFAULT NULL,
  `DTD_IDENTIFIER` longtext NOT NULL,
  `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table information_schema.columns\G
*************************** 1. row ***************************
       Table: COLUMNS
Create 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 '',
  `GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> delimiter //

mysql> create function foo() returns LONGTEXT begin declare bar LONGTEXT; set bar = 'a'; return bar; end //

mysql> delimiter ;

mysql> select * from information_schema.routines where routine_name = 'foo'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: foo
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: tap
            ROUTINE_NAME: foo
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: longtext
CHARACTER_MAXIMUM_LENGTH: 2147483647
  CHARACTER_OCTET_LENGTH: 2147483647
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
          DTD_IDENTIFIER: longtext
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin declare bar LONGTEXT; set bar = 'a'; return bar; end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2017-11-07 08:46:48
            LAST_ALTERED: 2017-11-07 08:46:48
                SQL_MODE: STRICT_ALL_TABLES
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: latin1
    COLLATION_CONNECTION: latin1_swedish_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.02 sec)

mysql> select * from information_schema.parameters where specific_name = 'foo'\G
*************************** 1. row ***************************
        SPECIFIC_CATALOG: def
         SPECIFIC_SCHEMA: tap
           SPECIFIC_NAME: foo
        ORDINAL_POSITION: 0
          PARAMETER_MODE: NULL
          PARAMETER_NAME: NULL
               DATA_TYPE: longtext
CHARACTER_MAXIMUM_LENGTH: 2147483647
  CHARACTER_OCTET_LENGTH: 2147483647
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
          DTD_IDENTIFIER: longtext
            ROUTINE_TYPE: FUNCTION
1 row in set (0.02 sec)

compare with

mysql> create table foo(a LONGTEXT);
Query OK, 0 rows affected (0.44 sec)

mysql> select * from information_schema.columns where table_name = 'foo'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: tap
              TABLE_NAME: foo
             COLUMN_NAME: a
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: longtext
CHARACTER_MAXIMUM_LENGTH: 4294967295
  CHARACTER_OCTET_LENGTH: 4294967295
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
             COLUMN_TYPE: longtext
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
   GENERATION_EXPRESSION: 
1 row in set (0.04 sec)

Suggested fix:
Change definition of CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH in information_schema.routines and information_schema.parameters to match the definition for information_schema.columns.

Hi!

Thank you for your report. However, it is not a bug. Columns do not have to be of the same type in all I_S tables. Also, they do not have to be UNSIGNED.

However, this is not properly documented. Hence, a documentation bug.

Hi!

I have to make a small correction. Maximum length in bytes of the LONGBLOB or LONGTEXT is a number that is equal to the maximum value that can be contained in an unsigned 32-bit integer.

Hence, this is a bug in the INFORMATION_SCHEMA. A small bug, an easy to fix bug, but still a bug.

Verified as a code bug.

Posted by developer:
 
Yes, I agree we see the issue in 5.7. Confirming the behavior by following,

show columns from information_schema.columns where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	bigint(21) unsigned	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21) unsigned	YES		NULL	
...

show columns from information_schema.routines where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	int(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	int(21)	YES		NULL	
...
...

show columns from information_schema.parameters where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	int(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	int(21)	YES		NULL	
...

MySQL 8.0 is already consistent, that all the three I_S tables use
same data type for the column,

show columns from information_schema.columns where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	bigint(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21)	YES		NULL	
...

show columns from information_schema.routines where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	bigint(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21)	YES		NULL	
...
...

show columns from information_schema.parameters where Field like 'CHARACTER_%';
Field	Type	Null	Key	Default	Extra
CHARACTER_MAXIMUM_LENGTH	bigint(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21)	YES		NULL	
...

This also prevents standard operation of the unit test framework STKUnit. Running most recent mysql distribution on docker/ubuntu. Also affects MariaDB on Arch linux and Percona Server on Ubuntu/Centos. This should be escalated.