MySQL Bugs: #8441: ROR code has key len 4, should be 5

Bug #8441	ROR code has key len 4, should be 5
Submitted:	11 Feb 2005 15:28	Modified:	23 Jun 2005 10:30
Reporter:	Fantin Vincent	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.7	OS:	NT, Linux/x86
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
I have a warning message on my server console, when I update or select, depending number of rows in my table.

This message ...
050211  9:23:44  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: Table name test/test, index name IDX2. Last data field length 5 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
len 4; hex 00010000; asc ;

How to repeat:
USE TEST;
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST (
  ID1 INT,
  ID2 DATE ,
  INDEX IDX2 (ID1,ID2),
  INDEX IDX1 (ID2)  
)
ENGINE = INNODB;

INSERT INTO TEST VALUES(1,'20040101');
SELECT * FROM  TEST  WHERE ID1 = 1  AND ID2= '20040101'; --> no messsage

INSERT INTO TEST VALUES(2,'20040102');
SELECT * FROM  TEST  WHERE ID1 = 1  AND ID2= '20040101'; --> message

050211  9:23:44  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: Table name test/test, index name IDX2. Last data field length 5 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 4; hex 00010000; asc ;

Hi!

Thank you for the bug report. I will check what is wrong.

Heikki

Hi!

I can still repeat this with 5.0.7.

I am raising the priority to P1 because there may be something wrong with the storage format of DATE.

Regards,

Heikki

heikki@hundin:~/mysql-5.0/sql> ./mysqld
050607 12:07:04  InnoDB: Started; log sequence number 0 3605366710
050607 12:07:05 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
050607 12:07:05 [Note] ./mysqld: ready for connections.
Version: '5.0.7-beta-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  S
ource distribution
050607 13:01:50  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `IDX2` of table `test/TEST`. Last data field length 5 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 4; hex 00010000; asc     ;
050607 13:01:50  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `IDX2` of table `test/TEST`. Last data field length 5 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 4; hex 00010000; asc     ;

Hi!

The bug seems to be in the ROR code. The key value it uses for IDX1 is sensible (integer value 1), but the key length is wrong 4. The key len should be 5, because there is the NULL byte and 4 bytes for the integer value.

This is a MySQL bug, not an InnoDB bug. I am lowering the priority of this bug to P2 because the bug is probably in the query optimizer, not in the storage of DATE values.

Regards,

Heikki

(gdb) bt
#0  row_sel_convert_mysql_key_to_innobase (tuple=0x40395c68,
    buf=0x8c8b8c0 '¥' <repeats 119 times>, "h4z\025\217)", buf_len=119,
    index=0x40394668, key_ptr=0x6368f6bc "", key_len=4, trx=0x40395068)
    at row0sel.c:2039
#1  0x08270860 in ha_innobase::records_in_range(unsigned, st_key_range*, st_key_
range*) (this=0x8c8b180, keynr=0, min_key=0x6368f69c, max_key=0x6368f68c)
    at ha_innodb.cc:5042
#2  0x08246202 in ror_scan_selectivity (info=0x8ca6070, scan=0x8ca5fe8)
    at opt_range.cc:2604
#3  0x0824656b in ror_intersect_add (info=0x8ca6070, ror_scan=0x8ca5fe8,
    is_cpk_scan=false) at opt_range.cc:2681
#4  0x08246be2 in get_best_ror_intersect (param=0x6368ffac, tree=0x8ca5cc0,
    read_time=1.4018281535648993, are_all_covering=0x6368ff93)
    at opt_range.cc:2874
#5  0x08244823 in SQL_SELECT::test_quick_select(THD*, Bitmap<64>, unsigned long
long, unsigned long long, bool) (this=0x8cab368, thd=0x8c895b8, keys_to_use=
      {map = 3}, prev_tables=0, limit=18446744073709551615,
    force_quick_range=false) at opt_range.cc:1810
#6  0x081e19b9 in get_quick_record_count (thd=0x8c895b8, select=0x8cab368,
    table=0x8c88720, keys=0x8cab124, limit=18446744073709551615)
    at sql_select.cc:2087
