Bug #11039 InnoDB: Warning: using a partial-field key prefix in search.
Submitted: 2 Jun 2005 15:07 Modified: 16 Jun 2005 17:11
Reporter: Tomas Ulin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0 OS:Any (any)
Assigned to: Michael Widenius

[2 Jun 2005 15:07] Tomas Ulin
Description:
<tomas> CURRENT_TEST: type_bit_innodb
<tomas> 050602 19:48:15  InnoDB: Warning: using a partial-field key prefix in search.
<tomas> InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
<tomas> InnoDB: key ptr now exceeds key end by 1 bytes.
<tomas> InnoDB: Key value in the MySQL format:
<tomas>  len 1; hex 01; asc  ;
<tomas> 050602 19:48:15  InnoDB: Warning: using a partial-field key prefix in search.
<tomas> InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
<tomas> InnoDB: key ptr now exceeds key end by 1 bytes.
<tomas> InnoDB: Key value in the MySQL format:
<tomas>  len 1; hex 01; asc  ;
<tomas> 050602 19:48:15  InnoDB: Warning: using a partial-field key prefix in search.
<tomas> InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
<tomas> InnoDB: key ptr now exceeds key end by 1 bytes.
<tomas> InnoDB: Key value in the MySQL format:
<tomas>  len 5; hex 0400000001; asc      ;
<tomas> 050602 19:48:15  InnoDB: Warning: using a partial-field key prefix in search.
<tomas> InnoDB: index `a` of table `test/t1`. Last data field length 3 bytes,
<tomas> InnoDB: key ptr now exceeds key end by 2 bytes.
<tomas> InnoDB: Key value in the MySQL format:
<tomas>  len 7; hex 04000000000001; asc        ;
<monty|review> testing in my tree
<monty|review> tomas: ok, got same warning
<monty|review> this is something that ramil should check out!

How to repeat:
see above
[2 Jun 2005 15:12] Jorge del Conde
Hi Tomas,

Can you give us a reproducible test case that shows this behaviour ?

Thanks!
[2 Jun 2005 15:52] Heikki Tuuri
Hi!

I did not get this error with 5.0 pulled and built 2 hours ago.

