MySQL Bugs: #65379: Unable to restore database dump

Bug #65379	Unable to restore database dump
Submitted:	21 May 2012 9:38	Modified:	17 Oct 2012 9:30
Reporter:	Richard Kojedzinszky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.1.61-0+squeeze1, 5.1.62, 5.5.23	OS:	Any (Debian squeeze, Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Tried to restore a daily backup file, and got an error during restore. A simplified database structure follows to reproduce the problem.

How to repeat:
$ mysql  -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 80
Server version: 5.1.61-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, 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 a (id int, v text);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v_a as select id, cast(v as signed) as v from a;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
$ mysqldump -uroot test > test.sql
$ mysql -uroot test < test.sql 
ERROR 1439 (42000) at line 48: Display width out of range for column 'v' (max = 255)

Thank you for the bug report. Verified with 5.1.62 and 5.5.23 on Windows also. The reason is wrong metadata for the view created:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.62-community MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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 database d;
Query OK, 1 row affected (0.02 sec)

mysql> use d;
Database changed
mysql> create table a (id int, v text);
Query OK, 0 rows affected (0.13 sec)

mysql> create view v_a as select id, cast(v as signed) as v from a;
Query OK, 0 rows affected (0.06 sec)

mysql> desc v_a;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | int(11)        | YES  |     | NULL    |       |
| v     | bigint(196605) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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 a (id int, v text);
Query OK, 0 rows affected (0.14 sec)

mysql> create view v_a as select id, cast(v as signed) as v from a;
Query OK, 0 rows affected (0.08 sec)

mysql> desc v_a;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| v     | bigint(65535) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.42 sec)

mysql> show create view v_a\G
*************************** 1. row ***************************
                View: v_a
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `v_a` AS select `a`.`id` AS `id`,cast(`a`.`v` as signed) A
S `v` from `a`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

Fixed as part of revision 5.6.6

Noted in 5.1.69, 5.5.31, 5.6.6 changelogs.

Incorrect metadata could be produced for columns returned from some views.