[MDEV-12198] innodb_defragment=1 crashes server on OPTIMIZE TABLE when FULLTEXT index exists Created: 2017-03-07  Updated: 2017-04-06  Resolved: 2017-04-06

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1, 10.2.4, 10.2
Fix Version/s: 10.1.23, 10.2.6

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
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

Issue Links:
Relates
relates to MDEV-11336 Enable defragmentation on 10.2 when t... Closed
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)



 Comments   
Comment by VAROQUI Stephane [ 2017-03-07 ]

First intresting founding my own server is on 10.2.3,

And it report to me on DDL ,

Specified key was too long; max key length is 767 bytes

Comment by VAROQUI Stephane [ 2017-03-07 ]

verified on fresh install of 10.2.3 fixing the DDL to use varchar(100) optimize stop the server

Comment by VAROQUI Stephane [ 2017-03-07 ]

conf used
[mysqld]
default_storage_engine = InnoDB
innodb_force_primary_key = 1
innodb_adaptive_hash_index = 0
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table=1
innodb_write_io_threads = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
innodb_max_dirty_pages_pct = 10
innodb_flush_neighbors = 1
innodb_doublewrite = 1
innodb_log_file_size = 1024M
innodb_log_buffer_size = 256M
innodb_log_files_in_group = 4
innodb_thread_concurrency = 0
innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

innodb_open_files = 16384
innodb_purge_threads = 8

innodb_change_buffering=none
innodb_concurrency_tickets =5000

Comment by VAROQUI Stephane [ 2017-03-07 ]

Can not reproduce on a fresh install on 10.2.4 with all default package config

Comment by VAROQUI Stephane [ 2017-03-07 ]

When first failed we have been using innodb_defragment, his may have some impact during the previous failure , my plan is to first reinstall the old config file and check if reproducible with new config

Comment by VAROQUI Stephane [ 2017-03-07 ]

can reproduce by applying the innodb conf above +extra optimizer conf
join_buffer_space_limit = 1G
join_buffer_size = 128M
join_cache_level = 6
mrr_buffer_size = 96M

optimizer_switch='orderby_uses_equalities=on'
optimizer_switch ='extended_keys=off'
optimizer_switch ='index_condition_pushdown=on'
optimizer_switch ='engine_condition_pushdown=on'
optimizer_switch ='derived_merge=on'
optimizer_switch ='derived_with_keys=on'
optimizer_switch ='firstmatch=off'
optimizer_switch ='loosescan=off'
optimizer_switch ='materialization=on'
optimizer_switch ='in_to_exists=on'
optimizer_switch ='semijoin=on'
optimizer_switch ='partial_match_rowid_merge=on'
optimizer_switch ='partial_match_table_scan=on'
optimizer_switch ='subquery_cache=off'
optimizer_switch ='mrr=on'
optimizer_switch ='mrr_cost_based=off'
optimizer_switch ='mrr_sort_keys=on'
optimizer_switch ='outer_join_with_cache=on'
optimizer_switch ='semijoin_with_cache=off'
optimizer_switch ='join_cache_incremental=on'
optimizer_switch ='join_cache_hashed=on'
optimizer_switch ='join_cache_bka=on'
optimizer_switch ='optimize_join_buffer_size=on'
optimizer_switch ='table_elimination=on'

Comment by VAROQUI Stephane [ 2017-03-07 ]

ar 7 16:24:10 appli01 mysqld[22641]: 2017-03-07 16:24:10 119304612389632 [ERROR] InnoDB: Trying to access page number 4294967295 in space 28, space name test/layout, 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.
Mar 7 16:24:10 appli01 mysqld[22641]: 2017-03-07 16:24:10 119304612389632 [ERROR] InnoDB: Server exits.
Mar 7 16:24:11 appli01 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE

Comment by VAROQUI Stephane [ 2017-03-07 ]

sorry i have extra
innodb_defragment = 1
innodb_force_primary_key = 1

Comment by VAROQUI Stephane [ 2017-03-07 ]

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

Comment by Elena Stepanova [ 2017-03-08 ]

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].

Comment by Marko Mäkelä [ 2017-04-04 ]

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.

Comment by Marko Mäkelä [ 2017-04-04 ]

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

Comment by Jan Lindström (Inactive) [ 2017-04-04 ]

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

Generated at Thu Feb 08 07:55:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.