MySQL Bugs: #67499: mysqldump - Bad column type exporting VIEWS

Bug #67499	mysqldump - Bad column type exporting VIEWS
Submitted:	7 Nov 2012 9:21	Modified:	7 Nov 2012 14:06
Reporter:	Antoine Verger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.5.28-0ubuntu0.12.04.2	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	command-line, mysqldump, Views

Description:
The mysqldump command-line does not correctly export VIEWS anymore. The temporary table structure dumped for view contains columns with the same single type : tinyint.

How to repeat:
1 - Create a table mytable with two colums : 

CREATE TABLE IF NOT EXISTS mytable(id int(11),name varchar(255));

2 - Create a view which does a simple select on this table :

CREATE VIEW myview AS select * from mytable;

3 - Run the mysqldump command

mysqldump --host xxxxx --user xxx myschema > dump.sql

4 - Find in dump.sql :

--
-- Temporary table structure for view `myview`
--

DROP TABLE IF EXISTS `myview`;
/*!50001 DROP VIEW IF EXISTS `myview`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `myview` (
  `id` tinyint NOT NULL,
  `name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

which is absolutely not correct.

Why do you think this is a problem? Had you tried to restore and got wrong view?

Look, this is the entire code generated:

...
DROP TABLE IF EXISTS `myview`;
/*!50001 DROP VIEW IF EXISTS `myview`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `myview` (
  `id` tinyint NOT NULL,
  `name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Final view structure for view `myview`
--

/*!50001 DROP TABLE IF EXISTS `myview`*/;
/*!50001 DROP VIEW IF EXISTS `myview`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `myview` AS select `mytable`.`id` AS `id`,`mytable`.`name` AS `nam
e` from `mytable` */;
...

So, table is created temporary with the same name as view, then dropped and then view is recreated properly. tinyint as column types allows to save *something* in the process probably :)

Hi Valeriy,

so you are right : the restore works normally. I had integration tests parsing the result of the mysqldump command broken after the last apt update in my current project that's why I've reported this issue. By curiosity, do you have any idea for what this temporary table is used ?

Also see no bug here.

The purpose of the placeholder tables is to satisfy any dependencies.  I believe mysqldump doesn't figure out all the dependencies beforehand, e.g. view1 can refer to view2 who refers to view3, etc.  But in which order should those be created?  It's not easy to determine that at the moment.

Thank you for your explanations. I set the issue to closed.