[MDEV-14542] Engine Aria index limitations if row_format is dynamic - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.7, 10.2(EOL)
Fix Version/s: 10.2.13, 10.3.5
Component/s: Data Definition - Alter Table, Storage Engine - Aria
Labels:
None
Environment:
Any host, tested on ubuntu and windows too. MariaDB >=10.2.7.

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

Attachments

Issue Links

relates to

MDEV-14326 engine ARIA with row_format=FIXED is broken

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2017-12-02 22:39 - edited

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.

Alice Sherepa added a comment - 2017-12-02 22:39 - edited 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.

Elena Stepanova added a comment - 2019-05-01 18:16

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

Elena Stepanova added a comment - 2019-05-01 18:16 It seems to have disappeared, need to bisect to find out when/why.

Elena Stepanova added a comment - 2021-02-21 17:18

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)

Elena Stepanova added a comment - 2021-02-21 17:18 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)

MariaDB Server

Engine Aria index limitations if row_format is dynamic

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration