[MDEV-27374] InnoDB table becomes corrupt after renaming DESC-indexed column Created: 2021-12-27  Updated: 2022-01-26  Resolved: 2021-12-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: 10.8.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-13756 Implement descending index: KEY (a DE... Closed
Relates
relates to MDEV-27432 ASC/DESC primary and unique keys caus... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t (pk INT PRIMARY KEY, a VARCHAR(8), KEY(a DESC)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1,'foo'),(2,'bar');
 
ALTER TABLE t RENAME COLUMN a TO b, ALGORITHM=INPLACE; # Also NOCOPY, INSTANT
 
 
 
--source include/restart_mysqld.inc
 
 
 
CHECK TABLE t;
 
 
 
# Cleanup
 
DROP TABLE t;

preview-10.8-MDEV-13756-desc-indexes 013faaee non-debug

 
CHECK TABLE t;
 
Table	Op	Msg_type	Msg_text
 
test.t	check	Warning	InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
test.t	check	Warning	InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
test.t	check	Warning	InnoDB: The B-tree of index PRIMARY is corrupted.
 
test.t	check	error	Corrupt


2021-12-27 17:49:07 3 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
 
 
 
2021-12-27 17:49:07 3 [ERROR] InnoDB could not find key no 1 with name a from dict cache for table test/t
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
 
 
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Record offset out of bounds: 16250+278
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Record offset out of bounds: 16277+278
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Summed data size 556, returned by func 54
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: Apparent corruption in space 5 page 3 of index `PRIMARY` of table `test`.`t`
 
2021-12-27 17:49:07 3 [ERROR] InnoDB: In page 3 of index `PRIMARY` of table `test`.`t`

preview-10.8-MDEV-13756-desc-indexes 013faaee debug

 
2021-12-27 17:50:26 0 [ERROR] InnoDB: No matching column for `a` in index `a` of table `test`.`t`
 
mariadbd: /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/rem/rem0rec.cc:619: void rec_init_offsets(const rec_t *, const dict_index_t *, ulint, rec_offs *): Assertion `index->n_core_null_bytes <= (((index->n_nullable)
 
 + 7) >> 3) || index->in_instant_init' failed.
 
211227 17:50:26 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f6abfeabf36 in __GI___assert_fail (assertion=0x3c1d240 <str> "index->n_core_null_bytes <= (((index->n_nullable) + 7) >> 3) || index->in_instant_init", file=0x3c18e20 <str> "/data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/rem/rem0rec.cc", line=619, function=0x3c1d2c0 <__PRETTY_FUNCTION__._ZL16rec_init_offsetsPKhPK12dict_index_tmPt> "void rec_init_offsets(const rec_t *, const dict_index_t *, ulint, rec_offs *)") at assert.c:101
 
#8  0x0000000002933018 in rec_init_offsets (rec=0x7f6ab60d807f "\200", index=0x616000318120, n_core=4, offsets=0x7f6ab13a2440) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/rem/rem0rec.cc:618
 
#9  0x000000000292fb32 in rec_get_offsets_func (rec=0x7f6ab60d807f "\200", index=0x616000318120, offsets=0x7f6ab13a2440, n_core=4, n_fields=1, file=0x3befaa0 <str> "/data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/page/page0cur.cc", line=453, heap=0x7f6ab13a2420) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/rem/rem0rec.cc:948
 
#10 0x0000000002885896 in page_cur_search_with_match (block=0x7f6ab5c11630, index=0x616000318120, tuple=0x61d00011fe30, mode=PAGE_CUR_LE, iup_matched_fields=0x7f6ab13a2f80, ilow_matched_fields=0x7f6ab13a2fc0, cursor=0x61f000001070, rtr_info=0x0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/page/page0cur.cc:451
 
#11 0x0000000002c795b8 in btr_cur_search_to_nth_level_func (index=0x616000318120, level=0, tuple=0x61d00011fe30, mode=PAGE_CUR_LE, latch_mode=2, cursor=0x61f000001068, ahi_latch=0x0, mtr=0x7f6ab13a5ce0, autoinc=0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/btr/btr0cur.cc:1973
 
#12 0x0000000002a9a6f7 in btr_pcur_open_low (index=0x616000318120, level=0, tuple=0x61d00011fe30, mode=PAGE_CUR_LE, latch_mode=2, cursor=0x61f000001068, autoinc=0, mtr=0x7f6ab13a5ce0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/include/btr0pcur.ic:369
 
#13 0x0000000002a99d6d in row_search_on_row_ref (pcur=0x61f000001068, mode=2, table=0x618000020120, ref=0x61d00011fe30, mtr=0x7f6ab13a5ce0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0row.cc:1215
 
#14 0x0000000002a7b761 in row_purge_reposition_pcur (mode=2, node=0x61f000000fc8, mtr=0x7f6ab13a5ce0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0purge.cc:81
 
#15 0x0000000002a80ecf in row_purge_reset_trx_id (node=0x61f000000fc8, mtr=0x7f6ab13a5ce0) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0purge.cc:745
 
#16 0x0000000002a7fc81 in row_purge_record_func (node=0x61f000000fc8, undo_rec=0x6250000502f8 "", thr=0x61f000000f20, updated_extern=false) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0purge.cc:1165
 
#17 0x0000000002a7c247 in row_purge (node=0x61f000000fc8, undo_rec=0x6250000502f8 "", thr=0x61f000000f20) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0purge.cc:1209
 
#18 0x0000000002a7bfb4 in row_purge_step (thr=0x61f000000f20) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/row/row0purge.cc:1258
 
#19 0x000000000290c04f in que_thr_step (thr=0x61f000000f20) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/que/que0que.cc:653
 
#20 0x000000000290abfb in que_run_threads_low (thr=0x61f000000f20) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/que/que0que.cc:709
 
#21 0x000000000290a7e7 in que_run_threads (thr=0x61f000000f20) at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/que/que0que.cc:729
 
#22 0x0000000002b2a45b in srv_task_execute () at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/srv/srv0srv.cc:1663
 
#23 0x0000000002b25e4e in purge_worker_callback () at /data/src/preview-10.8-MDEV-13756-desc-indexes/storage/innobase/srv/srv0srv.cc:1907
 
#24 0x0000000003022349 in tpool::task_group::execute (this=0x621e660 <purge_task_group>, t=0x621e720 <purge_worker_task>) at /data/src/preview-10.8-MDEV-13756-desc-indexes/tpool/task_group.cc:55
 
#25 0x00000000030231aa in tpool::task::execute (this=0x621e720 <purge_worker_task>) at /data/src/preview-10.8-MDEV-13756-desc-indexes/tpool/task.cc:32
 
#26 0x000000000300ab2f in tpool::thread_pool_generic::worker_main (this=0x618000001080, thread_var=0x63000002fa00) at /data/src/preview-10.8-MDEV-13756-desc-indexes/tpool/tpool_generic.cc:549
 
#27 0x000000000301b277 in std::__invoke_impl<void, void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> (__f=@0x604000004168: (void (tpool::thread_pool_generic::*)(tpool::thread_pool_generic * const, tpool::worker_data *)) 0x300a8f0 <tpool::thread_pool_generic::worker_main(tpool::worker_data*)>, __t=@0x604000004160: 0x618000001080, __args=@0x604000004158: 0x63000002fa00) at /usr/bin/../lib/gcc/x86_64-linux-gnu/9/../../../../include/c++/9/bits/invoke.h:73
 
#28 0x000000000301b0b7 in std::__invoke<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> (__fn=@0x604000004168: (void (tpool::thread_pool_generic::*)(tpool::thread_pool_generic * const, tpool::worker_data *)) 0x300a8f0 <tpool::thread_pool_generic::worker_main(tpool::worker_data*)>, __args=@0x604000004158: 0x63000002fa00, __args=@0x604000004158: 0x63000002fa00) at /usr/bin/../lib/gcc/x86_64-linux-gnu/9/../../../../include/c++/9/bits/invoke.h:95
 
#29 0x000000000301b05b in std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> >::_M_invoke<0ul, 1ul, 2ul> (this=0x604000004158) at /usr/bin/../lib/gcc/x86_64-linux-gnu/9/../../../../include/c++/9/thread:244
 
#30 0x000000000301aff5 in std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> >::operator() (this=0x604000004158) at /usr/bin/../lib/gcc/x86_64-linux-gnu/9/../../../../include/c++/9/thread:251
 
#31 0x000000000301abd9 in std::thread::_State_impl<std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> > >::_M_run (this=0x604000004150) at /usr/bin/../lib/gcc/x86_64-linux-gnu/9/../../../../include/c++/9/thread:195
 
#32 0x00007f6ac0163de4 in ?? () from /lib/x86_64-linux-gnu/libstdc++.so.6
 
#33 0x00007f6ac0280609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#34 0x00007f6abff97293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

No obvious problem with MyISAM.
Not reproducible with ALGORITHM=COPY.
Not reproducible without DESC index.

In the original test, the same assertion failure happened upon a query rather than upon purge.

mysqld: /data/src/preview-10.8-MDEV-13756-desc-indexes-gcov/storage/innobase/rem/rem0rec.cc:618: void rec_init_offsets(const rec_t*, const dict_index_t*, ulint, rec_offs*): Assertion `index->n_core_null_bytes <= (((index->n_nullable) + 7) >> 3) || index->in_instant_init' failed.
 
211227  6:53:32 [ERROR] mysqld got signal 6 ;
 
 
 
??:0(__assert_fail)[0x7fad62fa0f36]
 
rem/rem0rec.cc:620(rec_init_offsets(unsigned char const*, dict_index_t const*, unsigned long, unsigned short*))[0x5583d0b98ef2]
 
rem/rem0rec.cc:950(rec_get_offsets_func(unsigned char const*, dict_index_t const*, unsigned short*, unsigned long, unsigned long, char const*, unsigned in
 
t, mem_block_info_t**))[0x5583d0b9b8a8]
 
row/row0sel.cc:4994(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x5583d0cd2983]
 
handler/ha_innodb.cc:9008(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x5583d08e9998]
 
handler/ha_innodb.cc:9369(ha_innobase::index_first(unsigned char*))[0x5583d08eb86c]
 
sql/handler.cc:3544(handler::ha_index_first(unsigned char*))[0x5583d01bf15b]
 
sql/sql_select.cc:22108(join_read_first(st_join_table*))[0x5583cfbe6187]
 
sql/sql_select.cc:21067(sub_select(JOIN*, st_join_table*, bool))[0x5583cfbe14bf]
 
sql/sql_select.cc:20617(do_select(JOIN*, Procedure*))[0x5583cfbdfd1f]
 
sql/sql_select.cc:4735(JOIN::exec_inner())[0x5583cfb8f0af]
 
sql/sql_select.cc:4513(JOIN::exec())[0x5583cfb8d3d3]
 
sql/sql_select.cc:4993(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5583cfb90647]
 
sql/sql_select.cc:545(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5583cfb7073e]
 
sql/sql_parse.cc:6253(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5583cfac33f7]
 
sql/sql_parse.cc:3944(mysql_execute_command(THD*, bool))[0x5583cfaaf6e1]
 
sql/sql_parse.cc:8028(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5583cfaccaed]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5583cfaa2dc0]
 
