Bug #70779 COLUMN_FORMAT and STORAGE attributes should be shown in COLUMNS.EXTRA

• Submitted: 31 Oct 2013 0:08
• Modified: 4 Nov 2013 19:56
• Reporter: Hartmut Holzgraefe
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: mysql-5.6.14
• OS: Any
• CPU Architecture: Any

[31 Oct 2013 0:08] Hartmut Holzgraefe

Description:
(filed against Sever as even though COLUMN_FORMAT has no effect on e.g. InnoDB tables the setting is preserved and shown in SHOW CREATE TABLE output)

COLUMN_FORMAT and STORAGE are engine specific non-standard column attributes but are not shown in I_S.COLUMNS.EXTRA or any other place in INFORMATION_SCHEMA that I'm aware of ...

How to repeat:
CREATE TABLE test.t1 (   
  id INT PRIMARY KEY AUTO_INCREMENT,   
  msg VARCHAR(100) COLUMN_FORMAT FIXED
);

SELECT COLUMN_NAME, EXTRA 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME='t1'
   AND TABLE_SCHEMA='test';

+-------------+----------------+
| COLUMN_NAME | EXTRA          |
+-------------+----------------+
| id          | auto_increment |
| msg         |                |
+-------------+----------------+
2 rows in set (0.00 sec)

Suggested fix:
Show all non-standard column options in I_S.COLUMNS.EXTRA, 
not just AUTO_INCREMENT

(so that in the long run all information necessary to re-create a tables CREATE statement is available via I_S as alternative to SHOW CREATE TABLE ...