[MDEV-14542] Engine Aria index limitations if row_format is dynamic Created: 2017-11-30  Updated: 2021-02-21  Resolved: 2021-02-21

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - Aria
Affects Version/s: 10.2.7, 10.2
Fix Version/s: 10.2.13, 10.3.5

Type: Bug Priority: Major
Reporter: Janos Szentgyorgyi Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

Any host, tested on ubuntu and windows too. MariaDB >=10.2.7.

Issue Links:
Relates
relates to MDEV-14326 engine ARIA with row_format=FIXED is ... Closed

 Description   

Hi,

I discovered a bug when using storage-engine ARIA with row_format=DYNAMIC (only mariaDB 10.2.7 or greather), the table contain ~9million rows. I tested this issue, with mariaDB 10.0.33, 10.1.29 and 10.2.6 and this version are correct). If i add more index to table, i got this error:

[Warning] Warning: Enabling keys got errno 136 on test.#sql-3074_a, retrying

Table definition:

CREATE TABLE `aria_dynamic` (
 
	`id` INT(10) UNSIGNED NOT NULL,
 
	`u_id` INT(10) UNSIGNED NOT NULL,
 
	`v_id` INT(10) UNSIGNED NOT NULL,
 
	`v_date` DATETIME NOT NULL,
 
	`status` INT(1) UNSIGNED NOT NULL DEFAULT '0',
 
	`deletable` INT(1) UNSIGNED NOT NULL DEFAULT '0',
 
	PRIMARY KEY (`id`),
 
	UNIQUE INDEX `*u_id*` (`u_id`, `v_id`, `status`),
 
	INDEX `*v_date*` (`v_date`),
 
	INDEX `*deletable*` (`deletable`),
 
        INDEX `*v_id_status*` (`v_id`, `status`)
 
)
 
COLLATE='utf8_general_ci'
 
ENGINE=Aria
 
ROW_FORMAT=DYNAMIC
 
AUTO_INCREMENT=42606736;

Table status:

Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment
 
aria_dynamic|Aria|10|Dynamic|9030620|25|231952596|281474976710655|444153856|0|NULL|2017-11-30 08:59:33|2017-11-30 09:00:01|2017-11-30 09:00:00|utf8_general_ci|NULL|page_checksum=0 row_format=DYNAMIC transactional=0|...

So, if i import the table dump from sql file, the process stopped on the add indexes progress.
If i create table, and add only the data, it's working fine, until i add 4 indexes (primary, unique u_id, v_date and deletable). But if i add the v_id_status index, the process stopped with error no:136.
This table full of data and indexes working fine on 10.0.33 (and tested 10.1.29, 10.2.6 where is it ok), but try on 10.2.7 (or greather) and the import or add more indexes failed...

Remark:
If the deletable and status fields converted from int to tinyint, everything fine. Or try half of the data (~4-5 millions row) also fine...
I tried with row_format = PAGE, and worked fine, with full of data and indexes...

Tables status:

Name|Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time|Update_time|Check_time|Collation|Checksum|Create_options|Comment
 
aria_dynamic|Aria|10|Dynamic|9030620|25|231952596|281474976710655|444153856|0|NULL|2017-11-30 08:59:33|2017-11-30 09:00:01|2017-11-30 09:00:00|utf8_general_ci|NULL|page_checksum=0 row_format=DYNAMIC transactional=0|...
 
aria_dynamic_half|Aria|10|Dynamic|5030620|25|127768488|281474976710655|318177280|0|NULL|2017-11-30 09:10:26|2017-11-30 09:10:47|2017-11-30 09:10:46|utf8_general_ci|NULL|page_checksum=1 row_format=DYNAMIC|...
 
aria_page|Aria|10|Page|9030620|32|290136064|17592186011648|407986176|0|NULL|2017-11-29 14:20:18|2017-11-29 14:21:03|2017-11-29 14:21:03|utf8_general_ci|NULL|page_checksum=0 row_format=PAGE transactional=0|...

regards
János

 Comments   
Comment by Alice Sherepa [ 2017-12-02 ]

