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:Microsoft Windows (windows & debian unstable)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[17 Sep 2005 12:51] Emmanuel JAMIN
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;
[17 Sep 2005 19:15] Hartmut Holzgraefe
verified on linux, works fine for 4.1.14, but hangs with 5.0.10, .12 and 5.0bk
[18 Sep 2005 7:30] Heikki Tuuri
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
[18 Sep 2005 12:15] Heikki Tuuri
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...
[10 Nov 2005 20:59] Bugs System
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
[18 Nov 2005 21:09] Bugs System
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
[24 Nov 2005 16:54] Bugs System
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
[25 Nov 2005 22:46] Evgeny Potemkin
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
[9 Dec 2005 0:05] Paul Dubois
Noted in 5.0.16 changelog.