MySQL 8 ignoring integer lengths

user_580141 0
Viewed 4629

I have a MySQL 8.0.19 running in a Docker container and using the InnoDB engine. I have noticed that table integer field lengths are getting ignored.

The issue occurs with integer datatypes regardless if running a CREATE or ALTER query

CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `text_field` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `decimal_field` decimal(6,2) DEFAULT NULL,
  `int_field` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The lengths are showing as 0 in my MySQL client (Navicat), but the same occurs if checking in the console with SHOW FULL COLUMNS FROMtest;

mysql> SHOW FULL COLUMNS FROM `test`;
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field         | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id            | int          | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| text_field    | varchar(20)  | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| decimal_field | decimal(6,2) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| int_field     | int          | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

The Type column should be showing int(11) for the two integer fields, but it isn't.

Is this related to something in my MySQL settings, and if so, which variable would have to be changed?

mysql-8.0 innodb mysql sql
2 Answers

This is a change documented in the MySQL 8.0.19 release notes:

Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:

  • The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN columns; this exception enables them to continue to make that assumption.

  • The type includes the ZEROFILL attribute.

This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width. (Bug #30556657, Bug #97680)

The "length" of an integer column doesn't mean anything. A column of int(11) is the same as int(2) or int(40). They are all a fixed-size, 32-bit integer data type. They support the same minimum and maximum value.

The "length" of integer columns has been a confusing feature of MySQL for years. It's only a hint that affects the display width, not the storage or the range of values. Practically, it only matters when you use the ZEROFILL option.

mysql> create table t ( i1 int(6) zerofill, i2 int(12) zerofill );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t set i1 = 123, i2 = 123;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+--------+--------------+
| i1     | i2           |
+--------+--------------+
| 000123 | 000000000123 |
+--------+--------------+
1 row in set (0.00 sec)

So it's a good thing that the misleading integer "length" is now deprecated and removed. It has caused confusion for many years.

Bill_Karwin_208600

I can confirm that having upgraded AWS RDS to MySQL 8.0.19 that you can now sync using Navicat correctly.

However, PLEASE BE AWARE!!

When updating the id column, if auto_increment is set, Navicat removes auto_increment to change the length and then re-applies it at the end. This causes the auto_increment column to reassign the ids is sequencial order!

ALTER TABLE `database`.`table` MODIFY COLUMN `id` mediumint(0) NOT NULL FIRST;
...
...
ALTER TABLE `database`.`table` MODIFY COLUMN `id` mediumint(0) NOT NULL AUTO_INCREMENT;

If you are using table relationships and do not have the foreign keys setup properly, this will break your database!

Also, if you have id numbers of zero or below in your auto_increment column, this will cause the following error:

Result: 1062 - ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'table.PRIMARY'

To avoid the above, you will need to manually change each tables id length to 0 and then save the changes before attempting to use the Navicat sync feature. When saving the changes using Navicat this will automatically change any other int column lengths to 0.

Please ensure you throughly test your changes on a copy of the database before trying to apply to any production databases.

user_16929320
Related