Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Warning 1681: integer display width is deprecated

ErrorWarning: #1681 Integer display width is deprecated and will be removed in a future release.

Short answer

Warning 1681 is not an error and the table is created. MySQL deprecated the display width in types such as INT(11), and ZEROFILL with it. The width never limited the values a column can hold. Remove the number from the type, and use LPAD() where you needed zero-padded output.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the full warning list after the statement that triggered it. It names what is deprecated, and the table definition shows which columns carry a width.

    SHOW WARNINGS;
    SHOW CREATE TABLE orders\G
  2. 2Match what you see, then jump to its fix

    1. 1Warning 1681 on CREATE TABLE or ALTER TABLE, and the column types look like int(11) or bigint(20)A column is declared with a display width such as INT(11)Fix
    2. 2The definition has INT(4) ZEROFILL (or similar) and the app relies on 0005-style outputZEROFILL is used to pad numbersFix

What does the error look like?

  • Warning: #1681 Integer display width is deprecated and will be removed in a future release.

Cause 1 of 2

A column is declared with a display width such as INT(11)

1Check

Find the columns that have a number in brackets after an integer type, in your schema file, migration or dump.

SHOW CREATE TABLE orders\G
-- look for int(11), bigint(20), smallint(5)

2Fix

Remove the number. The manual states that the display width does not constrain the range of values the column stores, so nothing about the data changes.

-- before
CREATE TABLE orders (id INT(11) NOT NULL, qty SMALLINT(5) NOT NULL);
-- after
CREATE TABLE orders (id INT NOT NULL, qty SMALLINT NOT NULL);

3Verify

Run the statement again. SHOW WARNINGS returns nothing for these columns.

SHOW WARNINGS;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

ZEROFILL is used to pad numbers

1Check

ZEROFILL is deprecated together with the display width. It also adds UNSIGNED automatically, and it is ignored in expressions and UNION queries.

SHOW CREATE TABLE tickets\G

2Fix

Store the plain integer and pad it when you read it, with LPAD(), or keep the formatted number in a CHAR column.

ALTER TABLE tickets MODIFY number INT UNSIGNED NOT NULL;
SELECT LPAD(number, 4, '0') AS ticket_no FROM tickets;

3Verify

The query returns 0005 for the value 5 and the warning is gone.

SELECT LPAD(5, 4, '0');

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Write integer columns without a number, for example INT or BIGINT, in new schemas and migrations.
  • Do the zero padding in the query or in the application, not in the column type.
  • Check SHOW WARNINGS after schema changes so deprecations show up before they become errors.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.