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

mysqldump does not dump data if table name is same as view earlier on

    XMLWordPrintable

    Details

      Description

      Hi everybody,

      If mysqldump is run with -A (--all-databases) and a VIEW with the same name is exported as the next schema where it is a table it will not export any of the table's data.

      Test script:

      DROP DATABASE IF EXISTS dumped_last;
      DROP DATABASE IF EXISTS dumped_first;
      CREATE DATABASE dumped_last;
      CREATE DATABASE dumped_first;
      USE dumped_last
      CREATE TABLE nonuniquename (id serial);
      INSERT INTO nonuniquename VALUES(NULL);
      use dumped_first
      CREATE TABLE useless (id serial);
      CREATE VIEW nonuniquename AS SELECT * FROM useless;

      Then, dump the data:

      mysqldump -A -uroot -p > dump.sql

      And verify that the INSERT INTO uniquename is not present in the dump file. Please verify that 'dump_first' is dumped as first database and 'dumped_last' is dumped as second database. Also -A (or probably --all-databases) is required for this to occur.

      The dump file gives a note about the 'Temporary table structure':

      --
      -- Temporary table structure for view `nonuniquename`
      --
       
      DROP TABLE IF EXISTS `nonuniquename`;
      /*!40101 SET @saved_cs_client     = @@character_set_client */;
      /*!40101 SET character_set_client = utf8 */;
      CREATE TABLE `nonuniquename` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        UNIQUE KEY `id` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;
       
      --
      -- Current Database: `mysql`
      --

        Attachments

          Activity

            People

            Assignee:
            elenst Elena Stepanova
            Reporter:
            michaeldg Michaël de groot
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration