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

Cannot SHOW CREATE VIEW if underlying tabels are ALTERed

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.1.67, 5.2.14, 5.3.12, 5.5.35, 10.0.8
    • 5.5.37, 10.0.11
    • None
    • Ubuntu Precise Pangolin

    Description

      From MySQL bug #61718 (see below for explanation why):

      Description:
      If an underlying table changes SHOW CREATE VIEW is broken in 5.1 and 5.5 - but not in 5.0

      This ia a problem because the VIEW definition needs to be adjusted. That can be difficult it you cannot display it as it was created.

      Affects various GUI clients.

      How to repeat:

      SELECT VERSION(); -- 5.0.90-community-nt
      USE test;
      DROP TABLE IF EXISTS k;
      CREATE TABLE k (a VARCHAR(20));
      DROP TABLE IF EXISTS l;
      CREATE TABLE l (b VARCHAR(20));
      CREATE VIEW m AS SELECT k.a, l.b FROM k,l;
      ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20);
      SHOW CREATE VIEW m; --success
      SHOW FULL FIELDS FROM m;
      /*
      Error Code : 1356
      View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them*/

      SELECT VERSION(); -- 5.1.57-community
      USE test;
      DROP TABLE IF EXISTS k;
      CREATE TABLE k (a VARCHAR(20));
      DROP TABLE IF EXISTS l;
      CREATE TABLE l (b VARCHAR(20));
      DROP VIEW IF EXISTS m;
      CREATE VIEW m AS SELECT k.a, l.b FROM k,l;
      ALTER TABLE `test`.`k` CHANGE `a` `aa` VARCHAR(20);
      SHOW CREATE VIEW m;
      /*
      Error Code : 1356
      View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them */
      SHOW FULL FIELDS FROM m; -- same error
      SELECT * FROM `information_schema`.`VIEWS` WHERE table_name = 'm' -- success!!

      So workaround is to SELECT from I_S.

      Suggested fix:
      Restore 5.0 behaviour. If SELECT from I_S can show the VIEW then also SHOW should.

      -----------

      We use MariaDb, and Oracle make registering and entering a comment on an existing bug way too difficult!

      This issue was originally raised as MySQL bug #54139 (1 June 2010) and then again as bug #61718 (1 July 2011) with more information. However, #61718 is closed, but the only progress was to note it as a known bug in the 5.7.0 changelog! It is noted that it affects late versions of 5.0, 5.1, 5.5, etc onwards.

      Meanwhile bug #54139 was closed as a duplicate of #61718 on 10 January 2013, so there is no open bug report anywhere, and just an acknowledgement of the bug in the 5.7.0 changelog.

      There is a workaround:

      SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE TABLE_SCHEMA='database' AND TABLE_NAME='view_name'

      but all of the sensible SQL commands for displaying the VIEW no longer work:

      MariaDB [private_17_production]> show create view data_view_854;
      ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      MariaDB [private_17_production]> show create table data_view_854;
      ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
      MariaDB [private_17_production]> desc data_view_854;
      ERROR 1356 (HY000): View 'private_17_production.data_view_854' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

      This just makes diagnosing a problem with a VIEW difficult - if you cannot see the definition of the VIEW then you cannot work out what needs to be fixed: the VIEW or the underlying tables (and which ones).

      There is a comment on bug #61718:
      What is a potential bug here is that query to I_S does NOT produce error in 5.1, while SELECT and SHOW commands do...
      In other words, make it impossible to diagnose VIEW errors rather than difficult!

      Hopefully MariaDb can make better progress...

      Attachments

        Activity

          People

            serg Sergei Golubchik
            jonathan.monahan@workbooks.com Jonathan Monahan
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.