[MDEV-12198] innodb_defragment=1 crashes server on OPTIMIZE TABLE when FULLTEXT index exists - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.4, 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.23, 10.2.6
Component/s: Storage Engine - InnoDB
Labels:
- 10.2-ga
Environment:
Debian Jessy
Linux version 3.14.32-xxxx-grs-ipv6-64 (kernel@kernel.ovh.net) (gcc version 4.7.2 (Debian 4.7.2-5) ) #7 SMP Wed Jan 27 18:05:09 CET 2016

Sprint:
10.2.6-2, 10.2.6-3

Description

create or replace   TABLE `layout` (   `id` varbinary(16) NOT NULL,   `context` enum('page','slider','slideshow','menu') NOT NULL DEFAULT 'page',   `tag` varchar(400) NOT NULL,   `hash` varchar(500) DEFAULT NULL,   `device` enum('mobile','desktop','tablet') NOT NULL DEFAULT 'desktop',   `lang` varchar(10) NOT NULL DEFAULT 'fr',   `author` varchar(80) DEFAULT NULL,   `comment` text DEFAULT NULL,   `content` mediumtext DEFAULT NULL,   `footnote` mediumtext DEFAULT NULL,   `backup` mediumtext DEFAULT NULL,   `newbie` mediumtext DEFAULT NULL,   `expert` mediumtext DEFAULT NULL,   `status` enum('active','deleted','locked','backup','readonly','draft') DEFAULT NULL,   `ctime` int(11) NOT NULL,   `mtime` int(11) NOT NULL,   `version` varchar(10) NOT NULL DEFAULT '1.0.0',   PRIMARY KEY (`id`),   UNIQUE KEY `hash` (`hash`) USING BTREE,   KEY `author` (`author`),   KEY `ctime` (`ctime`),   KEY `mtime` (`mtime`),   KEY `version` (`version`),   KEY `ltype` (`context`),   KEY `tag` (`tag`),   FULLTEXT KEY `content` (`content`) )

 ENGINE=InnoDB DEFAULT CHARSET=utf8;

MariaDB [0_2273db5d2273db62]> optimize table layout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: Can't connect to the server