Thanks for the report!
The problem is reproducible after commit dd8474b1dc556d0ea9491d19 in MariaDB 10.2

[Warning] Warning: Enabling keys got errno 136 on test.#sql-5eac_9, retrying
 
mysqld: /home/alice/AM/m8-10.2/src/storage/maria/ha_maria.cc:2032: virtual int ha_maria::enable_indexes(uint): Assertion `thd->killed != 0' failed.
 
[ERROR] mysqld got signal 6 ;

testcase:

--source include/have_sequence.inc
 
 
 
CREATE TABLE `t1` (
 
    `id` INT(10) UNSIGNED NOT NULL,
 
    `u_id` INT(10) UNSIGNED NOT NULL,
 
    `v_id` INT(10) UNSIGNED NOT NULL,
 
    `v_date` DATETIME NOT NULL,
 
    `status` INT(1) UNSIGNED NOT NULL DEFAULT 0,
 
    `deletable` INT(1) UNSIGNED NOT NULL DEFAULT 0,
 
    PRIMARY KEY (`id`)
 
) ENGINE=Aria ROW_FORMAT=DYNAMIC;
 
 
 
insert into t1 SELECT seq,seq,seq,now(),0,0 FROM seq_1_to_10000000;
 
 
 
alter table t1 add    UNIQUE INDEX `*u_id*` (`u_id`, `v_id`, `status`);
 
alter table t1 add     INDEX `*v_date*` (`v_date`);
 
alter table t1 add INDEX `*deletable*` (`deletable`);
 
alter table t1 add index  `*v_id_status*` (`v_id`, `status`);
 
 
 
drop table t1;


Thread 1 (Thread 0x7fabb931d700 (LWP 30259)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x000055c0466036ff in my_write_core (sig=6) at /home/alice/git/10.2/mysys/stacktrace.c:477
 
#2  0x000055c045e952ec in handle_fatal_signal (sig=6) at /home/alice/git/10.2/sql/signal_handler.cc:303
 
#3  <signal handler called>
 
#4  0x00007fabbf04a428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007fabbf04c02a in __GI_abort () at abort.c:89
 
#6  0x00007fabbf042bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x55c04693bf87 "thd->killed != 0", file=file@entry=0x55c04693b830 "/home/alice/git/10.2/storage/maria/ha_maria.cc", line=line@entry=2032, function=function@entry=0x55c04693c900 <ha_maria::enable_indexes(unsigned int)::__PRETTY_FUNCTION__> "virtual int ha_maria::enable_indexes(uint)") at assert.c:92
 
#7  0x00007fabbf042c82 in __GI___assert_fail (assertion=0x55c04693bf87 "thd->killed != 0", file=0x55c04693b830 "/home/alice/git/10.2/storage/maria/ha_maria.cc", line=2032, function=0x55c04693c900 <ha_maria::enable_indexes(unsigned int)::__PRETTY_FUNCTION__> "virtual int ha_maria::enable_indexes(uint)") at assert.c:101
 
#8  0x000055c046493daf in ha_maria::enable_indexes (this=0x7faba81664d0, mode=2) at /home/alice/git/10.2/storage/maria/ha_maria.cc:2032
 
#9  0x000055c04649447c in ha_maria::end_bulk_insert (this=0x7faba81664d0) at /home/alice/git/10.2/storage/maria/ha_maria.cc:2221
 
#10 0x000055c045c0b007 in handler::ha_end_bulk_insert (this=0x7faba81664d0) at /home/alice/git/10.2/sql/handler.h:2898
 
#11 0x000055c045cfafe9 in copy_data_between_tables (thd=0x7faba8000a98, from=0x7faba8176848, to=0x7faba8165938, create=..., ignore=false, order_num=0, order=0x0, copied=0x7fabb93194e8, deleted=0x7fabb93194f0, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7fabb931a1c0) at /home/alice/git/10.2/sql/sql_table.cc:10026
 
#12 0x000055c045cf9316 in mysql_alter_table (thd=0x7faba8000a98, new_db=0x7faba8186cc0 "test", new_name=0x0, create_info=0x7fabb931ae00, table_list=0x7faba8115d80, alter_info=0x7fabb931ad20, order_num=0, order=0x0, ignore=false) at /home/alice/git/10.2/sql/sql_table.cc:9450
 
#13 0x000055c045d7251b in Sql_cmd_alter_table::execute (this=0x7faba80f9d80, thd=0x7faba8000a98) at /home/alice/git/10.2/sql/sql_alter.cc:324
 
#14 0x000055c045c2e667 in mysql_execute_command (thd=0x7faba8000a98) at /home/alice/git/10.2/sql/sql_parse.cc:6197
 
#15 0x000055c045c33241 in mysql_parse (thd=0x7faba8000a98, rawbuf=0x7faba806d020 "alter table t1 add index  `*v_id_status*` (`v_id`, `status`)", length=60, parser_state=0x7fabb931c1f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7887
 
#16 0x000055c045c20e66 in dispatch_command (command=COM_QUERY, thd=0x7faba8000a98, packet=0x7faba8007fe9 "", packet_length=60, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1805
 
#17 0x000055c045c1f7bd in do_command (thd=0x7faba8000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
 
#18 0x000055c045d6d232 in do_handle_one_connection (connect=0x55c04992eb28) at /home/alice/git/10.2/sql/sql_connect.cc:1354
 
#19 0x000055c045d6cfb2 in handle_one_connection (arg=0x55c04992eb28) at /home/alice/git/10.2/sql/sql_connect.cc:1260
 
#20 0x000055c0460c9f2e in pfs_spawn_thread (arg=0x55c04988e6e8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1863
 
#21 0x00007fabbfc876ba in start_thread (arg=0x7fabb931d700) at pthread_create.c:333
 
#22 0x00007fabbf11c3dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

similar testcase: 

CREATE TABLE `t1` (
 
    `id` INT PRIMARY KEY,
 
    `u_id` INT,
 
    `v_id` INT,
 
    `v_date` DATETIME,
 
    `status` INT(1) DEFAULT 0,
 
    `deletable` INT(1) DEFAULT 0
 
) ENGINE=Aria ROW_FORMAT=DYNAMIC;
 
 
 
insert into t1 SELECT seq,seq,seq,now(),0,0 FROM seq_1_to_10000000;
 
 
 
alter table t1 add    UNIQUE INDEX `*u_id*` (`u_id`, `v_id`, `status`);
 
alter table t1 add     INDEX `*v_date*` (`v_date`);
 
alter table t1 add INDEX `*deletable*` (`deletable`);
 
alter table t1 add index  `*v_id_status*` (`v_id`, `status`);

on 10.2.11:

ERROR 1034 (HY000): Create index by sort failed

2017-12-02 16:51:39 140589676951296 [Warning] Warning: Enabling keys got errno 136 on test.#sql-6782_9, retrying
2017-12-02 23:15:48 140517019555648 [ERROR] mysqld: Can't lock aria control file 'm6-10.2.11/dt/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
2017-12-02 23:16:19 140517019555648 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file 'm6-10.2.11/dt/aria_log_control'
2017-12-02 23:16:19 140517019555648 [ERROR] Plugin 'Aria' init function returned error.
2017-12-02 23:16:19 140517019555648 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

Comment by Elena Stepanova [ 2019-05-01 ]

It seems to have disappeared, need to bisect to find out when/why.

Comment by Elena Stepanova [ 2021-02-21 ]

The failure apparently stopped happening after this commit:

commit 50de7d13036c7bacbaf460bfcaa77cfbe1ad4123
 
Author: Monty <monty@mariadb.org>
 
Date:   Thu Jan 18 01:41:52 2018 +0200
 
 
 
    Fixed MDEV-14326 engine ARIA with row_format=FIXED is broken
 
    
    The problem was that max_size was acciently set to 1 in some
 
    cases.
 
    
    Other things:
 
    - Adjust max_rows if min_rows > max_rows.
 
    - Removed not used variable varchar_length
 
    - Adjusted max_pack_length (safety fix)

Generated at Thu Feb 08 08:14:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.