sql/sql_parse.cc:1402(do_command(THD*, bool))[0x5583cfa9fe56]
 
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x5583cfe12583]
 
sql/sql_connect.cc:1312(handle_one_connection)[0x5583cfe11d4b]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5583d07302e5]
 
nptl/pthread_create.c:478(start_thread)[0x7fad634b9609]
 
??:0(clone)[0x7fad6308c293]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7facf0114860): SELECT /*!40001 SQL_NO_CACHE */ `v`, `col_bigint_unsigned`, `col_varchar_257_latin1`, `col_timestamp`, `col_varchar_257_utf8`, `col_tinyint_unsigned`, `pk`, `col_bigint` FROM `table10_innodb_int` ORDER BY `pk`



 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2021-12-28 ] 

CREATE TABLE t (pk INT PRIMARY KEY, a VARCHAR(8), KEY(a DESC)) ENGINE=InnoDB;
 
ALTER TABLE t RENAME COLUMN a TO b, ALGORITHM=inplace;
 
show create table t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `pk` int(11) NOT NULL,
 
  `b` varchar(8) DEFAULT NULL,
 
  PRIMARY KEY (`pk`),
 
  KEY `a` (`b` DESC)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
select * from information_schema.innodb_sys_fields;
 
INDEX_ID	NAME	POS
 