unknown [0_2273db5d2273db62]> create or replace   TABLE `layout` (   `id` varbinary(16) NOT NULL,   `context` enum('page','slider','slideshow','menu') NOT NULL DEFAULT 'page',   `tag` varchar(400) NOT NULL,   `hash` varchar(500) DEFAULT NULL,   `device` enum('mobile','desktop','tablet') NOT NULL DEFAULT 'desktop',   `lang` varchar(10) NOT NULL DEFAULT 'fr',   `author` varchar(80) DEFAULT NULL,   `comment` text DEFAULT NULL,   `content` mediumtext DEFAULT NULL,   `footnote` mediumtext DEFAULT NULL,   `backup` mediumtext DEFAULT NULL,   `newbie` mediumtext DEFAULT NULL,   `expert` mediumtext DEFAULT NULL,   `status` enum('active','deleted','locked','backup','readonly','draft') DEFAULT NULL,   `ctime` int(11) NOT NULL,   `mtime` int(11) NOT NULL,   `version` varchar(10) NOT NULL DEFAULT '1.0.0',   PRIMARY KEY (`id`),   UNIQUE KEY `hash` (`hash`) USING BTREE,   KEY `author` (`author`),   KEY `ctime` (`ctime`),   KEY `mtime` (`mtime`),   KEY `version` (`version`),   KEY `ltype` (`context`),   KEY `tag` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MariaDB [0_2273db5d2273db62]> optimize table layout;

+---------------------------+----------+----------+----------+

| Table                     | Op       | Msg_type | Msg_text |

+---------------------------+----------+----------+----------+

| 0_2273db5d2273db62.layout | optimize | status   | OK       |

+---------------------------+----------+----------+----------+

1 row in set (0.00 sec)

Attachments

Issue Links

relates to

MDEV-11336 Enable defragmentation on 10.2 when tests pass

Closed

Activity

Ascending order - Click to sort in descending order

View 8 older comments

VAROQUI Stephane added a comment - 2017-03-07 15:34

By just commenting out , now it pass and uncommenting crash
#innodb_defragment = 1

VAROQUI Stephane added a comment - 2017-03-07 15:34 By just commenting out , now it pass and uncommenting crash #innodb_defragment = 1

Elena Stepanova added a comment - 2017-03-08 22:42

Test case
--source include/have_innodb.inc

set global innodb_defragment = 1;

CREATE TABLE t1 (
c TEXT DEFAULT NULL,
FULLTEXT KEY (c)
) ENGINE=InnoDB;

optimize table t1;

drop table t1`;
set global innodb_defragment = DEFAULT;

10.1 bb4ef470c24cdbcedba3dd3dcda3b3d88b6fb491
2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 4 name test/t1 key_id 0 encryption 0.
2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 5 name test/FTS_0000000000000012_DELETED key_id 0 encryption 0.
2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 6 name test/FTS_0000000000000012_DELETED_CACHE key_id 0 encryption 0.
2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 7 name test/FTS_0000000000000012_BEING_DELETED key_id 0 encryption 0.
2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 8 name test/FTS_0000000000000012_BEING_DELETED_CACHE key_id 0 encryption 0.
2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 9 name test/FTS_0000000000000012_CONFIG key_id 0 encryption 0.
2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 10 name test/FTS_0000000000000012_000000000000001b_INDEX_1 key_id 0 encryption 0.
2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 11 name test/FTS_0000000000000012_000000000000001b_INDEX_2 key_id 0 encryption 0.
2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 12 name test/FTS_0000000000000012_000000000000001b_INDEX_3 key_id 0 encryption 0.
2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 13 name test/FTS_0000000000000012_000000000000001b_INDEX_4 key_id 0 encryption 0.
2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 14 name test/FTS_0000000000000012_000000000000001b_INDEX_5 key_id 0 encryption 0.
2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 15 name test/FTS_0000000000000012_000000000000001b_INDEX_6 key_id 0 encryption 0.
InnoDB: Error: trying to access page number 4294967288 in space 4,
InnoDB: space name test/t1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2017-03-09 00:39:51 7f331358bb00 InnoDB: Assertion failure in thread 139857344641792 in file fil0fil.cc line 6080

#5  0x00007f32db72b448 in __GI_abort () at abort.c:89

#6  0x00007f32d4aa2fec in fil_io (type=10, sync=true, space_id=4, zip_size=0, block_offset=4294967288, byte_offset=0, len=16384, buf=0x7f32d274c000, message=0x7f32d23c5360, write_size=0x7f32d23c5380) at /data/src/10.1/storage/innobase/fil/fil0fil.cc:6080

#7  0x00007f32d4a4b52e in buf_read_page_low (err=0x7f32dee2f7bc, sync=true, mode=132, space=4, zip_size=0, unzip=0, tablespace_version=5, offset=4294967295, rbpage=0x7f32dee2f820) at /data/src/10.1/storage/innobase/buf/buf0rea.cc:199

#8  0x00007f32d4a4bb01 in buf_read_page (space=4, zip_size=0, offset=4294967295, bpage=0x7f32dee2f820) at /data/src/10.1/storage/innobase/buf/buf0rea.cc:424

#9  0x00007f32d4a2a5a5 in buf_page_get_gen (space=4, zip_size=0, offset=4294967295, rw_latch=3, guess=0x0, mode=10, file=0x7f32d4caac10 "/data/src/10.1/storage/innobase/btr/btr0defragment.cc", line=223, mtr=0x7f32dee2f980, err=0x7f32dee2f8f4) at /data/src/10.1/storage/innobase/buf/buf0buf.cc:2953

#10 0x00007f32d4a1a42c in btr_block_get_func (space=4, zip_size=0, page_no=4294967295, mode=3, file=0x7f32d4caac10 "/data/src/10.1/storage/innobase/btr/btr0defragment.cc", line=223, index=0x7f32c5a84d78, mtr=0x7f32dee2f980) at /data/src/10.1/storage/innobase/include/btr0btr.ic:60

#11 0x00007f32d4a1b4c8 in btr_defragment_add_index (index=0x7f32c5a84d78, async=false, err=0x7f32dee2fea8) at /data/src/10.1/storage/innobase/btr/btr0defragment.cc:223

#12 0x00007f32d4b05230 in ha_innodb::defragment_table (this=0x7f32c58b0888, name=0x7f32c5985058 "test/t1", index_name=0x0, async=false) at /data/src/10.1/storage/innobase/handler/ha_innodb.cc:12609

#13 0x00007f32d4b07611 in ha_innodb::optimize (this=0x7f32c58b0888, thd=0x7f32d1fa6070, check_opt=0x7f32d1faa8d0) at /data/src/10.1/storage/innobase/handler/ha_innodb.cc:13815

#14 0x00007f32de16dc53 in handler::ha_optimize (this=0x7f32c58b0888, thd=0x7f32d1fa6070, check_opt=0x7f32d1faa8d0) at /data/src/10.1/sql/handler.cc:4074

#15 0x00007f32de06fccb in mysql_admin_table(THD *, TABLE_LIST *, HA_CHECK_OPT *, const char *, thr_lock_type, bool, bool, uint, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *), struct {...}, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *)) (thd=0x7f32d1fa6070, tables=0x7f32c58fa158, check_opt=0x7f32d1faa8d0, operator_name=0x7f32de87af48 "optimize", lock_type=TL_WRITE, open_for_modify=true, repair_table_use_frm=false, extra_open_options=0, prepare_func=0x0, operator_func=(int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x7f32de16dbd2 <handler::ha_optimize(THD*, st_ha_check_opt*)>, view_operator_func=0x0) at /data/src/10.1/sql/sql_admin.cc:817

#16 0x00007f32de0719dc in Sql_cmd_optimize_table::execute (this=0x7f32c58fa750, thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_admin.cc:1308

#17 0x00007f32ddf359c8 in mysql_execute_command (thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_parse.cc:5695

#18 0x00007f32ddf39c89 in mysql_parse (thd=0x7f32d1fa6070, rawbuf=0x7f32c58fa088 "optimize table t1", length=17, parser_state=0x7f32dee315e0) at /data/src/10.1/sql/sql_parse.cc:7343

#19 0x00007f32ddf28c76 in dispatch_command (command=COM_QUERY, thd=0x7f32d1fa6070, packet=0x7f32d3e9b071 "", packet_length=17) at /data/src/10.1/sql/sql_parse.cc:1490

#20 0x00007f32ddf279d5 in do_command (thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_parse.cc:1109

#21 0x00007f32de05e37c in do_handle_one_connection (thd_arg=0x7f32d1fa6070) at /data/src/10.1/sql/sql_connect.cc:1349

#22 0x00007f32de05e0e0 in handle_one_connection (arg=0x7f32d1fa6070) at /data/src/10.1/sql/sql_connect.cc:1261

#23 0x00007f32de341478 in pfs_spawn_thread (arg=0x7f32d3eb8970) at /data/src/10.1/storage/perfschema/pfs.cc:1860

#24 0x00007f32dd6250a4 in start_thread (arg=0x7f32dee32b00) at pthread_create.c:309

#25 0x00007f32db7dd87d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

10.2 29a980cf5cca0f27477d55c86a31c2cf01e3b52b

2017-03-09  0:42:28 140270436340480 [ERROR] InnoDB: Trying to access page number 4294967295 in space 4, space name test/t1, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.

2017-03-09  0:42:28 140270436340480 [ERROR] InnoDB: Server exits at /data/src/10.2/storage/innobase/fil/fil0fil.cc[5039].

Elena Stepanova added a comment - 2017-03-08 22:42 Test case --source include/have_innodb.inc set global innodb_defragment = 1; CREATE TABLE t1 ( c TEXT DEFAULT NULL , FULLTEXT KEY (c) ) ENGINE=InnoDB; optimize table t1; drop table t1`; set global innodb_defragment = DEFAULT ; 10.1 bb4ef470c24cdbcedba3dd3dcda3b3d88b6fb491 2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 4 name test/t1 key_id 0 encryption 0. 2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 5 name test/FTS_0000000000000012_DELETED key_id 0 encryption 0. 2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 6 name test/FTS_0000000000000012_DELETED_CACHE key_id 0 encryption 0. 2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 7 name test/FTS_0000000000000012_BEING_DELETED key_id 0 encryption 0. 2017-03-09 0:39:49 139857344641792 [Note] InnoDB: Created tablespace for space 8 name test/FTS_0000000000000012_BEING_DELETED_CACHE key_id 0 encryption 0. 2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 9 name test/FTS_0000000000000012_CONFIG key_id 0 encryption 0. 2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 10 name test/FTS_0000000000000012_000000000000001b_INDEX_1 key_id 0 encryption 0. 2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 11 name test/FTS_0000000000000012_000000000000001b_INDEX_2 key_id 0 encryption 0. 2017-03-09 0:39:50 139857344641792 [Note] InnoDB: Created tablespace for space 12 name test/FTS_0000000000000012_000000000000001b_INDEX_3 key_id 0 encryption 0. 2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 13 name test/FTS_0000000000000012_000000000000001b_INDEX_4 key_id 0 encryption 0. 2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 14 name test/FTS_0000000000000012_000000000000001b_INDEX_5 key_id 0 encryption 0. 2017-03-09 0:39:51 139857344641792 [Note] InnoDB: Created tablespace for space 15 name test/FTS_0000000000000012_000000000000001b_INDEX_6 key_id 0 encryption 0. InnoDB: Error: trying to access page number 4294967288 in space 4, InnoDB: space name test/t1, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 2017-03-09 00:39:51 7f331358bb00 InnoDB: Assertion failure in thread 139857344641792 in file fil0fil.cc line 6080 #5 0x00007f32db72b448 in __GI_abort () at abort.c:89 #6 0x00007f32d4aa2fec in fil_io (type=10, sync=true, space_id=4, zip_size=0, block_offset=4294967288, byte_offset=0, len=16384, buf=0x7f32d274c000, message=0x7f32d23c5360, write_size=0x7f32d23c5380) at /data/src/10.1/storage/innobase/fil/fil0fil.cc:6080 #7 0x00007f32d4a4b52e in buf_read_page_low (err=0x7f32dee2f7bc, sync=true, mode=132, space=4, zip_size=0, unzip=0, tablespace_version=5, offset=4294967295, rbpage=0x7f32dee2f820) at /data/src/10.1/storage/innobase/buf/buf0rea.cc:199 #8 0x00007f32d4a4bb01 in buf_read_page (space=4, zip_size=0, offset=4294967295, bpage=0x7f32dee2f820) at /data/src/10.1/storage/innobase/buf/buf0rea.cc:424 #9 0x00007f32d4a2a5a5 in buf_page_get_gen (space=4, zip_size=0, offset=4294967295, rw_latch=3, guess=0x0, mode=10, file=0x7f32d4caac10 "/data/src/10.1/storage/innobase/btr/btr0defragment.cc", line=223, mtr=0x7f32dee2f980, err=0x7f32dee2f8f4) at /data/src/10.1/storage/innobase/buf/buf0buf.cc:2953 #10 0x00007f32d4a1a42c in btr_block_get_func (space=4, zip_size=0, page_no=4294967295, mode=3, file=0x7f32d4caac10 "/data/src/10.1/storage/innobase/btr/btr0defragment.cc", line=223, index=0x7f32c5a84d78, mtr=0x7f32dee2f980) at /data/src/10.1/storage/innobase/include/btr0btr.ic:60 #11 0x00007f32d4a1b4c8 in btr_defragment_add_index (index=0x7f32c5a84d78, async=false, err=0x7f32dee2fea8) at /data/src/10.1/storage/innobase/btr/btr0defragment.cc:223 #12 0x00007f32d4b05230 in ha_innodb::defragment_table (this=0x7f32c58b0888, name=0x7f32c5985058 "test/t1", index_name=0x0, async=false) at /data/src/10.1/storage/innobase/handler/ha_innodb.cc:12609 #13 0x00007f32d4b07611 in ha_innodb::optimize (this=0x7f32c58b0888, thd=0x7f32d1fa6070, check_opt=0x7f32d1faa8d0) at /data/src/10.1/storage/innobase/handler/ha_innodb.cc:13815 #14 0x00007f32de16dc53 in handler::ha_optimize (this=0x7f32c58b0888, thd=0x7f32d1fa6070, check_opt=0x7f32d1faa8d0) at /data/src/10.1/sql/handler.cc:4074 #15 0x00007f32de06fccb in mysql_admin_table(THD *, TABLE_LIST *, HA_CHECK_OPT *, const char *, thr_lock_type, bool, bool, uint, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *), struct {...}, int (*)(THD *, TABLE_LIST *, HA_CHECK_OPT *)) (thd=0x7f32d1fa6070, tables=0x7f32c58fa158, check_opt=0x7f32d1faa8d0, operator_name=0x7f32de87af48 "optimize", lock_type=TL_WRITE, open_for_modify=true, repair_table_use_frm=false, extra_open_options=0, prepare_func=0x0, operator_func=(int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x7f32de16dbd2 <handler::ha_optimize(THD*, st_ha_check_opt*)>, view_operator_func=0x0) at /data/src/10.1/sql/sql_admin.cc:817 #16 0x00007f32de0719dc in Sql_cmd_optimize_table::execute (this=0x7f32c58fa750, thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_admin.cc:1308 #17 0x00007f32ddf359c8 in mysql_execute_command (thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_parse.cc:5695 #18 0x00007f32ddf39c89 in mysql_parse (thd=0x7f32d1fa6070, rawbuf=0x7f32c58fa088 "optimize table t1", length=17, parser_state=0x7f32dee315e0) at /data/src/10.1/sql/sql_parse.cc:7343 #19 0x00007f32ddf28c76 in dispatch_command (command=COM_QUERY, thd=0x7f32d1fa6070, packet=0x7f32d3e9b071 "", packet_length=17) at /data/src/10.1/sql/sql_parse.cc:1490 #20 0x00007f32ddf279d5 in do_command (thd=0x7f32d1fa6070) at /data/src/10.1/sql/sql_parse.cc:1109 #21 0x00007f32de05e37c in do_handle_one_connection (thd_arg=0x7f32d1fa6070) at /data/src/10.1/sql/sql_connect.cc:1349 #22 0x00007f32de05e0e0 in handle_one_connection (arg=0x7f32d1fa6070) at /data/src/10.1/sql/sql_connect.cc:1261 #23 0x00007f32de341478 in pfs_spawn_thread (arg=0x7f32d3eb8970) at /data/src/10.1/storage/perfschema/pfs.cc:1860 #24 0x00007f32dd6250a4 in start_thread (arg=0x7f32dee32b00) at pthread_create.c:309 #25 0x00007f32db7dd87d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111 10.2 29a980cf5cca0f27477d55c86a31c2cf01e3b52b 2017-03-09 0:42:28 140270436340480 [ERROR] InnoDB: Trying to access page number 4294967295 in space 4, space name test/t1, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server. 2017-03-09 0:42:28 140270436340480 [ERROR] InnoDB: Server exits at /data/src/10.2/storage/innobase/fil/fil0fil.cc[5039].

Marko Mäkelä added a comment - 2017-04-04 06:46

elenst, thank you for the test case. I can repeat this in both 10.1 and 10.2. ~~MDEV-11336~~ only affects 10.2, so this is not a duplicate of that.

Marko Mäkelä added a comment - 2017-04-04 06:46 elenst , thank you for the test case. I can repeat this in both 10.1 and 10.2. MDEV-11336 only affects 10.2, so this is not a duplicate of that.

Marko Mäkelä added a comment - 2017-04-04 07:16

http://lists.askmonty.org/pipermail/commits/2017-April/010971.html

Marko Mäkelä added a comment - 2017-04-04 07:16 http://lists.askmonty.org/pipermail/commits/2017-April/010971.html

Jan Lindström (Inactive) added a comment - 2017-04-04 14:08

ok to push, please add spatial index test case for 10.2.

Jan Lindström (Inactive) added a comment - 2017-04-04 14:08 ok to push, please add spatial index test case for 10.2.

MariaDB Server

innodb_defragment=1 crashes server on OPTIMIZE TABLE when FULLTEXT index exists

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration