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

No human-readable, fast, pipe-able dump & restore mechanism available

    XMLWordPrintable

Details

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

    Description

      Problem

      Each of the available mysqldump export formats possesses a significant problem:

      Approach Inadequate because Existing bug report(s)
      --extended-insert Lines too long for human-readability https://bugs.mysql.com/bug.php?id=65465 https://stackoverflow.com/q/15750535 https://stackoverflow.com/q/1293529
      --skip-extended-insert Too slow due to unnecessary INSERT statements Not a bug report per se because slow behaviour is expected, but one user reported 17m20s for --skip-extended-insert vs 19s for --extended-insert.
      --xml Dump cannot be restored via mysqldump https://bugs.mysql.com/bug.php?id=91119
      --tab Cannot easily be dumped/restored via shell redirection over SSH to mysqldump https://bugs.mysql.com/bug.php?id=36405

      The fact that there is no good solution to this problem is, at least in my view, a reason to avoid MySQL or MariaDB wherever possible, until the problem is fixed.

      I view it as a major issue, because there is no workaround available: with MySQL/MariaDB, you have no choice but to compromise one of those important factors, and if that is not viable, then an alternative product must be used. However, others may disagree, so I have reported it as a "minor" issue. Please modify that to "major" if you agree that this warrants it.

      Proposed solution

      As described here by Kendall Bennett, `mysqldump --extended-insert` should be fixed to format its output the same way as MySQL Administrator used to.

      I.e. when fixed, mysqldump --extended-insert would yield this:

      INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES 
       (887,'0.0000'),
       (191607,'1.0300');
      

      instead of this:

      INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES (887,'0.0000'),191607,'1.0300');
      

      According to Tim Riker and Lon Binder the fix is as simple as this:

      On line 3506, you can see where the row-ending comma is output:
      fputc(',',md_result_file); /* Always row break */

      Simply insert this line immediately after line 3506:
      fputc('\n',md_result_file); /* Lon Binder says wrap that line! */

      If Riker and Binder are correct that the fix is that trivial, then I hope the MariaDB devs/maintainers will be able to easily and quickly patch and backport it into the versions of mysqldump that they ship.

      Thank you for your work on MariaDB!

      Attachments

        Activity

          People

            Unassigned Unassigned
            sampablokuper Sam Kuper
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.