11	ID	0
 
12	FOR_NAME	0
 
13	REF_NAME	0
 
14	ID	0
 
14	POS	1
 
15	TABLE_ID	0
 
15	POS	1
 
15	BASE_POS	2
 
16	database_name	0
 
16	table_name	1
 
17	database_name	0
 
17	table_name	1
 
17	index_name	2
 
17	stat_name	3
 
18	transaction_id	0
 
19	commit_id	0
 
20	begin_timestamp	0
 
21	commit_timestamp	0
 
21	transaction_id	1
 
23	pk	0
 
24	a	0
 
DROP TABLE t;

InnoDB fails to rename the column in "sys_fields" table. InnoDB hits innobase_rename_column_try() and does rename the column. Need to debug more to
find out where it fails to rename

Comment by Thirunarayanan Balathandayuthapani [ 2021-12-28 ]

The following patch fixes the issue:

diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
 
index 48affbb9036..35bb0a93ee1 100644
 
--- a/storage/innobase/handler/handler0alter.cc
 
+++ b/storage/innobase/handler/handler0alter.cc
 
@@ -8979,6 +8979,7 @@ innobase_rename_column_try(
 
                        pars_info_t* info = pars_info_create();
 
                        ulint pos = has_prefixes ? i << 16 | f.prefix_len : i;
 
 
+                       pos |= !!f.descending << 15;
 
                        pars_info_add_ull_literal(info, "indexid", index->id);
 
                        pars_info_add_int4_literal(info, "nth", pos);
 
                        pars_info_add_str_literal(info, "new", to);

Position field stores the descending flag too in this branch. But during rename of column, InnoDB fails to include
the descending flag for the position. The above patch does that. I think I can push the patch into preview-10.8-MDEV-13756-desc-indexes
without any approval

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