#7  0x081e2b74 in make_join_statistics (join=0x8ca9fb8, tables=0x0,
    conds=0x8ca9ef0, keyuse_array=0x8caaccc) at sql_select.cc:2399
#8  0x081dcfae in JOIN::optimize() (this=0x8ca9fb8) at sql_select.cc:655
#9  0x081e173f 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=0x8c895b8,
    rref_pointer_array=0x8c898fc, tables=0x8ca9a28, wild_num=1,
    fields=@0x8c8985c, conds=0x8ca9ef0, og_num=0, order=0x0, group=0x0,
    having=0x0, proc_param=0x0, select_options=2156153344, result=0x8ca9fa8,
    unit=0x8c89608, select_lex=0x8c897ec) at sql_select.cc:2029
#10 0x081dbcb9 in handle_select(THD*, st_lex*, select_result*, unsigned long) (
    thd=0x8c895b8, lex=0x8c895f8, result=0x8ca9fa8, setup_tables_done_option=0)
    at sql_select.cc:239
#11 0x081a38b7 in mysql_execute_command(THD*) (thd=0x8c895b8)
    at sql_parse.cc:2399
#12 0x081ac057 in mysql_parse(THD*, char*, unsigned) (thd=0x8c895b8,
    inBuf=0x8ca9910 "SELECT * FROM  TEST  WHERE ID1 = 1  AND ID2= '20040101'",
    length=55) at sql_parse.cc:5298
---Type <return> to continue, or q <return> to quit---
#13 0x081a19d4 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8c895b8,
    packet=0x8ca18b1 "SELECT * FROM  TEST  WHERE ID1 = 1  AND ID2= '20040101'",
packet_length=56) at sql_parse.cc:1652
#14 0x081a11e4 in do_command(THD*) (thd=0x8c895b8) at sql_parse.cc:1455
#15 0x081a0339 in handle_one_connection (arg=0x8c895b8) at sql_parse.cc:1113
#16 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#17 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#18 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb) frame 1
#1  0x08270860 in ha_innobase::records_in_range(unsigned, st_key_range*, st_key_
range*) (this=0x8c8b180, keynr=0, min_key=0x6368f69c, max_key=0x6368f68c)
    at ha_innodb.cc:5042
5042            row_sel_convert_mysql_key_to_innobase(
Current language:  auto; currently c++
(gdb) print *min_key
$4 = {key = 0x6368f6bc "", length = 4, flag = HA_READ_KEY_EXACT}
(gdb) x/4b min_key->key
0x6368f6bc:     0x00    0x01    0x00    0x00
(gdb) x/4b max_key->key
0x6368f6bc:     0x00    0x01    0x00    0x00
(gdb)

Oops, in the last comment I meant IDX2, not IDX1. It should read:

"
The key value it uses for IDX2 is sensible (integer value 1), but the key length is wrong 4. The key len should be 5, because there is the NULL byte and 4 bytes for the integer value.
"

Regards,

Heikki

Please consider making this bug a higher priority. We've been evaluating MySQL 5, and when we tested the latest version, 5.0.7, we ended up with nearly 3300 of these errors logged in 3 minutes. A couple examples of what we're seeing follow:

050615 22:53:02  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `entitylistitems_index2_idx` of table `darkrealms/entitylistitems`. Last data field length 122 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 128; hex 0d00000000000000080043484f4943455f320000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc           CHOICE_2                                                                                                              ;
050615 22:53:02  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `sequencedetails_index2_idx` of table `darkrealms/sequencedetails`. Last data field length 122 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 128; hex 01000000000000000d0044454641554c545f4f52444552000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc           DEFAULT_ORDER                                                                                                         ;

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/26165

Applied the patch to 5.0.7, and it seems to have fixed the problem. Thank you!

-rh

Patch approved by Igor

Fix pushed into 5.0.8 tree. 
Note for the docs team: Except for producing InnoDB warnings, the bug could cause inoptimal index_merge plans being chosen (and nothing else).

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.8 changelog; marked Closed.