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

All but last insert ignored in InnoDB tables when table locked

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.1, 10.4.6, 10.2, 10.3, 10.4
    • 10.2.28, 10.1.42, 10.3.19, 10.4.9
    • macOS High Sierra 10.13.6, homebrew install stable 10.4.6 (bottled), mysql --version: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) using readline 5.1

    Description

      It's common when doing bulk inserts (e.g. when importing an SQL dump) for the table to be locked first. Whenever this happens, apparently only on InnoDB tables, all but the final insert is being ignored.

      $ mysqladmin create mariadb_bug
      $ mysql mariadb_bug < mariadb-insertion-bug-demo.sql
      

      Here's a demo using a standard WordPress database structure from one table, and three demo rows.

      Expected: 3 rows for users foo, bar and baz

      Observed - single row - the last one baz.

      $ mysql mariadb_bug

      MariaDB [mariadb_bug]> select * from wp_users;
      +----+------------+------------------------------------+---------------+-----------------+----------+---------------------+---------------------+-------------+--------------+
      | ID | user_login | user_pass                          | user_nicename | user_email      | user_url | user_registered     | user_activation_key | user_status | display_name |
      +----+------------+------------------------------------+---------------+-----------------+----------+---------------------+---------------------+-------------+--------------+
      |  3 | baz        | $P$B/p0FE1jehIvSqVgw3ytaBGS1234567 | baz           | baz@example.com |          | 2017-11-14 11:19:35 |                     |           0 | baz          |
      +----+------------+------------------------------------+---------------+-----------------+----------+---------------------+---------------------+-------------+--------------+
      1 row in set (0.001 sec)
      
      

      I can confirm that if the inserts are batched, the last batch (of however many rows were in the insert statement) is retained.

      If can also confirm the same behaviour if I run the commands manually in SQL, one at at a time, rather than piping the SQL dump in - the table has 3 rows if I omit the lock and unlock statements, but only one if I include them.

      Multiple people are experiencing this with Drupal installations (either fresh installs, or when clearing the cache, which repopulates the Symfony routing table).

      When testing in_ MySQL_ (installed via homebrew or other means) in the same environment, rather than mariadb, the bug doesn't appear.

      It's possible this began in an earlier version than 10.4.6 (someone mentioned 10.4.4 on the above Drupal issue). I looked at the release notes for 10.4.7 (not available on Homebrew yet) and didn't spot anything related.

      Attachments

        1. client.cnf
          0.3 kB
        2. mariadb-insertion-bug-demo.sql
          2 kB
        3. my.cnf
          0.2 kB
        4. mysql-clients.cnf
          0.2 kB
        5. server.cnf
          0.7 kB
        6. variables.txt
          19 kB

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              wturrell William Turrell
              Votes:
              1 Vote for this issue
              Watchers:
              9 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.