MySQL Bugs: #13218: select MIN(..) / InnoDB: using a partial-field key prefix in search

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:	Windows (Windows XP, Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

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;

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  ;

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

Patch for this bug is attached to bug report#11039

Backported fix for bug#11039.

Fixed in 4.1.15, cset 1.2445

Noted in 4.1.15 changelog (with same note as for
Bug #11039 in 5.0.7).