MySQL Bugs: #30027: mysqldump does not dump views properly

Bug #30027	mysqldump does not dump views properly
Submitted:	25 Jul 2007 9:49	Modified:	2 Aug 2007 3:52
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
mysqldump can not dump a view properly in the following case:
  - view contains non-ASCII symbol;
  - mysqldump is called with non-compatible character set as default.

How to repeat:
set names koi8r;
CREATE VIEW db1.v4 AS SELECT 1 AS колонка1;

mysqldump --default-character-set=latin1 -u root db1 > /tmp/qqq.sql
mysql -u root db1 < /tmp/qqq.sql
--> Error.

Dump contains:
  --
  -- Temporary table structure for view `v3`
  --

  DROP TABLE IF EXISTS `v3`;
  /*!50001 DROP VIEW IF EXISTS `v3`*/;
  /*!50001 CREATE TABLE `v3` (
    `???1` int(1)
  ) */;

Suggested fix:
mysqldump uses SHOW FIELDS in order to get fields of the view.
So,
  a) mysqldump's character set should be set to binary to avoid conversion;
  b) "set names utf8" is required in the dump.

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/31707

ChangeSet@1.2562, 2007-07-27 18:20:17+04:00, anozdrin@ibm. +3 -0
  Fix for BUG#30027: mysqldump does not dump views properly.
  
  mysqldump generates view defitions in two stages:
  
    - dump CREATE TABLE statements for the temporary tables.  For each view a
      temporary table, that has the same structure as the view is created.
  
    - dump DROP TABLE statements for the temporary tables and CREATE VIEW
      statements for the view.
  
  This approach is required because views can have dependencies on each other
  (a view can use other views). So, they should be created in the particular
  order. mysqldump however is not smart enough, so in order to resolve
  dependencies it creates temporary tables first of all.
  
  The problem was that mysqldump might have generated incorrect dump for the
  temporary table when a view has non-ASCII column name. That happened when
  default-character-set is not utf8.
  
  The fix is to:
  
    1. Switch character_set_client for the mysqldump's connection to binary
       before issuing SHOW FIELDS statement in order to avoid conversion.
      
    2. Dump switch character_set_client statements to UTF8 and back for
       CREATE TABLE statement that is issued to create temporary table.

Pushed into 5.1-runtime.

Pushed into 5.1.21-beta

Noted in 5.1.21 changelog.

(Added this bug number to the entry for Bug#11986.)