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

mysqldump fails with "Got error: 1356" if the database contains a view with a subquery

    XMLWordPrintable

Details

    Description

      Impact

      Database users cannot use data export if the database contains views with subqueries. This causes difficulties in backing up data and causes an application compatibility issue with newer versions of MariaDB.

      Steps to reproduce

      1. Create database and user, grant all privileges:

        mysql -uroot
        

        CREATE DATABASE test;
        CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';
        GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost';
        

      2. Create test data:

        mysql -utest -ppassword
        

        USE test;
        CREATE TABLE `test_table` (`id` INT NOT NULL);
        CREATE VIEW `test_view` AS SELECT * FROM (SELECT * FROM `test_table`) tt;
        

      3. Try to dump the database:

        mysqldump test -utest -ppassword
        

      Actual result

      The dump fails with an error:

      mysqldump: Got error: 1356: "View 'test.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES
      

      Expected result

      The dump procedure is completed successfully.

      Additional information

      The issue is reproduced on versions: 10.5.8, 10.4.17. But I have not tested earlier versions (10.5.* and 10.4.*).

      The issue does not occur on earlier versions of MariaDB (10.3.27 works correct).

      Attachments

        Activity

          People

            serg Sergei Golubchik
            nrybalov Nikita Rybalov
            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.