LP:983285 - Syntax Error in mysqldump's Output When View's Algorithm is 2

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`;

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`))) */;

5) Try to read the dump with mysql gives error:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFINER=`root`@`` SQL SECURITY DEFINER *