[MDEV-14081] ALTER TABLE CHANGE COLUMN Corrupts Index Leading to Crashes in 10.2 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.2.6, 10.2.9
Fix Version/s: 10.2.10
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
None
Environment:
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

Ascending order - Click to sort in descending order

Chris Calender (Inactive) created issue - 2017-10-16 22:33

Chris Calender (Inactive) made changes - 2017-10-16 22:35

Field	Original Value	New Value
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: {code} 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'; {code} 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: {code} 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'; {code} Here is the error log stack trace: {code} 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.) {code}	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: {code} 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'; {code} {code} MariaDB [crashtest]> SELECT * FROM `t1` where id4 like 'a'; ERROR 2013 (HY000): Lost connection to MySQL server during query {code} 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: {code} 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'; {code} Here is the error log stack trace: {code} 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.) {code}

Elena Stepanova made changes - 2017-10-17 06:23

Fix Version/s

10.2 [ 14601 ]

Elena Stepanova added a comment - 2017-10-17 06:37

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

Elena Stepanova added a comment - 2017-10-17 06:37 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

Elena Stepanova made changes - 2017-10-17 07:12

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2017-10-17 07:12

Assignee

Marko Mäkelä [ marko ]

Elena Stepanova added a comment - 2017-10-17 07:12

10.1 is not affected

Elena Stepanova added a comment - 2017-10-17 07:12 10.1 is not affected

Marko Mäkelä made changes - 2017-10-17 11:53

Affects Version/s

10.2.6 [ 22527 ]

Elena Stepanova added a comment - 2017-10-17 12:00

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';

Elena Stepanova added a comment - 2017-10-17 12:00 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' ;

Marko Mäkelä added a comment - 2017-10-17 12:39

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.

Marko Mäkelä added a comment - 2017-10-17 12:39 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.

Marko Mäkelä made changes - 2017-10-17 12:39

Assignee

Marko Mäkelä [ marko ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2017-10-17 22:12

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2017-10-18 06:16

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2017-10-19 08:11

Component/s		Data Definition - Alter Table [ 10114 ]
Component/s		Storage Engine - InnoDB [ 10129 ]
Fix Version/s		10.2.10 [ 22615 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 83058 ]

MariaDB v4 [ 152997 ]

Jira Automation (IT) made changes - 2024-07-04 08:10

Zendesk Related Tickets

105719

People

Assignee:: Sergei Golubchik

Reporter:: Chris Calender (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-10-16 22:33

Updated:: 2024-07-08 00:37

Resolved:: 2017-10-19 08:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration