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 Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.5.7-rc OS:Mac OS X
Assigned to: 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