Instead, it hangs in lock_multi in ps-protocol mode :(.

Regards,

Heikki

heikki@hundin:~/mysql-5.0> make test
cd mysql-test; ./mysql-test-run && ./mysql-test-run --ps-protocol
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --based
ir=. --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb
--language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/
Installing Slave Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables     --based
ir=. --datadir=./var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb     -
-language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

TEST                            RESULT
-------------------------------------------------------
alias                          [ pass ]
alter_table                    [ pass ]
analyse                        [ pass ]
ansi                           [ pass ]
archive                        [ skipped ]
auto_increment                 [ pass ]
backup                         [ pass ]
bdb-alter-table-1              [ skipped ]
bdb-alter-table-2              [ skipped ]
bdb-crash                      [ skipped ]
bdb-deadlock                   [ skipped ]
bdb                            [ skipped ]
bdb_cache                      [ skipped ]
bench_count_distinct           [ pass ]
bigint                         [ pass ]
binary                         [ pass ]
binlog                         [ skipped ]
blackhole                      [ skipped ]
bool                           [ pass ]
bulk_replace                   [ pass ]
case                           [ pass ]
cast                           [ pass ]
check                          [ pass ]
client_xml                     [ pass ]
comments                       [ pass ]
compare                        [ pass ]
connect                        [ pass ]
consistent_snapshot            [ pass ]
constraints                    [ pass ]
count_distinct                 [ pass ]
count_distinct2                [ pass ]
count_distinct3                [ pass ]
create                         [ pass ]
create_select_tmp              [ pass ]
csv                            [ skipped ]
ctype_big5                     [ pass ]
ctype_collate                  [ pass ]
ctype_cp1250_ch                [ pass ]
ctype_cp1251                   [ pass ]
ctype_cp932                    [ pass ]
ctype_create                   [ pass ]
ctype_eucjpms                  [ pass ]
ctype_latin1                   [ pass ]
ctype_latin1_de                [ pass ]
ctype_latin2                   [ pass ]
ctype_many                     [ pass ]
ctype_mb                       [ pass ]
ctype_recoding                 [ pass ]
ctype_sjis                     [ pass ]
ctype_tis620                   [ pass ]
ctype_uca                      [ pass ]
ctype_ucs                      [ pass ]
ctype_ucs_binlog               [ pass ]
ctype_ujis                     [ pass ]
ctype_utf8                     [ pass ]
date_formats                   [ pass ]
default                        [ pass ]
delayed                        [ pass ]
delete                         [ pass ]
derived                        [ pass ]
dirty_close                    [ pass ]
distinct                       [ pass ]
drop                           [ pass ]
drop_temp_table                [ pass ]
empty_table                    [ pass ]
endspace                       [ pass ]
errors                         [ pass ]
exampledb                      [ skipped ]
explain                        [ pass ]
federated                      [ skipped ]
flush                          [ pass ]
flush_block_commit             [ pass ]
flush_read_lock_kill           [ pass ]
flush_table                    [ pass ]
foreign_key                    [ pass ]
fulltext                       [ pass ]
fulltext2                      [ pass ]
fulltext_cache                 [ pass ]
fulltext_distinct              [ pass ]
fulltext_left_join             [ pass ]
fulltext_multi                 [ pass ]
fulltext_order_by              [ pass ]
fulltext_update                [ pass ]
fulltext_var                   [ pass ]
func_compress                  [ pass ]
func_concat                    [ pass ]
func_crypt                     [ pass ]
func_date_add                  [ pass ]
func_default                   [ pass ]
func_encrypt                   [ skipped ]
func_equal                     [ pass ]
func_gconcat                   [ pass ]
func_group                     [ pass ]
func_if                        [ pass ]
func_in                        [ pass ]
func_isnull                    [ pass ]
func_like                      [ pass ]
func_math                      [ pass ]
func_misc                      [ pass ]
func_op                        [ pass ]
func_regexp                    [ pass ]
func_sapdb                     [ pass ]
func_set                       [ pass ]
func_str                       [ pass ]
func_system                    [ pass ]
func_test                      [ pass ]
func_time                      [ pass ]
func_timestamp                 [ pass ]
gcc296                         [ pass ]
gis-rtree                      [ pass ]
gis                            [ pass ]
grant                          [ pass ]
grant2                         [ pass ]
grant3                         [ pass ]
grant_cache                    [ pass ]
greedy_optimizer               [ pass ]
group_by                       [ pass ]
group_min_max                  [ pass ]
handler                        [ pass ]
having                         [ pass ]
heap                           [ pass ]
heap_auto_increment            [ pass ]
heap_btree                     [ pass ]
heap_hash                      [ pass ]
help                           [ pass ]
index_merge                    [ pass ]
index_merge_bdb                [ skipped ]
index_merge_innodb             [ pass ]
index_merge_innodb2            [ pass ]
index_merge_ror                [ pass ]
index_merge_ror_cpk            [ pass ]
information_schema             [ pass ]
information_schema_db          [ pass ]
information_schema_inno        [ pass ]
init_connect                   [ pass ]
init_file                      [ pass ]
innodb-big                     [ skipped ]
innodb-deadlock                [ pass ]
innodb-lock                    [ pass ]
innodb-replace                 [ pass ]
innodb                         [ pass ]
innodb_cache                   [ pass ]
innodb_handler                 [ pass ]
insert                         [ pass ]
insert_select-binlog           [ pass ]
insert_select                  [ pass ]
insert_update                  [ pass ]
join                           [ pass ]
join_crash                     [ pass ]
join_nested                    [ pass ]
join_outer                     [ pass ]
key                            [ pass ]
key_cache                      [ pass ]
key_diff                       [ pass ]
key_primary                    [ pass ]
keywords                       [ pass ]
kill                           [ pass ]
limit                          [ pass ]
loaddata                       [ pass ]
lock                           [ pass ]
lock_multi                     [ pass ]
lock_tables_lost_commit        [ pass ]
lowercase_table                [ pass ]
lowercase_table2               [ skipped ]
lowercase_table3               [ skipped ]
lowercase_table_grant          [ pass ]
lowercase_table_qcache         [ pass ]
lowercase_view                 [ pass ]
merge                          [ pass ]
metadata                       [ pass ]
mix_innodb_myisam_binlog       [ pass ]
multi_statement                [ pass ]
multi_update                   [ pass ]
myisam-blob                    [ pass ]
myisam                         [ pass ]
mysql                          [ pass ]
mysql_client_test              [ pass ]
mysql_protocols                [ pass ]
mysqlbinlog                    [ pass ]
mysqlbinlog2                   [ pass ]
mysqldump                      [ pass ]
mysqlshow                      [ pass ]
mysqltest                      [ pass ]
ndb_alter_table                [ skipped ]
ndb_autodiscover               [ skipped ]
ndb_autodiscover2              [ skipped ]
ndb_basic                      [ skipped ]
ndb_bitfield                   [ skipped ]
ndb_blob                       [ skipped ]
ndb_cache                      [ skipped ]
ndb_cache2                     [ skipped ]
ndb_cache_multi                [ skipped ]
ndb_cache_multi2               [ skipped ]
ndb_charset                    [ skipped ]
ndb_condition_pushdown         [ skipped ]
ndb_database                   [ skipped ]
ndb_index                      [ skipped ]
ndb_index_ordered              [ skipped ]
ndb_index_unique               [ skipped ]
ndb_insert                     [ skipped ]
ndb_limit                      [ skipped ]
ndb_lock                       [ skipped ]
ndb_minmax                     [ skipped ]
ndb_multi                      [ skipped ]
ndb_read_multi_range           [ skipped ]
ndb_replace                    [ skipped ]
ndb_restore                    [ skipped ]
ndb_subquery                   [ skipped ]
ndb_transaction                [ skipped ]
ndb_truncate                   [ skipped ]
ndb_types                      [ skipped ]
ndb_update                     [ skipped ]
negation_elimination           [ pass ]
null                           [ pass ]
null_key                       [ pass ]
odbc                           [ pass ]
olap                           [ pass ]
openssl_1                      [ skipped ]
order_by                       [ pass ]
order_fill_sortbuf             [ pass ]
outfile                        [ pass ]
overflow                       [ pass ]
packet                         [ pass ]
preload                        [ pass ]
ps                             [ pass ]
ps_10nestset                   [ pass ]
ps_11bugs                      [ pass ]
ps_1general                    [ pass ]
ps_2myisam                     [ pass ]
ps_3innodb                     [ pass ]
ps_4heap                       [ pass ]
ps_5merge                      [ pass ]
ps_6bdb                        [ skipped ]
ps_7ndb                        [ skipped ]
ps_grant                       [ pass ]
query_cache                    [ pass ]
query_cache_merge              [ pass ]
raid                           [ skipped ]
range                          [ pass ]
rename                         [ pass ]
repair                         [ pass ]
replace                        [ pass ]
rollback                       [ pass ]
row                            [ pass ]
rowid_order_bdb                [ skipped ]
rowid_order_innodb             [ pass ]
rpl000001                      [ pass ]
rpl000002                      [ pass ]
rpl000004                      [ pass ]
rpl000005                      [ pass ]
rpl000006                      [ pass ]
rpl000008                      [ pass ]
rpl000009                      [ pass ]
rpl000010                      [ pass ]
rpl000011                      [ pass ]
rpl000012                      [ pass ]
rpl000013                      [ pass ]
rpl000015                      [ pass ]
rpl000017                      [ pass ]
rpl000018                      [ skipped ]
rpl_EE_error                   [ pass ]
rpl_alter                      [ pass ]
rpl_auto_increment             [ pass ]
rpl_chain_temp_table           [ skipped ]
rpl_change_master              [ pass ]
rpl_charset                    [ pass ]
rpl_commit_after_flush         [ pass ]
rpl_create_database            [ pass ]
rpl_deadlock                   [ pass ]
rpl_delete_all                 [ pass ]
rpl_do_grant                   [ pass ]
rpl_drop                       [ pass ]
rpl_drop_temp                  [ pass ]
rpl_empty_master_crash         [ pass ]
rpl_error_ignored_table        [ pass ]
rpl_failed_optimize            [ pass ]
rpl_failsafe                   [ skipped ]
rpl_flush_log_loop             [ pass ]
rpl_flush_tables               [ pass ]
rpl_free_items                 [ pass ]
rpl_get_lock                   [ pass ]
rpl_heap                       [ skipped ]
rpl_ignore_grant               [ pass ]
rpl_init_slave                 [ pass ]
rpl_insert_id                  [ pass ]
rpl_insert_ignore              [ pass ]
rpl_loaddata                   [ pass ]
rpl_loaddata_rule_m            [ pass ]
rpl_loaddata_rule_s            [ pass ]
rpl_loaddatalocal              [ pass ]
rpl_log                        [ pass ]
rpl_log_pos                    [ pass ]
rpl_many_optimize              [ pass ]
rpl_master_pos_wait            [ pass ]
rpl_max_relay_size             [ pass ]
rpl_misc_functions             [ pass ]
rpl_multi_delete               [ pass ]
rpl_multi_delete2              [ pass ]
rpl_multi_query                [ pass ]
rpl_multi_update               [ pass ]
rpl_multi_update2              [ pass ]
rpl_mystery22                  [ pass ]
rpl_openssl                    [ skipped ]
rpl_optimize                   [ pass ]
rpl_ps                         [ pass ]
rpl_redirect                   [ pass ]
rpl_relayrotate                [ pass ]
rpl_relayspace                 [ pass ]
rpl_replicate_do               [ pass ]
rpl_reset_slave                [ pass ]
rpl_rewrite_db                 [ pass ]
rpl_rotate_logs                [ pass ]
rpl_server_id1                 [ pass ]
rpl_server_id2                 [ pass ]
rpl_session_var                [ pass ]
rpl_set_charset                [ pass ]
rpl_skip_error                 [ pass ]
rpl_sp                         [ pass ]
rpl_sporadic_master            [ pass ]
rpl_start_stop_slave           [ pass ]
rpl_temporary                  [ pass ]
rpl_timezone                   [ pass ]
rpl_trunc_binlog               [ skipped ]
rpl_until                      [ pass ]
rpl_user_variables             [ pass ]
rpl_variables                  [ pass ]
rpl_view                       [ pass ]
schema                         [ pass ]
select                         [ pass ]
select_found                   [ pass ]
select_safe                    [ pass ]
show_check                     [ pass ]
skip_grants                    [ pass ]
skip_name_resolve              [ pass ]
sp-error                       [ pass ]
sp-security                    [ pass ]
sp-threads                     [ pass ]
sp                             [ pass ]
sp_trans                       [ pass ]
sql_mode                       [ pass ]
status                         [ pass ]
strict                         [ pass ]
subselect                      [ pass ]
subselect2                     [ pass ]
subselect_gis                  [ pass ]
subselect_innodb               [ pass ]
sum_distinct-big               [ skipped ]
sum_distinct                   [ pass ]
symlink                        [ pass ]
synchronization                [ pass ]
system_mysql_db                [ pass ]
system_mysql_db_fix            [ pass ]
system_mysql_db_refs           [ pass ]
tablelock                      [ pass ]
temp_table                     [ pass ]
timezone                       [ pass ]
timezone2                      [ pass ]
timezone3                      [ pass ]
timezone_grant                 [ pass ]
trigger                        [ pass ]
truncate                       [ pass ]
type_bit                       [ pass ]
type_bit_innodb                [ pass ]
type_blob                      [ pass ]
type_date                      [ pass ]
type_datetime                  [ pass ]
type_decimal                   [ pass ]
type_enum                      [ pass ]
type_float                     [ pass ]
type_nchar                     [ pass ]
type_newdecimal                [ pass ]
[2 Jun 2005 16:17] Tomas Ulin
test passes fo us as well

however master.err gives the warnings I showed...

You don't get any warnings in this test in master.err?

BR,

T
[2 Jun 2005 16:30] Heikki Tuuri
Tomas,

ok, it does print warnings to the .err log!

MySQL probably calculates the key part length wrong for BIT(n).

Thank you,

Heikki

heikki@hundin:~/mysql-5.0/mysql-test/t>  ../../client/mysql test < type_bit_inno
db.test

heikki@hundin:~/mysql-5.0/sql> ./mysqld
050602 18:22:11  InnoDB: Started; log sequence number 1 3004410732
050602 18:22:11 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
050602 18:22:11 [Note] ./mysqld: ready for connections.
Version: '5.0.7-beta-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  S
ource distribution

050602 18:29:53  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 1; hex 01; asc  ;
050602 18:29:53  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 1; hex 01; asc  ;
050602 18:29:54  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `a` of table `test/t1`. Last data field length 2 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 5; hex 0400000001; asc      ;
050602 18:29:54  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `a` of table `test/t1`. Last data field length 3 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 7; hex 04000000000001; asc        ;
[3 Jun 2005 7:54] Ramil Kalimullin
Hi Heikki!

Debugging the row0sel.c:row_sel_convert_mysql_key_to_innobase() I didn't
understand how it's related to the bit type.
Looks like the issue is more common, could you try the following?

drop table if exists t2;
create table t2 (a char(1), b char(1), key(a, b)) engine=innodb;
insert into t2 values ('8', '6'), ('4', '7');
select min(a) from t2;

I got the same warnings in this case.
[3 Jun 2005 12:22] Heikki Tuuri
Raising the priority to P1, because this can be a symptom of a need for data conversion. Recall the DECIMAL case 2 weeks ago.

Regards,

Heikki
[5 Jun 2005 14:17] Michael Widenius
Noticed a bug in the min() function where we send a too short key length for key parts that may be null. Now fixing this. Will update or close bug report when I am done
[6 Jun 2005 10:51] 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/25635
[6 Jun 2005 10:53] Michael Widenius
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
[7 Jun 2005 11:03] Heikki Tuuri
Putting this to the 'Patch approved' state because Monty's patch is already pushed into the 5.0 tree.
--Heikki
[16 Jun 2005 17:11] Heikki Tuuri
Fixed in 5.0.7.
[19 Sep 2005 0:35] 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/30031
[19 Sep 2005 0:38] 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/30032