MySQL Bugs: #19427: Description field is corrupted

Bug #19427	Description field is corrupted
Submitted:	28 Apr 2006 16:55	Modified:	28 Jul 2006 21:32
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.22-BK, 5.0.18-nt	OS:	Linux (Linux, Windows 2003 Server)
Assigned to:	Anjuta Widenius	CPU Architecture:	Any

Description:
On one table, the Description field is corrupted.  It appears to contain part of a foreign key constraint definition

The problem persists when you recreate the complete database from scratch and when you recreate it on different MySql servers

How to repeat:
Run the script below

Then do a: SHOW TABLE STATUS LIKE 'Person'

Look at the Description field

SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `Test`
    CHARACTER SET 'latin1'
    COLLATE 'latin1_swedish_ci';

#
# Structure for the `boroughtype` table : 
#

CREATE TABLE `boroughtype` (
  `BoroughTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`BoroughTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `employedhourstype` table : 
#

CREATE TABLE `employedhourstype` (
  `EmployedHoursTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`EmployedHoursTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `ethnictype` table : 
#

CREATE TABLE `ethnictype` (
  `EthnicTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`EthnicTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `experienceleveltype` table : 
#

CREATE TABLE `experienceleveltype` (
  `ExperienceLevelTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`ExperienceLevelTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `qualificationleveltype` table : 
#

CREATE TABLE `qualificationleveltype` (
  `QualificationLevelTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`QualificationLevelTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `sourcetype` table : 
#

CREATE TABLE `sourcetype` (
  `SourceTypeCode` varchar(10) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY  (`SourceTypeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Structure for the `person` table : 
#

CREATE TABLE `person` (
  `PersonId` int(11) NOT NULL auto_increment,
  `Forename` varchar(30) NOT NULL,
  `Surname` varchar(30) NOT NULL,
  `Title` varchar(20) default NULL,
  `Salutation` varchar(30) default NULL,
  `Address1` varchar(60) default NULL,
  `Address2` varchar(60) default NULL,
  `Address3` varchar(60) default NULL,
  `Address4` varchar(60) default NULL,
  `Town` varchar(60) default NULL,
  `S_Town` varchar(60) default NULL,
  `County` varchar(60) default NULL,
  `PostCode` varchar(20) default NULL,
  `S_PostCode` varchar(20) default NULL,
  `Country` varchar(60) default NULL,
  `HomePhone` varchar(30) default NULL,
  `WorkPhone` varchar(30) default NULL,
  `Mobile` varchar(30) default NULL,
  `Email` varchar(255) default NULL,
  `BirthDate` datetime default NULL,
  `SexType` varchar(20) NOT NULL,
  `Password` varchar(20) default NULL,
  `Employed` tinyint(1) NOT NULL default '0',
  `ReceiveUnemploymentBenefit` tinyint(1) NOT NULL default '0',
  `UnemploymentBenefitDetails` text,
  `AvailableFromDate` datetime default NULL,
  `ContactAfterDate` datetime default NULL,
  `PersonalStrengths` text,
  `WouldLikeToDo` text,
  `WorkExperience` text,
  `OtherWLUHelp` text,
  `SourceTypeOther` varchar(60) default NULL,
  `DetailsOnDatabase` tinyint(1) NOT NULL default '0',
  `ShareDetails` tinyint(1) NOT NULL default '0',
  `SubscribeToNewsletter` tinyint(1) NOT NULL default '0',
  `CV` blob,
  `DisabilityType` varchar(20) default NULL,
  `OtherEthnicGroup` varchar(40) default NULL,
  `BoroughTypeCode` varchar(10) NOT NULL,
  `EmployedHoursTypeCode` varchar(10) default NULL,
  `QualificationLevelTypeCode` varchar(10) default NULL,
  `ExperienceLevelTypeCode` varchar(10) default NULL,
  `SourceTypeCode` varchar(10) default NULL,
  `EthnicTypeCode` varchar(10) default NULL,
  PRIMARY KEY  (`PersonId`),
  KEY `IDX_Person_1` (`BoroughTypeCode`),
  KEY `IDX_Person_2` (`EmployedHoursTypeCode`),
  KEY `IDX_Person_3` (`QualificationLevelTypeCode`),
  KEY `IDX_Person_4` (`ExperienceLevelTypeCode`),
  KEY `IDX_Person_5` (`SourceTypeCode`),
  KEY `IDX_Person_6` (`EthnicTypeCode`),
  CONSTRAINT `BoroughType_Person` FOREIGN KEY (`BoroughTypeCode`) REFERENCES `boroughtype` (`BoroughTypeCode`) ON UPDATE CASCADE,
  CONSTRAINT `EmployedHoursType_Person` FOREIGN KEY (`EmployedHoursTypeCode`) REFERENCES `employedhourstype` (`EmployedHoursTypeCode`) ON UPDATE CASCADE,
  CONSTRAINT `EthnicType_Person` FOREIGN KEY (`EthnicTypeCode`) REFERENCES `ethnictype` (`EthnicTypeCode`) ON UPDATE CASCADE,
  CONSTRAINT `ExperienceLevelType_Person` FOREIGN KEY (`ExperienceLevelTypeCode`) REFERENCES `experienceleveltype` (`ExperienceLevelTypeCode`) ON UPDATE CASCADE,
  CONSTRAINT `QualificationLevelType_Person` FOREIGN KEY (`QualificationLevelTypeCode`) REFERENCES `qualificationleveltype` (`QualificationLevelTypeCode`) ON UPDATE CASCADE,
  CONSTRAINT `SourceType_Person` FOREIGN KEY (`SourceTypeCode`) REFERENCES `sourcetype` (`SourceTypeCode`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS=1;

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.20a, and send the results.

With 5.0.22 on Linux I've got on your test script:

mysql> SHOW TABLE STATUS LIKE 'person'\G
*************************** 1. row ***************************
           Name: person
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-04-28 16:56:04
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 463872 kB; (`BoroughTypeCode`) REFER `test/borough
type`(`BoroughTyp
1 row in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.22    |
+-----------+
1 row in set (0.00 sec)

So, where is the bug here?

Someone asked what the problem was.

Look at the Comment field in the Show Table status output

It says:

Comment: InnoDB free: 463872 kB; (`BoroughTypeCode`) REFER `test/borough

it should say:

Comment: InnoDB free: 463872 kB;

I agree this is not a serious problem but I fear it is a sign of a wider problem since that same table caused problems in EMS's SqlManager whereas other tables did not

Comment field really contains some garbage. It is a bug. Verified with 5.0.22-BK debug build on Linux.

Many thenks for verifying it

This is not a bug. SHOW TABLE STATUS does list foreign key constraints in the comment field.

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->                     INDEX par_ind (parent_id),
    ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                       ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

| child     | InnoDB |       9 | Fixed      |    0 |              0 |       16384 |            NULL |        16384 |         0 |           NULL | 2006-07-29 00:30:42 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 0 kB; (`parent_id`) REFER `test/parent`(`id`) ON DELETE CASCADE |

SHOW TABLE STATUS does list foreign key information in the
Comment field. However, INFORMATION_SCHEMA.TABLES.TABLE_COMMENT
is defined as VARCHAR(80) so that information can easily be
truncated and appear to be incorrect. At least, it seems to me that's
what's going on here.