[MDEV-20918] INSTANT algorithm doesn't work with unique HASH index Created: 2019-10-29  Updated: 2023-11-03

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.4.7
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Grigorijs Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: algorithm, alter, hash, index, instant
Environment:

Centos7, mariadb 10.4.7

Issue Links:
Relates
relates to MDEV-371 Unique indexes for blobs Closed
relates to MDEV-32668 tables with UNIQUE blob columns canno... Open

 Description   

Hello,

Centos7, mariadb 10.4.7, row_format=Dynamic

INSTANT algorithm doesn't work if unique HASH index exist in table.

Also, all tables with unique HASH index have version=11 in 'Show table status'.
Tables without unique HASH index have version=10 and INSTANT alter work properly

How to reproduce:
1) CREATE TABLE `TEST` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`MERCHANT_INTERFACE_ID` INT(10) UNSIGNED NOT NULL,
`ORDER_ID` VARCHAR(50) NOT NULL,

PRIMARY KEY (`ID`),
UNIQUE INDEX `PAYMENT_ORDER_ID` (`ORDER_ID`, `MERCHANT_INTERFACE_ID`) USING HASH
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
2) SET SESSION alter_algorithm='INSTANT';
3) ALTER TABLE `TEST`
ADD COLUMN `test` INT NULL AFTER `ORDER_ID`;

/* SQL Error (1846): ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY */

 Comments   
Comment by Grigorijs [ 2019-10-30 ]

Seems like a unique HASH index block all ALTER algorithms except alter_algorithm='COPY'.
ALTER TABLE `TEST`
ADD INDEX `test_ind` (`ORDER_ID`);
/* SQL Error (1846): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY */

Also mysql cant use this index in selects (100.000.000 records in table). He using fullscan:

explain SELECT * FROM TEST FORCE INDEX(PAYMENT_ORDER_ID) p WHERE p.ORDER_ID='5db94e0c15932';
PAYMENT_ORDER_ID in possible_keys, but NULL in key,key_len and ref columns.

But this index still working fine if im trying to insert duplicate records:
SQL Error (1062): Duplicate entry '5db94e0c15932-492' for key 'PAYMENT_ORDER_ID

Comment by Grigorijs [ 2019-10-30 ]

InnoDB doesn't support hash indexes for simple tables, but:
Seems like 10.0 and 10.4 work differently with ` INDEX `name` (`col`) USING HASH` statement.

Maria10.0.17 will ignore this option and create BTREE index.
INFORMATION_SCHEMA.STATISTICS -> INDEX_TYPE will be BTREE.
only in SHOW CREATE TABLE we can see `by hash` option

Maria10.4.7 will try to create HASH index.
INFORMATION_SCHEMA.STATISTICS -> INDEX_TYPE will be HASH and CARDINALITY will be NULL.
in SHOW CREATE TABLE will be `by hash` option.
We will receive no errors after creating this index, but he will block any alter algorithms exept `COPY`. And this index will never be used by maria for optimization your queries

Its not really bug, but it can cause problems after migration from 10.0 to 10.4

Comment by Daniel Black [ 2023-11-03 ]

Algorithms on old/new key are unequal, so not InnoDB related.

Thread 32 "mariadbd" hit Breakpoint 1, compare_keys_but_name (table_key=0x7fff80035960, new_key=0x7fff80017618, alter_info=0x7ffff424f010, table=0x7fff80034860, 
    new_pk=0x7fff80017560, old_pk=0x7fff800358a8) at /home/dan/repos/mariadb-server-10.4/sql/sql_table.cc:6851
 
6851	  if (table_key->algorithm != new_key->algorithm)
 
(gdb) n
 
6852	    return Compare_keys::NotEqual;
 
(gdb) p *table_key
 
