Syntax Error in mysqldump's Output When View's Algorithm is 2

[nbrnhardt]

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 *

[nbrnhardt (nb-k)]

MariaDB used: 5.3.6

[Elena Stepanova (elenst)]

The change happened between MariaDB 5.2 and 5.3.
In 5.2 numeric algorithm values are: undefined=0, temptable=1, merge=2.
In 5.3: undefined=0, merge=5, temptable=9.
As described, it causes problems with dump after upgrade from 5.2 to 5.3.

In MySQL 5.1-5.6 values are 0, 1, 2.

To reproduce the problem:

- Start MariaDB server 5.2;

- execute
CREATE ALGORITHM=UNDEFINED VIEW v_undef AS SELECT 1;
CREATE TABLE t AS SELECT 2;
CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM t;
 CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT 3;

- stop server;
- start MariaDB server 5.3 on the same datadir;
- (optional step, it does not make the difference): run mysql_upgrade
- run SHOW CREATE VIEW v_merge, see that it shows CREATE ALGORITHM=DEFINER= ...
- run mysqldump for the schema, see that the dump says /*!50001 CREATE ALGORITHM=*/ for v_merge and v_temptable;
- attempt to feed the dump to MySQL client, see error 1064 (syntax error).

Thus, it breaks compatibility between versions of MariaDB, and between MySQL and MariaDB.

[Elena Stepanova (elenst)]

Fix released in 5.3.7 and 5.5.24