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

Handle generated columns in mariadb-dump INSERT statements

    XMLWordPrintable

Details

    • Hide
      mariadb-dump --complete-insert when enabled (either explicitly or due to INVISIBLE columns) generated, the generated columns are omitted from the column list and values.

      Contribution thanks to Fariha Shaikh from AWS
      Show
      mariadb-dump --complete-insert when enabled (either explicitly or due to INVISIBLE columns) generated, the generated columns are omitted from the column list and values. Contribution thanks to Fariha Shaikh from AWS

    Description

      When doing a backup of a table with a virtual colum (GENERATED ALWAYS) the mysqldump tools fails to see its a generated column and adds it to the restore

      example

      CREATE TABLE example(
              firstname VARCHAR(255),
              lastname VARCHAR(255),
              fullname VARCHAR(512) AS (CONCAT(firstname, ' ', lastname)) VIRTUAL
      );
       
      insert into example(firstname, lastname) values('Donald', 'Duck');
      

      then a mysqldump --opt generates

      LOCK TABLES `example` WRITE;
      /*!40000 ALTER TABLE `example` DISABLE KEYS */;
      INSERT INTO `example` VALUES ('Donald','Duck','Donald Duck');
      /*!40000 ALTER TABLE `example` ENABLE KEYS */;
      UNLOCK TABLES;
      /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
      

      It should actually ignore it since its generated.

      The example above is very simple but we have seen issues with restoring due to this.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              Desdic Kim Gert Nielsen
              Georgi Kodinov Georgi Kodinov
              Sergei Golubchik Sergei Golubchik
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 7h
                  7h

                  Git Integration

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