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

Cannot use indexes with HTML tables in CONNECT storage engine

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.5, 10.0.6
    • 10.0.6, 10.0.7
    • None
    • CentOS 6.4 x86_64

    Description

      I have created a couple of HTML tables from an InnoDB table (employees and departments) - the original tables are from the employees DB used in MySQL training - https://dev.mysql.com/doc/employee/en/.

      The tables are:

      CREATE TABLE `employees` (
        `emp_no` int(11) NOT NULL,
        `birth_date` date NOT NULL,
        `first_name` varchar(14) NOT NULL,
        `last_name` varchar(16) NOT NULL,
        `hire_date` date NOT NULL
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='/var/lib/mysql_connect/employees.html' `header`=yes `option_list`='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow'

      and:

      CREATE TABLE `departments` (
        `dept_no` char(4) NOT NULL,
        `dept_name` varchar(40) NOT NULL,
        UNIQUE KEY `dep` (`dept_no`)
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=XML `file_name`='/var/lib/mysql_connect/departments.html' `header`=yes `option_list`='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow'

      As you can see, I have added indexes with no problems.

      When I try to use the indexes, I have all sort of problems:

      === Accessing the unique index in departments:
      MariaDB [connect_examples]> select * from departments where dept_no = 'd006';
      ERROR 1296 (HY000): Got error 122 'Table departments is not indexable' from CONNECT

      === Dropping the index in departments:
      MariaDB [connect_examples]> drop index dep on departments;
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      === Using EXPLAIN to test the employees window:
      MariaDB [connect_examples]> explain select * from employees where emp_no = 10005;
      +------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
      | id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
      +------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
      |    1 | SIMPLE      | employees | ALL  | employess_ix  | NULL | NULL    | NULL | 300024 | Using where |
      +------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
      1 row in set (42.73 sec)

      The index from employees is not used and I cannot drop it.

      Thanks!
      -ivan

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            izoratti Ivan Zoratti (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.