MySQL Bugs: #80000: Please add option to avoid displaying integer display widths in CREATE TABLE

Bug #80000	Please add option to avoid displaying integer display widths in CREATE TABLE
Submitted:	15 Jan 2016 10:42	Modified:	19 Jan 2016 8:00
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	integer column widths, SHOW CREATE TABLE

Description:
SHOW CREATE TABLE for numeric columns also has a display width setting. This is only really used by the mysql command line client and not really of much interest to anything else, especially to applications or most users.

It also confuses devs who think that this might be some indication of the size of the data, which it is not, and when creating new tables want to specify this value and may do it incorrectly.  Doing so is usually pretty pointless.

It would be convenient to NOT show this column width setting normally as it is not really very useful, so please provide an option to not show it, and maybe make this the default behaviour in 5.8.

How to repeat:
see above.

Suggested fix:
Example table as shown now:

CREATE TABLE `some_table` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I would like to see this shown as:

CREATE TABLE `some_table` (
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Another example:

CREATE TABLE `some_table` (
  `id` mediumint(8) unsigned NOT NULL, 
  `some_date` date NOT NULL,
  `col_1` mediumint(8) unsigned DEFAULT NULL,
  `col_2` mediumint(8) unsigned DEFAULT NULL,
  `col_3` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`some_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I would like to see this shown as:

CREATE TABLE `some_table` (
  `id` mediumint unsigned NOT NULL, 
  `some_date` date NOT NULL,
  `col_1` mediumint unsigned DEFAULT NULL,
  `col_2` mediumint unsigned DEFAULT NULL,
  `col_3` mediumint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`some_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

In my experience:
* Just a few people use that option;
* None of them understands what the option does. They stop using it as soon as someone explains it to them.

So, personally, I'd prefer that the option is accepted just to avoid breaking tools, dumps, etc. But, IMO, it should be ignored, and it should cause a warning.

This has previously been discussed here (in combination with ZEROFILL - the only modern user of the display meta data):
http://www.tocker.ca/2015/07/02/proposal-to-deprecate-mysql-integer-display-width-and-zero...

I am in support of deprecation and removal.  I am not sure a server option is required.

(Congratulations on bug #80000!)

Morgan: thanks for the congrats. More luck than judgement.

Morgan: "I am in support of deprecation and removal.  I am not sure a server
option is required."

If you do this be careful. Remember people often do mysqldump .... | mysql <somedb>

If you dump from < 5.8 to 5.8 then if 5.8 does not recognise the option this will break.
Filtering out the appropriate entries (with a script) is possible but messy, so possibly this requires 2 changes:
(1) do not generate the (width) settings when displaying the output of SHOW CREATE TABLE ...., and
(2) ignore the input if you see someone use this format.  Maybe this could generate a warning and update a counter?

The counter/warning may not be necessary but both other steps would be and you'd probably have to wait 2 major versions before treating something like CREATE TABLE ... id int(10) ... as an error.

I agree with Simon. This happened at least once before, when MySQL 5.6 removed support for the "SET OPTION" syntax. The syntax had been deprecated for some time, but mysqldump still output the syntax in "SET OPTION SQL_QUOTE_SHOW_CREATE=1". So a dump file created on an earlier version could not be imported easily to 5.6.

There was no good workaround when 5.6 stopped supporting the syntax. When upgrading was not allowed at some client sites, I resorted to binary-editing the mysqldump executable to replace the string "SET OPTION" with "SET       ".

In the case of integer width, it would be better if MySQL 5.8 and future versions deprecate ZEROFILL and the syntax of the width argument for INT types, but continue to support parsing that syntax for the sake of backward compatibility. Just make SHOW CREATE TABLE (and therefore mysqldump) omit the ZEROFILL syntax and width argument by default in 5.8. 

Bonus points if SHOW CREATE TABLE outputs the width argument if and only if ZEROFILL is  set for a column. This would maintain best backward-compatibility for the handful of people who are actually using ZEROFILL on purpose.

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

Hopefully this will have no effect on the data returned by the query "show columns from TABLENAME". Also, at least until mysql_list_fields() has been removed I hope this has no effect on the data returned by that API call.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0