Bug #14871 mysqldump: invalid view dump output
Submitted: 11 Nov 2005 17:26 Modified: 27 Feb 2006 20:18
Reporter: Paul DuBois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0.13 and up OS:
Assigned to: Magnus Blåudd CPU Architecture:Any

[11 Nov 2005 17:26] Paul DuBois 
Description:
mysqldump output for a view is contained within a /!50001 ... */ comment,
but some of the syntax isn't implemented until after 5.0.1.

DEFINER and SQL SECURITY clauses not present until 5.0.13, and they
are included in the dumped definition as of 5.0.13.  Reloading into
5.0.12 or earlier will cause an error.

So it appears that output such as this:
  
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`paul`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select 1 AS `1`*/;

Should be written like this instead:

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`paul`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v` AS select 1 AS `1`*/;

Presumably a similar problem affects the WITH CHECK OPTION clause,
which is available as of 5.0.2, not 5.0.1.

How to repeat:
See above.
[11 Nov 2005 17:50] Paul DuBois 
No, it's not a duplicate of Bug#14256. That bug
concerns parsing of the definer user value. The
current bug concerns the commenting style used
to comment out version-specific parts of the
statement.  The two bugs are unrelated.
[27 Jan 2006 16:20] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1751
[21 Feb 2006 9:50] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2962
[21 Feb 2006 12:21] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2967
[24 Feb 2006 8:28] Magnus Blåudd 
Pushed a fix to 5.0.19 and 5.0.8 that makes mysqldump surround "DEFINER", "SQL SECURITY DEFINER" and "WITH CHECK OPTION" of a "CREATE VIEW" statement that are supported only from a specific version with "not in version" comments.
[27 Feb 2006 20:18] Mike Hillyer 
Documented in 5.1.8 and 5.0.19 changelogs:

 <listitem>
        <para>
          <filename>mysqldump</filename> now surrounds the <literal>DEFINER</literal>, <literal>SQL
            SECURITY DEFINER</literal> and <literal>WITH CHECK
              OPTION</literal> clauses of a <literal>CREATE
            VIEW</literal> statement with "not in version" comments to
          prevent errors in earlier versions of MySQL. (Bug #14871)
        </para>
      </listitem>