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

[25 Jul 2007 9:49] Alexander Nozdrin
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.
[27 Jul 2007 14: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/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.
[27 Jul 2007 14:22] Alexander Nozdrin
Pushed into 5.1-runtime.
[1 Aug 2007 23:27] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 3:52] Paul DuBois
Noted in 5.1.21 changelog.

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