$5 = {key_length = 8, flags = 40, user_defined_key_parts = 1, usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 41, ext_key_part_map = 0, overlapped = {buffer = {2}}, 
  constraint_correlated = {buffer = {2}}, name = {str = 0x7fff80033d79 "PAYMENT_ORDER_ID", length = 16}, block_size = 0, algorithm = HA_KEY_ALG_LONG_HASH, 
  is_statistics_from_stat_tables = false, {parser = 0x0, parser_name = 0x0}, key_part = 0x7fff80035a78, cache_name = 0x7fff80033ea0 "test", rec_per_key = 0x7fff80033db8, 
  read_stats = 0x0, collected_stats = 0x0, table = 0x7fff80034860, comment = {str = 0x0, length = 0}, option_list = 0x0, option_struct = 0x0}
 
(gdb) p *new_key
 
$6 = {key_length = 154, flags = 41, user_defined_key_parts = 2, usable_key_parts = 1, ext_key_parts = 0, ext_key_flags = 0, ext_key_part_map = 0, overlapped = {buffer = {0}}, 
  constraint_correlated = {buffer = {0}}, name = {str = 0x7fff80033d79 "PAYMENT_ORDER_ID", length = 16}, block_size = 0, algorithm = HA_KEY_ALG_UNDEF, 
  is_statistics_from_stat_tables = false, {parser = 0x0, parser_name = 0x0}, key_part = 0x7fff800176f0, cache_name = 0x0, rec_per_key = 0x0, read_stats = 0x0, 
  collected_stats = 0x0, table = 0x0, comment = {str = 0x0, length = 0}, option_list = 0x0, option_struct = 0x0}
 
(gdb) bt
 
#0  compare_keys_but_name (table_key=0x7fff80035960, new_key=0x7fff80017618, alter_info=0x7ffff424f010, table=0x7fff80034860, new_pk=0x7fff80017560, old_pk=0x7fff800358a8)
 
    at /home/dan/repos/mariadb-server-10.4/sql/sql_table.cc:6852
 
#1  0x0000555555ec6416 in fill_alter_inplace_info (thd=0x7fff80000da0, table=0x7fff80034860, varchar=true, ha_alter_info=0x7ffff424caf0)
 
    at /home/dan/repos/mariadb-server-10.4/sql/sql_table.cc:7288
 
#2  0x0000555555ecfad5 in mysql_alter_table (thd=0x7fff80000da0, new_db=0x7fff800055a8, new_name=0x7fff80005a18, create_info=0x7ffff424f0d0, table_list=0x7fff80015cd0, 
    recreate_info=0x7ffff424efd0, alter_info=0x7ffff424f010, order_num=0, order=0x0, ignore=false) at /home/dan/repos/mariadb-server-10.4/sql/sql_table.cc:10407
 
#3  0x0000555555f70b8a in Sql_cmd_alter_table::execute (this=0x7fff80016510, thd=0x7fff80000da0) at /home/dan/repos/mariadb-server-10.4/sql/sql_alter.cc:534
 
#4  0x0000555555dd55cd in mysql_execute_command (thd=0x7fff80000da0) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:6218
 
#5  0x0000555555ddaaf3 in mysql_parse (thd=0x7fff80000da0, rawbuf=0x7fff80015ba8 "ALTER TABLE `TEST`\nADD COLUMN `test` INT NULL AFTER `ORDER_ID`", length=62, 
    parser_state=0x7ffff4250340, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:8013
 
#6  0x0000555555dc63b6 in dispatch_command (command=COM_QUERY, thd=0x7fff80000da0, packet=0x7fff8000ab71 "ALTER TABLE `TEST`\nADD COLUMN `test` INT NULL AFTER `ORDER_ID`", 
    packet_length=62, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:1857
 
#7  0x0000555555dc4c1a in do_command (thd=0x7fff80000da0) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:1378
 
#8  0x0000555555f66e70 in do_handle_one_connection (connect=0x555558dd23c0) at /home/dan/repos/mariadb-server-10.4/sql/sql_connect.cc:1420
 
#9  0x0000555555f66bd8 in handle_one_connection (arg=0x555558dd23c0) at /home/dan/repos/mariadb-server-10.4/sql/sql_connect.cc:1324
 
#10 0x00007ffff78ac897 in start_thread () from /lib64/libc.so.6
 
#11 0x00007ffff79336bc in clone3 () from /lib64/libc.so.6

Generated at Thu Feb 08 09:03:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.