Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-356

Certain VIEWs not restorable from mysqldump

    XMLWordPrintable

Details

    Description

      (see also http://bugs.mysql.com/bug.php?id=65675 )

      Description:
      Restoring a view from a mysqldump may fail with

      Incorrect column name ''

      under certain conditions, e.g. when having a constant blank string in one query that is part of a UNION, e.g.

      CREATE VIEW v1 AS SELECT id FROM table UNION SELECT ' ';

      becomes

      CREATE VIEW `v1` AS SELECT `id` AS `id` FROM `table` UNION SELECT ' ' AS ``

      in SHOW CREATE VIEW ouput (and so in mysqldump output, too). This rewritten statement fails as `` is not a valid alias.

      The UNION trick is necessary to reproduce this, a simple

      CREATE VIEW v2 AS SELECT ' ';

      becomes

      CREATE VIEW `v2` AS SELECT ' ' AS `Name_exp_1`

      How to repeat:
      mysql -u root -h 127.0.0.1 -e "DROP DATABASE IF EXISTS view_bug; CREATE DATABASE view_bug;"
      mysql -u root -h 127.0.0.1 -e "CREATE VIEW view_bug.v1 AS select User from mysql.user union all select ' ';"
      mysqldump -u root -h 127.0.0.1 --add-drop-database --databases view_bug > view_bug.sql
      mysql -u root -h 127.0.0.1 < view_bug.sql

      Suggested fix:
      Change the rewriting code so that it never emits empty `` aliases

      Attachments

        Activity

          People

            serg Sergei Golubchik
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.