Bug #13218 select MIN(..) / InnoDB: using a partial-field key prefix in search
Submitted: 15 Sep 2005 10:25 Modified: 24 Sep 2005 3:51
Reporter: Sebastien Caille Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.14-nt, 4.1.15-BK OS:Microsoft Windows (Windows XP, Linux)
Assigned to: Evgeny Potemkin

[15 Sep 2005 10:25] Sebastien Caille
Description:

When using select min(attribute), attribute beeing a indexed bigint(20),
the server outputs a warning:

050915 11:51:59  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `timestamp` of table `test/alarm`. Last data field length 9 bytes,

InnoDB: key ptr now exceeds key end by 8 bytes.
InnoDB: Key value in the MySQL format:
 len 1; hex 01; asc  ;

Server output
----------------
> mysqld-nt.exe --defaults-file=../my.ini --port=3306

050915 11:51:26  InnoDB: Started; log sequence number 0 8551526 
mysqld-nt.exe: ready for connections.
Version: '4.1.14-nt'  socket: ''  port: 3306  Official MySQL binary
050915 11:51:59  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `timestamp` of table `test/alarm`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 8 bytes.
InnoDB: Key value in the MySQL format:
 len 1; hex 01; asc  ;
 
Content of my.ini
---------------------
[mysqld]
transaction-isolation=READ-COMMITTED
binlog_do_db=none
max_binlog_size=4096
default_table_type=innodb
log-error=error.log
console

How to repeat:

create table alarm(id int primary key auto_increment, severity int(11), timestamp bigint(20), message text, category varchar(255)) type innodb;

alter table alarm add index(timestamp);

insert into alarm(severity, timestamp, message, category) values (1, 1126775698062, 'State changed to out', 'ROUTE-PROCESSING');

(The following produces the warning)
select min(timestamp) from alarm;
[15 Sep 2005 12:13] Valerii Kravchuk
Thank you for the bug report!

Verified as described on yesterday's 4.1.15-bk build on Linux also:

050915 15:07:35  InnoDB: Started; log sequence number 0 46890
/home/openxs/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.15-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribut
ion
050915 15:08:15  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `timestamp` of table `test/alarm`. Last data field length 9 bytes,

InnoDB: key ptr now exceeds key end by 8 bytes.
InnoDB: Key value in the MySQL format:
 len 1; hex 01; asc  ;
[15 Sep 2005 19:51] Heikki Tuuri
MySQL sends a key value where the SQL NULL byte is set to 1 (means that the column value is the SQL NULL) but it has forgotten to add the column length to the key value length. This is probably the same bug as:

http://bugs.mysql.com/bug.php?id=11039

It should be fixed also in 4.1.

Changing category to 'Optimizer'.

A MySQL key value field contains one byte for a possible NULL flag, and the bytes for the data, also in the case where the value is the SQL NULL. For a bigint, the key value field should be 9 bytes, not 1 byte as we see below.

(gdb) step
row_sel_convert_mysql_key_to_innobase (tuple=0x40acd068,
    buf=0x8b2d9a8 '¥' <repeats 200 times>..., buf_len=340, index=0x40acdb68,
    key_ptr=0x63f1c08c "\001", key_len=1, trx=0x40aca468) at row0sel.c:1965

Regards,

Heikki
[19 Sep 2005 0:41] Evgeny Potemkin
Patch for this bug is attached to bug report#11039
[19 Sep 2005 21:16] Evgeny Potemkin
Backported fix for bug#11039.

Fixed in 4.1.15, cset 1.2445
[24 Sep 2005 3:51] Paul Dubois
Noted in 4.1.15 changelog (with same note as for
Bug #11039 in 5.0.7).