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

All but last insert ignored in InnoDB tables when table locked

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.4.6, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.2.28, 10.1.42, 10.3.19, 10.4.9
    • Data Manipulation - Insert, Locking
    • 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. mariadb-insertion-bug-demo.sql
          2 kB
        2. variables.txt
          19 kB
        3. my.cnf
          0.2 kB
        4. client.cnf
          0.3 kB
        5. mysql-clients.cnf
          0.2 kB
        6. server.cnf
          0.7 kB

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20683 MariaDB Transaction insert one data 1 row affected BUT select Empty set

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20741 Inserted row missing after commit

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 17 older comments
            elenst Elena Stepanova added a comment -

            Sorry. Yes, if the tables don't exist, please run mysql_upgrade.

            elenst Elena Stepanova added a comment - Sorry. Yes, if the tables don't exist, please run mysql_upgrade.
            msypes Michael Sypes added a comment - - edited

            Ran mysql_upgrade. Lots of information spit out and I now see the *_stats tables.

            Of the three sites that displayed issues as a result, two appear to functioning normally again. The third I likely broke in other ways during my attempts to deal with the issue when it first occurred, so you can mark me down as saying that the fix works. Thanks!

            msypes Michael Sypes added a comment - - edited Ran mysql_upgrade . Lots of information spit out and I now see the *_stats tables. Of the three sites that displayed issues as a result, two appear to functioning normally again. The third I likely broke in other ways during my attempts to deal with the issue when it first occurred, so you can mark me down as saying that the fix works. Thanks!
            msypes Michael Sypes added a comment -

            I don't know if this is related, but while poking around my instance to see if it's safe to use, I noticed that Sequel Pro can't see all the users and privileges, and also tends to crap out when trying to view them.
            For example, mysql.user has 36 rows representing about 30 usernames. However, Sequel Pro is only seeing two of them, and there's nothing obvious that would those two special.
            In addition, if I try to look at the schema-specific privileges for them (which I did trying to find out what was special), nothing shows up as available, for any database.

            msypes Michael Sypes added a comment - I don't know if this is related, but while poking around my instance to see if it's safe to use, I noticed that Sequel Pro can't see all the users and privileges, and also tends to crap out when trying to view them. For example, mysql.user has 36 rows representing about 30 usernames. However, Sequel Pro is only seeing two of them, and there's nothing obvious that would those two special. In addition, if I try to look at the schema-specific privileges for them (which I did trying to find out what was special), nothing shows up as available, for any database.
            wturrell William Turrell added a comment -

            Hello again.

            • mysql_upgrade runs successfully (albeit with errors in phase 3/7 - Fixing views from mysql).
            • subsequently, I can run my mariadb-insertion-bug-demo.sql statements and no rows are missing
            • I've also imported a large database (couple of tables with 300,000+ rows each) and that's fine too
            • not seeing the problems Michael is with Sequel Pro - the number of rows in CLI and Sequel Pro matches for me (however I have fewer records in user. table - 13 vs 36)

            Thanks,
            William

            wturrell William Turrell added a comment - Hello again. mysql_upgrade runs successfully (albeit with errors in phase 3/7 - Fixing views from mysql). subsequently, I can run my mariadb-insertion-bug-demo.sql statements and no rows are missing I've also imported a large database (couple of tables with 300,000+ rows each) and that's fine too not seeing the problems Michael is with Sequel Pro - the number of rows in CLI and Sequel Pro matches for me (however I have fewer records in user. table - 13 vs 36) Thanks, William
            msypes Michael Sypes added a comment - - edited

            I've done some research on the Sequel Pro issue, and determined that it's unrelated to this this, but is a known issue, that's just new to me:
            https://github.com/sequelpro/sequelpro/issues/2982, https://github.com/sequelpro/sequelpro/issues/3139, https://github.com/sequelpro/sequelpro/issues/3167

            msypes Michael Sypes added a comment - - edited I've done some research on the Sequel Pro issue, and determined that it's unrelated to this this, but is a known issue, that's just new to me: https://github.com/sequelpro/sequelpro/issues/2982 , https://github.com/sequelpro/sequelpro/issues/3139 , https://github.com/sequelpro/sequelpro/issues/3167

            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.