Bug #60713 REFERENCED_TABLE_NAME truncated in information schema result

• Submitted: 31 Mar 2011 20:56
• Modified: 31 Mar 2011 21:02
• Reporter: Charles Bailey
• Status: Duplicate
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.5.7-rc
• OS: Mac OS X
• CPU Architecture: Any

[31 Mar 2011 20:56] Charles Bailey

Description:
Under some circumstances, the REFERENCED_TABLE_NAME element of foreign key information returned from the information schema is truncated.  This does not occur with identical table definitions on a 5.5.6-rc server.

How to repeat:
CREATE DATABASE `trunc_table_name_test`;

USE `trunc_table_name_test`;

CREATE TABLE `procedures` (
  `id` int(10) unsigned NOT NULL,
  `patient_id` varchar(20) NOT NULL,
  `meditech_id` varchar(45) NOT NULL,
  `date` date NOT NULL,
  `confidence` enum('low','medium','high') DEFAULT NULL,
  `validated` tinyint(1) DEFAULT NULL,
  `cns_status` enum('1','2a','2b','2c','3a','3b','3c') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL,
  `procedure_id` int(10) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `specimen_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_order_procedure` (`procedure_id`),
  CONSTRAINT `fk_order_procedure` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = 'trunc_table_name_test' AND TABLE_NAME = 'orders';

Result (5.5.7-rc):
+-----------------------+
| REFERENCED_TABLE_NAME |
+-----------------------+
| procedu               |
+-----------------------+

Result (5.5.6-rc):
+-----------------------+
| REFERENCED_TABLE_NAME |
+-----------------------+
| procedures            |
+-----------------------+

[31 Mar 2011 21:00] Shane Bester

Can you try a recent version of 5.5 ?  See below

+-----------------------+
| REFERENCED_TABLE_NAME |
+-----------------------+
| procedures            |
+-----------------------+
1 row in set (0.08 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.10    |
+-----------+
1 row in set (0.09 sec)

[31 Mar 2011 21:02] Shane Bester

duplicate of bug #57960