MySQL – ‘Display Width Out of Range for Column’ Error When Importing 5.1 Dump into 5.5

MySQLmysqldumpupgrade

I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server.

I created a dump with 

mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql

When I try to restore it on the new server, I get an error:

mysql -u root -p dbname < dbname.sql
ERROR 1439 (42000) at line nnnn: Display width out of range for column 'ID' (max = 255)

The col. is defined as ID bigint(1536).

Edit: The table it is attempting to restore is actually a view.

Best Answer

This is likely to be a bug in the version of MySQL 5.1 that you are running, causing the server to provide incorrect metadata to mysqldump when it uses DESCRIBE to learn about the composition of views.

http://bugs.mysql.com/bug.php?id=65379

Background: any time you create a view that references a table, that table must already exist before you can create a view that references it. For this reason, mysqldump creates all the tables, and then all the views. That solves the "views depend on tables" issue... but when views reference other views, the referenced view also has to exist before a view that references it can be created. To work around these semi-circular dependencies without having to actually resolve them, mysqldump generates a dummy placeholder table for each view, with the same columns the view has, first ... and then later drops each dummy table and replaces it with the actual view.

The bug causes mysqldump to generate invalid CREATE TABLE statements for these dummy placeholder tables.

You can edit the existing dumpfile manually by fixing the dummy table definition, or upgrade the old server and rerun the dump, or -- possibly -- use the version of mysqldump that ships with the latest GA version of MySQL, which (I'm working from memory, here) may also contain a workaround that avoids trying to generate bad statements when the server returns nonsense metadata. The version of mysqldump for 5.6 is backwards-compatible with 5.1 servers, barring bugs or regressions.

If you don't have any views depending on these views, you should be able to simply remove the dummy table definitions from the dump files, which should then load without a problem.

Here's an example from a dumpfile, with some noise removed.

With the other tables, the dummy table is created, after dropping any table or view that already exists on the destination:

--
-- Temporary table structure for view `staff_list`
--

DROP TABLE IF EXISTS `staff_list`;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

...

/*!50001 CREATE TABLE `staff_list` (
 `ID` tinyint NOT NULL,

...

) ENGINE=MyISAM */;

Then, much further down in the dump file, the placeholder table is dropped and the real view is created:

--
-- Final view structure for view `staff_list`
--

/*!50001 DROP TABLE IF EXISTS `staff_list`*/;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`mezzell`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `staff_list` AS select `s`.`staff_id` AS `ID` ...
Related Question