MySQL Bugs: #13293: SELECT query goes in an infinite loop after an ALTER TABLE

Bug #13293	SELECT query goes in an infinite loop after an ALTER TABLE
Submitted:	17 Sep 2005 12:51	Modified:	9 Dec 2005 0:05
Reporter:	Emmanuel JAMIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.12	OS:	Windows (windows & debian unstable)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The problem is that the server lock after a this sequence of queries : 

--Sequence BEGIN
drop table if exists test;

CREATE TABLE `test` (
  `id` int(11) NOT NULL default '0',
  `label` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`,`label`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test values ( 1,"e")  , (2,"a") , ( 3,"c")  , (4,"d");

ALTER TABLE `test` DROP PRIMARY KEY, CHANGE `id` `id` int(11) NOT NULL DEFAULT '0' FIRST,
CHANGE `label` `label` char(1) NOT NULL DEFAULT '' AFTER `id`,
ADD PRIMARY KEY (`label`, `id`), TYPE=INNODB ;

SELECT distinct id a, id b FROM `test` order by id desc;
--Sequence END

When the server is start on a console with "mysqld-nt --console", this message appears many many times ( maybe an infinite loop) :

050916 15:58:53  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `PRIMARY` of table `test/test`. Last data field length 4 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 4; hex 61020000; asc a   ;  

This work fine if the alter table statment is not executed.
This sequence works fine on mysql 4.1.12

How to repeat:
drop table if exists test;

CREATE TABLE `test` (
  `id` int(11) NOT NULL default '0',
  `label` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`,`label`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test values ( 1,"e")  , (2,"a") , ( 3,"c")  , (4,"d");

ALTER TABLE `test` DROP PRIMARY KEY, CHANGE `id` `id` int(11) NOT NULL DEFAULT '0' FIRST,
CHANGE `label` `label` char(1) NOT NULL DEFAULT '' AFTER `id`,
ADD PRIMARY KEY (`label`, `id`), TYPE=INNODB ;

SELECT distinct id a, id b FROM `test` order by id desc;

verified on linux, works fine for 4.1.14, but hangs with 5.0.10, .12 and 5.0bk

Hi!

If I restart the mysqld server after doing the ALTER, then the query works ok.

Also, if I create the table directly to its final form:

CREATE TABLE `test` (
  `id` int(11) NOT NULL default '0',
  `label` char(1) NOT NULL default '',
  PRIMARY KEY  (`label`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

then the query works ok.

Maybe the ALTER makes an inconsistent MySQL table object in memory, but the .frm file is created ok?

----

InnoDB prints the warning because MySQL is using a key value of length 4 in a search. The length does not make sense. The sensible key value lengths would be 1 or 5. Maybe MySQL does not know that in the new table definition, id is the first column in the PRIMARY KEY?

(gdb) bt
#0  row_sel_convert_mysql_key_to_innobase (tuple=0x40aca468,
    buf=0x8c43110 "¥%", buf_len=61, index=0x40ad1e68,
    key_ptr=0x8c79628 "a\002", key_len=4, trx=0x40aca068) at row0sel.c:2038
#1  0x0827b642 in ha_innobase::index_read(char*, char const*, unsigned, ha_rkey_
function) (this=0x8c570f0, buf=0x8c57218 "ÿ\002", key_ptr=0x8c79628 "a\002",
    key_len=4, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:3874
#2  0x08260f87 in QUICK_GROUP_MIN_MAX_SELECT::next_prefix() (this=0x8c7c800)
    at opt_range.cc:8409
#3  0x082608d3 in QUICK_GROUP_MIN_MAX_SELECT::get_next() (this=0x8c7c800)
    at opt_range.cc:8189
#4  0x08265989 in rr_quick (info=0x8c7e46c) at records.cc:165
#5  0x081fdfbd in sub_select (join=0x8c7d268, join_tab=0x8c7e430,
    end_of_records=false) at sql_select.cc:9412
#6  0x081fdb01 in do_select (join=0x8c7d268, fields=0x0, table=0x8c79df8,
    procedure=0x0) at sql_select.cc:9170
#7  0x081ea6cb in JOIN::exec() (this=0x8c7d268) at sql_select.cc:1304
#8  0x081ecc2d in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Ite
m>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, sel
ect_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8c55c38,
    rref_pointer_array=0x8c55f94, tables=0x8c7cfa8, wild_num=0,
    fields=@0x8c55efc, conds=0x0, og_num=1, order=0x8c7d1d8, group=0x0,
    having=0x0, proc_param=0x0, select_options=2156153345, result=0x8c7d258,
    unit=0x8c55c88, select_lex=0x8c55e7c) at sql_select.cc:2093
#9  0x081e6eb5 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
    thd=0x8c55c38, lex=0x8c55c78, result=0x8c7d258, setup_tables_done_option=0)
    at sql_select.cc:238
#10 0x081ac7ee in mysql_execute_command(THD*) (thd=0x8c55c38)
    at sql_parse.cc:2462
#11 0x081b4d66 in mysql_parse(THD*, char*, unsigned) (thd=0x8c55c38,
    inBuf=0x8c7cd50 "SELECT distinct id a, id b FROM `test` order by id desc",
    length=55) at sql_parse.cc:5393
#12 0x081aa877 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8c55c38,
    packet=0x8c74cf1 "SELECT distinct id a, id b FROM `test` order by id desc",
packet_length=56) at sql_parse.cc:1671
#13 0x081aa03f in do_command(THD*) (thd=0x8c55c38) at sql_parse.cc:1466
#14 0x081a9135 in handle_one_connection (arg=0x8c55c38) at sql_parse.cc:1118
#15 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#16 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
---Type <return> to continue, or q <return> to quit---
#17 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)

Regards,

Heikki

A typo:

"Maybe MySQL does not know that in the new table definition, id is the
first column in the PRIMARY KEY?"

Should read: ...id is NOT the first column...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32159

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32420

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32692

Loose index scan with using only second part of multipart index was choosen, which
results in creating wrong keys and endless loop.

Fixed in 5.0.16, cset 1.2002.1.1

Noted in 5.0.16 changelog.