Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.5, 10.0.6
-
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