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

not using key in galera nodes

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3.12
    • 10.2.23, 10.3.14, 10.4.4
    • Galera
    • Arch Linux with packages:
      mariadb 10.3.12-5
      galera 25.3.25-3

    Description

      I upgraded a mariadb galera cluster from version 10.1.37 to version 10.3.12. The galera version did not change (though the package was rebuilt). Since the update the cluster nodes show different behavior on key usage.

      MariaDB [(none)]> SELECT @@hostname;
      +------------+
      | @@hostname |
      +------------+
      | mariadb01  |
      +------------+
      1 row in set (0.000 sec)
       
      MariaDB [(none)]> CREATE TABLE `test`.`reproduce` (
          ->   `id` INT NOT NULL AUTO_INCREMENT,
          ->   `value1` INT NOT NULL,
          ->   `value2` VARCHAR(255) NOT NULL,
          ->   PRIMARY KEY (`id`),
          ->   KEY `value1` (`value1`),
          ->   KEY `value2` (`value2`));
      Query OK, 0 rows affected (0.023 sec)
       
      MariaDB [(none)]> INSERT INTO `test`.`reproduce` VALUES (NULL, 1, "1"),
          -> (NULL, 1, "2"),
          -> (NULL, 2, "3"),
          -> (NULL, 3, "4");
      Query OK, 4 rows affected (0.008 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [(none)]> EXPLAIN SELECT * FROM `test`.`reproduce` WHERE `value1` = 1;
      +------+-------------+-----------+------+---------------+--------+---------+-------+------+-------+
      | id   | select_type | table     | type | possible_keys | key    | key_len | ref   | rows | Extra |
      +------+-------------+-----------+------+---------------+--------+---------+-------+------+-------+
      |    1 | SIMPLE      | reproduce | ref  | value1        | value1 | 4       | const |    2 |       |
      +------+-------------+-----------+------+---------------+--------+---------+-------+------+-------+
      1 row in set (0.000 sec)

      That looks ok. However another node does not use the key:

      MariaDB [(none)]> SELECT @@hostname;
      +------------+
      | @@hostname |
      +------------+
      | mariadb02  |
      +------------+
      1 row in set (0.000 sec)
       
      MariaDB [(none)]> EXPLAIN SELECT * FROM `test`.`reproduce` WHERE `value1` = 1;
      +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | reproduce | ALL  | value1        | NULL | NULL    | NULL |    1 | Using where |
      +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.000 sec)

      The host receiving the structural change from client is ok, others fail.

      This effects tables with millions of rows, breaking joins that need several seconds or minutes to complete where it finished in some milliseconds before.

      This takes down the whole cluster due to massively increased load.

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            eworm Christian Hesse
            Votes:
            2 Vote for this issue
            Watchers:
            7 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.