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

With a Spider table, a SELECT with WHERE involving primary key breaks following SELECTs

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4.6, 10.3, 10.4
    • 10.4.7
    • None
    • Ubuntu 18.4 on x86_64

    Description

      Doing a SELECT WHERE including the primary key of a partitioned table causes the following SELECTs to behave incorrectly.
      Essentially the following behaviour:

      INSERT INTO foo VALUES (1, '1'),(2, '2'),(3, '3'),(4, '4');
      – ok, check directly on backend nodes and values are split between them
      SELECT * FROM foo;
      – ok, returns all entries
      SELECT * FROM foo WHERE id=1;
      – ok, returns previously inserted row
      SELECT * FROM foo;
      – broken! returns only entries from the partition containing id=1!
      SELECT * FROM foo WHERE id=2;
      – ok, returns previously inserted row
      SELECT * FROM foo;
      – broken! returns only entries from the partition containing id=2!

      Note that this happens even when the SELECT's are done from different connections in spider.

      The setup is very simple, running locally, with:

      • 1 spider node
      • 2 datanodes (node1 and node2)

      No changes to spider variables, everything is default.

      I'm using the following table, partitioned BY KEY(id) in spider:

      CREATE TABLE foo
      (
      id int(10) NOT NULL,
      val varchar(50) NOT NULL,
      PRIMARY KEY(id)
      )
      ENGINE=spider
      COMMENT='wrapper "mysql", table "foo"'
      PARTITION BY KEY (id)
      (
      PARTITION p1 COMMENT = 'srv "node1"',
      PARTITION p2 COMMENT = 'srv "node2"'
      );

      This problem also happens with RANGE/HASH partitioned tables.
      Also, if the primary key is composite, say (id, val), then the select must include both columns for the problematic behavior to happen.

      Attachments

        1. spider.cnf
          0.1 kB
        2. node2.cnf
          0.1 kB
        3. node1.cnf
          0.1 kB
        4. my.cnf
          0.1 kB

        Issue Links

          Activity

            People

              Kentoku Kentoku Shiba (Inactive)
              lpacheco Leandro Pacheco (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.