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

[mysqldump] Support INSERT ... ON DUPLICATE KEY UPDATE for data dumps

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Backup
    • None

    Description

      Please consider implementing support for the following mysql feature request.

      https://bugs.mysql.com/bug.php?id=73190

      quoted here for safe keeping:
      -------------------------------------
      " [3 Jul 2014 21:23] Christopher Schultz

      Description:
      mysqldump currently supports two different options for dealing with PK collisions:

      --insert-ignore (uses INSERT IGNORE)
      --replace (uses REPLACE INTO instead of INSERT INTO)

      I'd like to request an additional option that uses INSERT ... ON DUPLICATE KEY UPDATE ... when used.

      This will allow one database to be used as a source to update another database without the following problems:

      Using INSERT IGNORE will ignore any updates to existing rows that are coming from the file being loaded "on top of" an existing database.

      Using REPLACE ends up churning the primary index, failing when foreign keys point to the record being updated (REPLACED), or ON DELETE CASCADE wiping-out records in other tables as the records in the target table are "updated" (using REPLACE).

      There are two significant downsides to using ON DUPLICATE KEY UPDATE, of course:

      1. The dump file will get much bigger, because the bulk-loading syntax for INSERT can no longer be used.
      2. The dump file will get much longer, because ON DUPLICATE KEY UPDATE requires that all values be specified twice: once in the VALUES() section and then a second time in the field=value, field=value part after "ON DUPLICATE KEY UPDATE" phrase.

      Bug 11422 http://bugs.mysql.com/bug.php?id=11422 requests the ability to simply say "ON DUPLICATE KEY UPDATE" and allow the engine to use the VALUES to update all fields in the table. Fixing bug 11422 and using the solution here would mitigate both downsides because then extended syntax could (possibly?) be used and the values would not have to be mentioned twice in the dump file.

      I have found many posts online about how to do something like this and the responses always steer the person toward --insert-ignore or --replace but there are many scenarios where the above request would be preferable.
      "
      ----------------------------
      if mariadb's mysqldump had this feature it would be an easy way to backup/restore particular data without disrupting the existing data on restore.

      thanks

      Attachments

        Activity

          People

            Unassigned Unassigned
            ITwrx whois
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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