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
Attachments
Activity
Indeed, while one cannot create an indexed table...
MariaDB [test]> create table t1 (i int not null, unique
) engine=CONNECT table_type=XML file_name='/home/elenst/bzr/10.0/data/test.xml';
ERROR 1105 (HY000): Table t1 is not indexable
one can add an index to an existing table:
MariaDB [test]> create table t1 (i int not null) engine=CONNECT table_type=XML file_name='/home/elenst/bzr/10.0/data/test.xml';
Query OK, 0 rows affected (0.20 sec)
MariaDB [test]> alter table t1 add unique;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
Further problems as described:
MariaDB [test]> select * from t1 where i = 0;
ERROR 1296 (HY000): Got error 122 'Table t1 is not indexable' from CONNECT
MariaDB [test]>
MariaDB [test]> alter table t1 drop index i;
ERROR 2013 (HY000): Lost connection to MySQL server during query
#3 <signal handler called>
#4 0x0000000000000000 in ?? ()
#5 0x00000000009dcc8e in ha_connect::external_lock (this=0x7f8f3f4d8488, thd=0x7f8f46318070, lock_type=2) at 10.0/storage/connect/ha_connect.cc:3034
#6 0x0000000000852004 in handler::ha_external_lock (this=0x7f8f3f4d8488, thd=0x7f8f46318070, lock_type=2) at 10.0/sql/handler.cc:5788
#7 0x0000000000926b26 in unlock_external (thd=0x7f8f46318070, table=0x7f8f3f41f158, count=1) at 10.0/sql/lock.cc:671
#8 0x0000000000926093 in mysql_unlock_tables (thd=0x7f8f46318070, sql_lock=0x7f8f3f41f130, free_lock=true) at 10.0/sql/lock.cc:382
#9 0x0000000000926134 in mysql_unlock_some_tables (thd=0x7f8f46318070, table=0x7f8f3dbfa968, count=1) at 10.0/sql/lock.cc:400
#10 0x0000000000926451 in mysql_lock_remove (thd=0x7f8f46318070, locked=0x7f8f3f41f2b0, table=0x7f8f3f4e8c70) at 10.0/sql/lock.cc:495
#11 0x0000000000601d18 in close_all_tables_for_name (thd=0x7f8f46318070, share=0x7f8f3f4c7688, extra=HA_EXTRA_NOT_USED, skip_table=0x0) at 10.0/sql/sql_base.cc:828
#12 0x000000000071776f in mysql_alter_table (thd=0x7f8f46318070, new_db=0x7f8f3f41a740 "test", new_name=0x0, create_info=0x7f8f3dbfc470, table_list=0x7f8f3f41a170, alter_info=0x7f8f3dbfc3e0, order_num=0, order=0x0, ignore=false) at 10.0/sql/sql_table.cc:8493
#13 0x000000000077c53c in Sql_cmd_alter_table::execute (this=0x7f8f3f41a770, thd=0x7f8f46318070) at 10.0/sql/sql_alter.cc:312
#14 0x000000000066aa1a in mysql_execute_command (thd=0x7f8f46318070) at 10.0/sql/sql_parse.cc:5037
#15 0x000000000066dab8 in mysql_parse (thd=0x7f8f46318070, rawbuf=0x7f8f3f41a088 "alter table t1 drop index i", length=27, parser_state=0x7f8f3dbfd630) at 10.0/sql/sql_parse.cc:6365
#16 0x00000000006609fa in dispatch_command (command=COM_QUERY, thd=0x7f8f46318070, packet=0x7f8f4cbea071 "alter table t1 drop index i", packet_length=27) at 10.0/sql/sql_parse.cc:1296
#17 0x000000000065fd9c in do_command (thd=0x7f8f46318070) at 10.0/sql/sql_parse.cc:993
#18 0x0000000000777bcd in do_handle_one_connection (thd_arg=0x7f8f46318070) at 10.0/sql/sql_connect.cc:1379
#19 0x0000000000777920 in handle_one_connection (arg=0x7f8f46318070) at 10.0/sql/sql_connect.cc:1293
#20 0x0000000000a8f88d in pfs_spawn_thread (arg=0x7f8f4636a990) at 10.0/storage/perfschema/pfs.cc:1853
#21 0x00007f8f6be4cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#22 0x00007f8f6a99ba7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
The CONNECT documentation clearly indicates what table types are not indexable and why. If not (I will check that) it is reported as an error in the CREATE TABLE statement.
However, the fact that a wrong (void) one is created using ALTER (and perhaps CREATE INDEX) and that trying to drop it cause a crash of the server is a bug indeed. Thanks to have reported it.
Until it is fixed (soon), a simple bypass is not to try fooling the system with ALTER and to use those not indexable table types with no index.
All ALTER, CREATE or DROP statement trying to act on index for a table type that is not indexable now result in error or warning (for DROP)
Note: CSV files work fine with indexes.