Bug #37900 Duplicate entries in information_schema.COLUMNS

• Submitted: 6 Jul 2008 12:34
• Modified: 7 Jul 2008 16:46
• Reporter: Chris Keen
• Status: Not a Bug
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.0.37
• OS: Linux
• CPU Architecture: Any

[6 Jul 2008 12:34] Chris Keen

Description:
After using ALTER TABLE to add comments to several columns in a table, I find that duplicate entries of some of those columns have appeared in the information_schema.COLUMNS table.

For example,

Existing Table:

my_table (column1 integer,
          column2 integer, ... )

ALTER TABLE my_table change column1 column1 integer COMMENT 'A comment';

SELECT DISTINCT COLUMN_NAME,COLUMN_COMMENT 
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'my_table';

produces the output:

--------------------------------------------------
| COLUMN_NAME          | COLUMN_COMMENT          |
--------------------------------------------------
| column1              |                         |
| column1              | A comment               |
| column2              |                         |
 ...
--------------------------------------------------

This does not appear to affect the querying of the actual table:

SELECT column1,column2 from my_table ...

How to repeat:
Apply ALTER TABLE to an existing table

[7 Jul 2008 11:58] Sveta Smirnova

Thank you for the report.

Please provide output of SELECT DISTINCT COLUMN_NAME,COLUMN_COMMENT, table_name, table_schema  FROM information_schema.COLUMNS WHERE TABLE_NAME = 'my_table';

[7 Jul 2008 16:46] MySQL Verification Team

Thank you for the feedback.