Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.6, 10.2.9
-
None
-
Windows, Linux
Description
ALTER TABLE CHANGE COLUMN Corrupts Index Leading to Crashes in 10.2
Run the following test in 10.2.9 (tested 10.2.6 through 10.2.9) and it will crash:
CREATE DATABASE IF NOT EXISTS `crashtest`;
|
USE `crashtest`;
|
drop table if exists t1;
|
CREATE TABLE `t1` (
|
`id1` INT(11) NOT NULL AUTO_INCREMENT,
|
`id2` VARCHAR(30) NOT NULL,
|
`id3` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`id1`),
|
UNIQUE KEY `unique_id2` (`id2`)
|
);
|
ALTER TABLE `t1` CHANGE COLUMN `id2` `id4` VARCHAR(100) NOT NULL;
|
SELECT * FROM `t1` where id4 like 'a';
|
MariaDB [crashtest]> SELECT * FROM `t1` where id4 like 'a';
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
This does not crash in 10.1.28.
Note that If I remove id3 (the column after id2 - the one being changed), then it does not crash:
CREATE DATABASE IF NOT EXISTS `crashtest`;
|
USE `crashtest`;
|
drop table if exists t1;
|
CREATE TABLE `t1` (
|
`id1` INT(11) NOT NULL AUTO_INCREMENT,
|
`id2` VARCHAR(30) NOT NULL,
|
PRIMARY KEY (`id1`),
|
UNIQUE KEY `unique_id2` (`id2`)
|
);
|
ALTER TABLE `t1` CHANGE COLUMN `id2` `id4` VARCHAR(100) NOT NULL;
|
SELECT * FROM `t1` where id4 like 'a';
|
Here is the error log stack trace:
2017-10-16 18:25:48 25472 [Note] C:\Program Files\MariaDB 10.2\bin\mysqld.exe: ready for connections.
|
Version: '10.2.9-MariaDB' socket: '' port: 3317 mariadb.org binary distribution
|
2017-10-16 18:25:48 35452 [Note] InnoDB: Buffer pool(s) load completed at 171016 18:25:48
|
2017-10-16 18:26:47 29244 [ERROR] Found index unique_id2 whose column info does not match that of MariaDB.
|
2017-10-16 18:26:47 29244 [ERROR] Build InnoDB index translation table for Table .\crashtest\t1 failed
|
171016 18:26:47 [ERROR] mysqld got exception 0xc0000005 ;
|
This could be because you hit a bug. It is also possible that this binary
|
or one of the libraries it was linked against is corrupt, improperly built,
|
or misconfigured. This error can also be caused by malfunctioning hardware.
|
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
|
Server version: 10.2.9-MariaDB
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=1
|
max_threads=65537
|
thread_count=7
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 136039 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x11f13258
|
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went
|
terribly wrong...
|
mysqld.exe!my_xml_error_string()
|
mysqld.exe!?get_tok_start_prev@Lex_input_stream@@QEAAPEBDXZ()
|
mysqld.exe!?ha_index_read_map@handler@@QEAAHPEAEPEBEKW4ha_rkey_function@@@Z()
|
mysqld.exe!?read_range_first@handler@@UEAAHPEBUst_key_range@@0_N1@Z()
|
mysqld.exe!?multi_range_read_next@handler@@UEAAHPEAPEAX@Z()
|
mysqld.exe!?get_next@Mrr_simple_index_reader@@UEAAHPEAPEAX@Z()
|
mysqld.exe!?dsmrr_next@DsMrr_impl@@QEAAHPEAPEAX@Z()
|
mysqld.exe!?get_next@QUICK_RANGE_SELECT@@UEAAHXZ()
|
mysqld.exe!?rr_from_pointers@@YAHPEAUREAD_RECORD@@@Z()
|
mysqld.exe!?sub_select@@YA?AW4enum_nested_loop_state@@PEAVJOIN@@PEAUst_join_table@@_N@Z()
|
mysqld.exe!?disjoin@?$List@VItem@@@@QEAAXPEAV1@@Z()
|
mysqld.exe!?exec_inner@JOIN@@QEAAXXZ()
|
mysqld.exe!?exec@JOIN@@QEAAXXZ()
|
mysqld.exe!?mysql_select@@YA_NPEAVTHD@@PEAUTABLE_LIST@@IAEAV?$List@VItem@@@@PEAVItem@@IPEAUst_order@@434_KPEAVselect_result@@PEAVst_select_lex_unit@@PEAVst_select_lex@@@Z()
|
mysqld.exe!?handle_select@@YA_NPEAVTHD@@PEAULEX@@PEAVselect_result@@K@Z()
|
mysqld.exe!?execute_init_command@@YAXPEAVTHD@@PEAUst_mysql_lex_string@@PEAUst_mysql_rwlock@@@Z()
|
mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@@Z()
|
mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEADIPEAVParser_state@@_N3@Z()
|
mysqld.exe!?dispatch_command@@YA_NW4enum_server_command@@PEAVTHD@@PEADI_N3@Z()
|
mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
|
mysqld.exe!?pool_of_threads_scheduler@@YAXPEAUscheduler_functions@@PEAKPEAI@Z()
|
mysqld.exe!?tp_callback@@YAXPEAUTP_connection@@@Z()
|
ntdll.dll!TpPostWork()
|
ntdll.dll!TpDisassociateCallback()
|
kernel32.dll!BaseThreadInitThunk()
|
ntdll.dll!RtlUserThreadStart()
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x11f1fc40): SELECT * FROM `t1` where id4 like 'a'
|
Connection ID (thread ID): 9
|
Status: NOT_KILLED
|
|
|
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
|
|
|
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
|
information that should help you find out what is causing the crash.
|
2017-10-16 18:27:04 26064 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5733ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
|
Attachments
Activity
|
MTR-friendly test case |
--source include/have_innodb.inc
|
|
|
CREATE TABLE `t1` ( |
`id1` INT(11) NOT NULL AUTO_INCREMENT, |
`id2` VARCHAR(30) NOT NULL, |
`id3` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, |
PRIMARY KEY (`id1`), |
UNIQUE KEY `unique_id2` (`id2`) |
) ENGINE=InnoDB;
|
ALTER TABLE `t1` CHANGE COLUMN `id2` `id4` VARCHAR(100) NOT NULL; |
SELECT * FROM `t1` where id4 like 'a'; |
I can repeat this with the following:
diff --git a/mysql-test/suite/innodb/t/alter_table.test b/mysql-test/suite/innodb/t/alter_table.test
|
index 13fb574972a..9658f61dd7b 100644
|
--- a/mysql-test/suite/innodb/t/alter_table.test
|
+++ b/mysql-test/suite/innodb/t/alter_table.test
|
@@ -8,3 +8,15 @@ create index idx1 on t1(a(3073));
|
show create table t1;
|
drop table t1;
|
set @@sql_mode=default;
|
+
|
+#
|
+# MDEV-14081 ALTER TABLE CHANGE COLUMN Corrupts Index Metadata
|
+#
|
+
|
+CREATE TABLE t1 (
|
+ id1 INT AUTO_INCREMENT PRIMARY KEY, id2 VARCHAR(30) NOT NULL UNIQUE,
|
+ id3 DATETIME
|
+) ENGINE=InnoDB;
|
+ALTER TABLE t1 CHANGE COLUMN id2 id4 VARCHAR(100) NOT NULL;
|
+SELECT * FROM t1 WHERE id4 LIKE 'a';
|
+DROP TABLE t1; |
I also tried to replace the VARCHAR(100) to VARCHAR(40), so that we would be extending the maximum length of the column from 30 to 40 characters, instead of from 30 to 100 characters. (Starting with MySQL 5.7.0, InnoDB supports "instant extend VARCHAR" if the maximum length does not cross the 255-byte boundary. If the table used UTF-8, extending from 30 to 100 characters would cross the boundary, because 3*30<255 and 4*30<255, but 3*100 or 4*100 would be more than 255.)
As far as I can tell, InnoDB is given wrong parameters, leading it to think that a new index must be created.
Thread 28 "mysqld" hit Breakpoint 1, ha_innobase::prepare_inplace_alter_table
|
(this=0x61c00005f8d0, altered_table=0x614000001050,
|
ha_alter_info=0x1c6900038040)
|
at /mariadb/10.2/storage/innobase/handler/handler0alter.cc:5501
|
1: m_user_thd.query_string = {string = {
|
str = 0x610000008660 "ALTER TABLE t1 CHANGE COLUMN id2 id4 VARCHAR(40) NOT NULL", length = 57}, cs = 0x429d480 <my_charset_latin1>}
|
2: /x ha_alter_info->handler_flags = 0x8480c
|
3: ha_alter_info->index_add_count = 1
|
Why is index_add_count not 0? Why are all these flags set, instead of only setting ALTER_CHANGE_COLUMN (1L<<2, or 0x8)?
0x8480c == ALTER_ALL_PARTITION|ALTER_REORGANIZE_PARTITION|ALTER_ADD_PARTITION|ALTER_ADD_INDEX|ALTER_CHANGE_COLUMN
|
From the InnoDB point of view, it goes "garbage in, garbage out" from this point on, and InnoDB cannot be blamed. serg, can you please fix the SQL layer first, and then I can check if InnoDB really is doing something wrong.
10.2 ad46ce658ac9d4d
#3 <signal handler called>#4 0x000055c4b4e8835c in row_sel_convert_mysql_key_to_innobase (tuple=0x7f0bc804da68, buf=0x7f0bc804d874 "", buf_len=4, index=0x7f0bc8060038, key_ptr=0x7f0bc8052fcc ' ' <repeats 98 times>, "\245\245\001", key_len=102, trx=0x7f0c14955888) at /home/elenst/git/10.2/storage/innobase/row/row0sel.cc:2594#5 0x000055c4b4d173fd in ha_innobase::index_read (this=0x7f0bc8046b28, buf=0x7f0bc804ad28 "", key_ptr=0x7f0bc8052fc8 "\001", key_len=102, find_flag=HA_READ_KEY_EXACT) at /home/elenst/git/10.2/storage/innobase/handler/ha_innodb.cc:9783#6 0x000055c4b4b0e41c in handler::index_read_map (this=0x7f0bc8046b28, buf=0x7f0bc804ad28 "", key=0x7f0bc8052fc8 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /home/elenst/git/10.2/sql/handler.h:3116#7 0x000055c4b4b047d1 in handler::ha_index_read_map (this=0x7f0bc8046b28, buf=0x7f0bc804ad28 "", key=0x7f0bc8052fc8 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /home/elenst/git/10.2/sql/handler.cc:2626#8 0x000055c4b4b0b216 in handler::read_range_first (this=0x7f0bc8046b28, start_key=0x7f0bc8046c10, end_key=0x7f0bc8046c30, eq_range_arg=true, sorted=true) at /home/elenst/git/10.2/sql/handler.cc:5332#9 0x000055c4b4a2292f in handler::multi_range_read_next (this=0x7f0bc8046b28, range_info=0x7f0c1404fd60) at /home/elenst/git/10.2/sql/multi_range_read.cc:290#10 0x000055c4b4a22a65 in Mrr_simple_index_reader::get_next (this=0x7f0bc8047098, range_info=0x7f0c1404fd60) at /home/elenst/git/10.2/sql/multi_range_read.cc:322#11 0x000055c4b4a25596 in DsMrr_impl::dsmrr_next (this=0x7f0bc8046f58, range_info=0x7f0c1404fd60) at /home/elenst/git/10.2/sql/multi_range_read.cc:1398#12 0x000055c4b4d2da7e in ha_innobase::multi_range_read_next (this=0x7f0bc8046b28, range_info=0x7f0c1404fd60) at /home/elenst/git/10.2/storage/innobase/handler/ha_innodb.cc:21950#13 0x000055c4b4c5d4ce in QUICK_RANGE_SELECT::get_next (this=0x7f0bc8049830) at /home/elenst/git/10.2/sql/opt_range.cc:11388#14 0x000055c4b4c6e5c2 in rr_quick (info=0x7f0bc8014e00) at /home/elenst/git/10.2/sql/records.cc:366#15 0x000055c4b4900d02 in join_init_read_record (tab=0x7f0bc8014d38) at /home/elenst/git/10.2/sql/sql_select.cc:19485#16 0x000055c4b48feae7 in sub_select (join=0x7f0bc80130c8, join_tab=0x7f0bc8014d38, end_of_records=false) at /home/elenst/git/10.2/sql/sql_select.cc:18560#17 0x000055c4b48fe0e4 in do_select (join=0x7f0bc80130c8, procedure=0x0) at /home/elenst/git/10.2/sql/sql_select.cc:18107#18 0x000055c4b48d8dc1 in JOIN::exec_inner (this=0x7f0bc80130c8) at /home/elenst/git/10.2/sql/sql_select.cc:3483#19 0x000055c4b48d8272 in JOIN::exec (this=0x7f0bc80130c8) at /home/elenst/git/10.2/sql/sql_select.cc:3278#20 0x000055c4b48d9432 in mysql_select (thd=0x7f0bc8000b00, tables=0x7f0bc8012648, wild_num=1, fields=..., conds=0x7f0bc8012ea0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0bc80130a8, unit=0x7f0bc80046a0, select_lex=0x7f0bc8004dd8) at /home/elenst/git/10.2/sql/sql_select.cc:3678#21 0x000055c4b48cddd3 in handle_select (thd=0x7f0bc8000b00, lex=0x7f0bc80045d8, result=0x7f0bc80130a8, setup_tables_done_option=0) at /home/elenst/git/10.2/sql/sql_select.cc:373#22 0x000055c4b4899b14 in execute_sqlcom_select (thd=0x7f0bc8000b00, all_tables=0x7f0bc8012648) at /home/elenst/git/10.2/sql/sql_parse.cc:6427#23 0x000055c4b488f861 in mysql_execute_command (thd=0x7f0bc8000b00) at /home/elenst/git/10.2/sql/sql_parse.cc:3461#24 0x000055c4b489d48d in mysql_parse (thd=0x7f0bc8000b00, rawbuf=0x7f0bc8012438 "SELECT * FROM `t1` where id4 like 'a'", length=37, parser_state=0x7f0c14051200, is_com_multi=false, is_next_command=false) at /home/elenst/git/10.2/sql/sql_parse.cc:7868#25 0x000055c4b488b14c in dispatch_command (command=COM_QUERY, thd=0x7f0bc8000b00, packet=0x7f0bc8085041 "", packet_length=37, is_com_multi=false, is_next_command=false) at /home/elenst/git/10.2/sql/sql_parse.cc:1812#26 0x000055c4b4889abb in do_command (thd=0x7f0bc8000b00) at /home/elenst/git/10.2/sql/sql_parse.cc:1360#27 0x000055c4b49d6a64 in do_handle_one_connection (connect=0x55c4b8bf17d0) at /home/elenst/git/10.2/sql/sql_connect.cc:1354#28 0x000055c4b49d67e4 in handle_one_connection (arg=0x55c4b8bf17d0) at /home/elenst/git/10.2/sql/sql_connect.cc:1260#29 0x000055c4b5212f94 in pfs_spawn_thread (arg=0x55c4b8b3c9a0) at /home/elenst/git/10.2/storage/perfschema/pfs.cc:1862#30 0x00007f0c1b62a6ba in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0#31 0x00007f0c1aabf82d in clone () from /lib/x86_64-linux-gnu/libc.so.6