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

MariaDB 10.1.15 instantly crashes when SELECT DISTINCT is combined with WHERE IN if run on an InnoDB table that contains a duplicate index

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.1.15
    • N/A
    • CentOS Linux release 7.2.1511 (Core)
      mysql Ver 15.1 Distrib 10.1.15-MariaDB, for Linux (x86_64) using readline 5.1
      cPanel version 11.56.0.25

    Description

      For MariaDB version 10.1.15, running the following query against a table with duplicate indexes instantly crashes MariaDB:

      select distinct example_row from table_crash where example_row in ('a','b');
      

      Here is the schema for table_crash:

      CREATE TABLE `table_crash` (
        `example_row` varchar(24),
        KEY `index1` (`example_row`),
        KEY `index2` (`example_row`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      The same query, run against the same table (but without the duplicate index) does not crash the server:

      CREATE TABLE `table_no_crash` (
        `example_row` varchar(24),
        KEY `index1` (`example_row`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      The same query, run against the first table schema (except this time, using MyISAM) does not crash the server:

      CREATE TABLE `table_crash` (
        `example_row` varchar(24),
        KEY `index1` (`example_row`),
        KEY `index2` (`example_row`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
      

      [root@node4 ~]# mysql mariadb_crash_poc
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 10
      Server version: 10.1.15-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [mariadb_crash_poc]> show tables;
      Empty set (0.00 sec)
       
      MariaDB [mariadb_crash_poc]> CREATE TABLE `table_crash` (
          ->   `example_row` varchar(24),
          ->   KEY `index1` (`example_row`),
          ->   KEY `index2` (`example_row`)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      Query OK, 0 rows affected, 1 warning (0.01 sec)
       
      MariaDB [mariadb_crash_poc]> CREATE TABLE `table_no_crash` (
          ->   `example_row` varchar(24),
          ->   KEY `index1` (`example_row`)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [mariadb_crash_poc]> select distinct example_row from table_crash where example_row in ('a','b');
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      MariaDB [mariadb_crash_poc]> select version();
      ERROR 2006 (HY000): MySQL server has gone away
      No connection. Trying to reconnect...
      ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111 "Connection refused")
      ERROR: Can't connect to the server
       
      unknown [mariadb_crash_poc]> \! service mysql start
      Starting mysql (via systemctl):                            [  OK  ]
      unknown [mariadb_crash_poc]> select distinct example_row from table_no_crash where example_row in ('a','b');
      No connection. Trying to reconnect...
      Connection id:    2
      Current database: mariadb_crash_poc
       
      Empty set (0.00 sec)
       
      MariaDB [mariadb_crash_poc]> select distinct example_row from table_no_crash where example_row in ('a','b');
      Empty set (0.00 sec)
      

      When it crashes, it seems to exit with exit code 1, logging absolutely nothing to the mysqld log. I have tested this many times and it is consistently reproducable each time without exception.

      I have tested this against 10.1.14 and it does not crash MySQL.

      Excerpt from mysqlbug:

      >Release:	mysql-10.1.15 (MariaDB Server)
      >Server: /usr/bin/mysqladmin  Ver 9.1 Distrib 10.1.15-MariaDB, for Linux on x86_64
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Server version		10.1.15-MariaDB
      Protocol version	10
      Connection		Localhost via UNIX socket
      UNIX socket		/var/lib/mysql/mysql.sock
      Uptime:			4 min 12 sec
       
      Threads: 2  Questions: 15  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 14  Queries per second avg: 0.059
      >C compiler:    cc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16)
       
      >C++ compiler:  c++ (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16)
       
      >Environment:
      	<machine, os, target, libraries (multiple lines)>
      System: Linux node4.sudomemo.net 3.10.0-327.10.1.el7.x86_64 #1 SMP Tue Feb 16 17:03:50 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
      Architecture: x86_64
       
      Some paths:  /usr/bin/perl /usr/local/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
      GCC: Using built-in specs.
      COLLECT_GCC=/usr/bin/gcc
      COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
      Target: x86_64-redhat-linux
      Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
      Thread model: posix
      gcc version 4.8.5 20150623 (Red Hat 4.8.5-4) (GCC) 
      Compilation info (call): CC='/usr/bin/cc'  CFLAGS=''  CXX='/usr/bin/c++'  CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
      Compilation info (used): CC='/usr/bin/cc'  CFLAGS=''  CXX='/usr/bin/c++'  CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
       
       
      Perl: This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Sudofox Austin Burk
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.