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

Cannot SHOW CREATE VIEW if underlying tabels are ALTERed

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

          Despite obscure information in the public reports or the lack thereof, the bug is actually fixed in 5.7. Now SHOW CREATE VIEW produces the desired output with a warning about referenced tables.

          Here is the commit comment which provides more details:

          revno: 4635
          revision-id: raghav.kapoor@oracle.com-20121005072219-jgkpyjuq5c3ogvjs
          parent: mysql-builder@oracle.com-20121005070911-e8d741cf8hmucsts
          committer: Raghav Kapoor <raghav.kapoor@oracle.com>
          branch nick: mysql-trunk-bug-12762393
          timestamp: Fri 2012-10-05 12:52:19 +0530
          message:
            BUG#12762393 - CAN NOT SHOW CREATE VIEW IF UNDERLYING TABLES 
                           ARE ALTERED 
            
            BACKGROUND:
            There was refactoring done in 5.1, which changed the way the 
            warnings are suppressed in the server. Before the refactoring,
            the warnings were suppressed (or propagated to errors) based 
            on the global flag.
            Afterwards the warnings were suppressed/propagated by local 
            instances of Internal_error_handler on a per case base.
            This bug is an indication of an issue with that refactoring.
            The issue was that ER_BAD_FIELD_ERROR was forgotten to be 
            handled when opening the underlying tables in SHOW CREATE VIEW.
            
            FIX:
            As a fix for this bug, added a case to handle ER_BAD_FIELD_ERROR
            in Show_create_error_handler to generate a warning instead of error 
            when columns of view's underlying table is changed.
            Also some redundant error codes have been removed in
            Show_create_error_handler and in TABLE_LIST::hide_view_error()
            by adding the appropriate comments.

          It would be nice to backport the bugfix into 10.0 line if possible.

          elenst Elena Stepanova added a comment - Despite obscure information in the public reports or the lack thereof, the bug is actually fixed in 5.7. Now SHOW CREATE VIEW produces the desired output with a warning about referenced tables. Here is the commit comment which provides more details: revno: 4635 revision-id: raghav.kapoor@oracle.com-20121005072219-jgkpyjuq5c3ogvjs parent: mysql-builder@oracle.com-20121005070911-e8d741cf8hmucsts committer: Raghav Kapoor <raghav.kapoor@oracle.com> branch nick: mysql-trunk-bug-12762393 timestamp: Fri 2012-10-05 12:52:19 +0530 message: BUG#12762393 - CAN NOT SHOW CREATE VIEW IF UNDERLYING TABLES ARE ALTERED BACKGROUND: There was refactoring done in 5.1, which changed the way the warnings are suppressed in the server. Before the refactoring, the warnings were suppressed (or propagated to errors) based on the global flag. Afterwards the warnings were suppressed/propagated by local instances of Internal_error_handler on a per case base. This bug is an indication of an issue with that refactoring. The issue was that ER_BAD_FIELD_ERROR was forgotten to be handled when opening the underlying tables in SHOW CREATE VIEW. FIX: As a fix for this bug, added a case to handle ER_BAD_FIELD_ERROR in Show_create_error_handler to generate a warning instead of error when columns of view's underlying table is changed. Also some redundant error codes have been removed in Show_create_error_handler and in TABLE_LIST::hide_view_error() by adding the appropriate comments. It would be nice to backport the bugfix into 10.0 line if possible.

          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.