Comment 0 for bug 983285

OS: Windows, 32 or 64 bit

When creating a view with
algorithm=2

in the view.frm file, mysqldump will produce faulty output:
/*!50001 CREATE ALGORITHM=*/
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_ansikt` AS select [...] */

First line should be:
/*!50001 CREATE ALGORITHM=UNDEFINED*/

I have no idea what versions of MySQL the views in my database where created, probably MySQL 5.5 or MariaDB 5.2. All I know is that all databases were properly updated with mysql_upgrade.exe.

I notice that views created with MariaDB 5.3 use:
algorithm=0 for UNDEFINED
algorithm=5 for MERGE
algorithm=9 for TEMPTABLE

but all my views have
algorithm=2

I suggest 2 fixes:
1) mysql_upgrade should check for views and update old or faulty settings in 'algorithm'
2) mysqldump should use UNDEFINED if an unknown value for 'algorithm' occurs.
Right now it doesn't output anything here, producing the error.

How to repeat:
==============

1) Issue these SQL commands:
=== SQL BEGIN ===
CREATE DATABASE `dumpit` CHARACTER SET utf8 COLLATE 'utf8_general_ci';
USE `dumpit`;dumpit
CREATE TABLE `a` (
 `id` INT(10) NULL,
 `number` INT(10) NULL,
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
CREATE TABLE `b` (
 `id` INT(10) NULL,
 `dvs` INT(10) NULL,
 PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
CREATE ALGORITHM = UNDEFINED DEFINER=`root`@`` VIEW `v_ansikt` AS SELECT a.id, a.number, b.dvs FROM a
LEFT JOIN b USING(id) ;
=== SQL END ===

2) Edit v_ansikt.frm, and change the value of this line to 2:
algorithm=2

3) Create a dump from the DOS command line:
C:\MariaDB\bin\mysqldump -h 127.0.0.1 -u root -p -a -C -R -i --add-drop-database -B -r c:\dump.sql

4) Dump looks like:
[...]
--
-- Final view structure for view `v_ansikt`
--

/*!50001 DROP TABLE IF EXISTS `v_ansikt`*/;
/*!50001 DROP VIEW IF EXISTS `v_ansikt`*/;
/*!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=*/
/*!50013 DEFINER=`root`@`` SQL SECURITY DEFINER */
/*!50001 VIEW `v_ansikt` AS select `a`.`id` AS `id`,`a`.`number` AS `number`,`b`.`dvs` AS `dvs` from (`a` left join `b` on((`a`.`id` = `b`.`id`))) */;