Bug #77754 Wrong max display width displayed
Submitted: 16 Jul 2015 21:08 Modified: 17 Jul 2015 5:00
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.8, 5.5.46, 5.6.27, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: display width

[16 Jul 2015 21:08] Daniël van Eeden
Description:
For big display width values a wrong maximum is mentioned in the error.
The value 0 is changed to the default instead of getting an error.

How to repeat:
mysql [localhost] {msandbox} (test) > create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql [localhost] {msandbox} (test) > create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql [localhost] {msandbox} (test) > create table foo5 (id int (255));
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > create table foo8 (id int (0));
Query OK, 0 rows affected (0.38 sec)

mysql [localhost] {msandbox} (test) > create table foo9 (id int (1));
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[17 Jul 2015 4:55] Umesh Shastry
Hello Daniël,

Thank you for the report.
Observed this behavior with 5.1.76,5.5.46, 5.6.27 and 5.7.8/9.

Thanks,
Umesh
[17 Jul 2015 5:00] Umesh Shastry
## 
-- 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc-linux-glibc2.5-x86_64: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql> show errors;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Error | 1439 | Display width out of range for column 'id' (max = 4294967295) |
+-------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql> create table foo5 (id int (255));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo8 (id int (0));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo9 (id int (1));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql> create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql> create table foo5 (id int (255));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo8 (id int (0));
Query OK, 0 rows affected (0.01 sec)

mysql> create table foo9 (id int (1));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[17 Jul 2015 5:00] Umesh Shastry
-- 5.6.27

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql> create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql>  create table foo5 (id int (255));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo8 (id int (0));
Query OK, 0 rows affected (0.01 sec)

mysql>  create table foo9 (id int (1));
Query OK, 0 rows affected (0.00 sec)

mysql>  show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

-- 5.5.46

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.46: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql> create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql>  create table foo5 (id int (255));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo8 (id int (0));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo9 (id int (1));
Query OK, 0 rows affected (0.00 sec)

mysql>  show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 5.1.76

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.1.76: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.76 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo5 (id int (1000000000000000000));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 4294967295)
mysql> create table foo5 (id int (4294967295));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
mysql> create table foo5 (id int (255));
Query OK, 0 rows affected (0.01 sec)

mysql>  create table foo8 (id int (0));
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo9 (id int (1));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table foo8\G
*************************** 1. row ***************************
       Table: foo8
Create Table: CREATE TABLE `foo8` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table foo9\G
*************************** 1. row ***************************
       Table: foo9
Create Table: CREATE TABLE `foo9` (
  `id` int(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[16 Jul 2018 8:10] Hartmut Holzgraefe
Still the same in MySQL 8.0.11.

MariaDB 10.3 always shows "max=255", and still treats zero as "use the default"

Unfortunately neither max length nor zero behavior are documented on 

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html