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

Dropping data when performing multiple inserts with load data when innodb_autoinc_lock_mode=2

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.6, 10.11.7
    • None
    • None
    • None
    • CentOS7 VM, 16GB RAM, 1TB storage Mariadb 10.11.7

    Description

      I have a Galera Cluster running which was failing to insert data on large ingests. After removing Galera I discovered it was still occurring on a stand alone node. Trial and error found that innodb_autoinc_lock_mode=2 was the culprit. Whenever it is set to 2 and I perform a concurrent ingest of 100 or so files of data into a database I get multiple deadlocks and data is not reliably inserted. For the test to reproduce the issue

      Create table 'loadtest' ('id' int(11) unsigned NOT NULL AUTO_INCREMENT,
      'val' int(11),
      PRIMARY KEY ('id'),
      UNIQUE KEY ('val')
      ) ENGINE=InnoDB

      Our test script uses a collection of 2000 files that contain the values. It spins up 100 threads running load data on each file then checks to verify that the data was inserted properly.

      load data concurrent local infile 'filename1' ignore into table loadtest (val);
      then
      select from loadtest where val = 'value';

      With innodb_autoinc_lock_mode=2 multiple deadlocks occur and there is dropped data without any errors. With innodb_autoinc_lock_mode=1 there are no issues at all.

      This issue was observed in 10.11.6 and 10.11.7

      My goal is to migrate to Galera, but this appears to be a roadblock.

      Attachments

        Activity

          People

            Unassigned Unassigned
            jmyrna Uncle Walter23
            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.