MySQL Bugs: #45185: InnoDB: Warning: using a partial-field key prefix in search

Bug #45185	InnoDB: Warning: using a partial-field key prefix in search
Submitted:	29 May 2009 10:19	Modified:	18 Aug 2014 6:44
Reporter:	Heribert Steuer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.81-log, 5.6.6	OS:	Linux (CentOS)
Assigned to:		CPU Architecture:	Any
Tags:	key prefix, Last data field length, partial-field

Description:
The logs of our mysql server show error messages like:

090529 11:08:44  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `ticker` of table `opus/aorder_archive`. Last data field length 22 bytes,
InnoDB: key ptr now exceeds key end by 18 bytes.
InnoDB: Key value in the MySQL format:
 len 4; hex 920c0000; asc     ;

These error messages appears 2 or 3 times a day on a pretty loaded server. The really serious thing about this problem is that i cannot reproduce it. It seems to be very random and I was not able to isolate any queries that cause this error. The only thing that happens regularily is that a stored function that creates a temporary table fails (by returning NULL values). 

This issue is pretty serious as it does not return an error but it returns invalid results. I searched the archives and stuff seemed to be fixed around 2005 - at least all posts regarding this issue are pretty old.

Can you recommend a way to isolate the queries that cause that warnings? I upgraded the server version to the latest one available, dropped indexes and recreated them, etc.

The index that causes the problem is:

KEY `ticker` (`ticker`,`handelsplatz`)

where the elements of the key are:

 `ticker` varchar(20) collate latin1_german1_ci NOT NULL default ''
 `handelsplatz` varchar(4) collate latin1_german1_ci NOT NULL default ''

and there is a constraint:

  CONSTRAINT `cs_aorder_fk_offlinekurse` FOREIGN KEY (`ticker`, `handelsplatz`) REFERENCES `offlinekurse` (`ticker`, `handelsplatz`)

where the referenced table called "offlinekurse" is:

 `ticker` varchar(20) collate latin1_german1_ci NOT NULL default ''
 `handelsplatz` varchar(4) collate latin1_german1_ci NOT NULL default ''

So from a table point of view, everything looks okay for me. Please advise!

Cheers,
Heri

How to repeat:
Yet not possible to isolate the issue.

Thank you for the report.

Could you please try to isolate query which causes this error? You can use general query log or MySQL Proxy for it.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I got this on mysql-trunk today.

120517 17:31:14  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `id` of table `test`.`blobtest_9`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 3 bytes.
InnoDB: Key value in the MySQL format:
 len 6; hex 00000000d243; asc      C;

Table structure is:

mysql> show create table blobtest_9\G
*************************** 1. row ***************************
       Table: blobtest_9
Create Table: CREATE TABLE `blobtest_9` (
  `id` bigint(20) DEFAULT NULL,
  `data1` char(5) DEFAULT NULL,
  `data2` varchar(25) DEFAULT NULL,
  `data3` varchar(155) DEFAULT NULL,
  `data4` longblob,
  `data5` longblob,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `a` (`data1`,`data2`) USING BTREE,
  UNIQUE KEY `b` (`data2`,`data3`) USING BTREE,
  KEY `c` (`data4`(15)) USING BTREE,
  KEY `d` (`data1`) USING BTREE,
  KEY `e` (`data2`) USING BTREE,
  KEY `f` (`data3`) USING BTREE,
  KEY `g` (`data4`(100),`data5`(100)) USING BTREE,
  KEY `h` (`data5`(1)) USING BTREE,
  KEY `i` (`data1`) USING BTREE,
  KEY `j` (`data2`) USING BTREE,
  KEY `k` (`data3`) USING BTREE,
  KEY `l` (`data4`(255)),
  KEY `m` (`data5`(1)),
  KEY `n` (`data1`),
  KEY `o` (`data2`),
  KEY `p` (`data3`),
  KEY `q` (`data4`(7)),
  KEY `r` (`data5`(1)),
  KEY `s` (`data1`),
  KEY `t` (`data2`),
  KEY `u` (`data3`),
  FULLTEXT KEY `ft1` (`data2`),
  CONSTRAINT `blobtest_9_ibfk_1` FOREIGN KEY (`id`) REFERENCES `blobtest_7` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `blobtest_9_ibfk_2` FOREIGN KEY (`id`) REFERENCES `blobtest_7` (`id`) ON DELETE CASCADE,
  CONSTRAINT `blobtest_9_ibfk_3` FOREIGN KEY (`id`) REFERENCES `blobtest_1` (`id`) ON DELETE CASCADE ON UPDATE SET NULL,
  CONSTRAINT `blobtest_9_ibfk_4` FOREIGN KEY (`data2`) REFERENCES `blobtest_7` (`data2`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `blobtest_9_ibfk_5` FOREIGN KEY (`data3`) REFERENCES `blobtest_4` (`data3`) ON UPDATE CASCADE,
  CONSTRAINT `blobtest_9_ibfk_6` FOREIGN KEY (`id`) REFERENCES `blobtest_6` (`id`),
  CONSTRAINT `blobtest_9_ibfk_7` FOREIGN KEY (`id`) REFERENCES `blobtest_2` (`id`) ON UPDATE SET NULL,
  CONSTRAINT `blobtest_9_ibfk_8` FOREIGN KEY (`id`) REFERENCES `blobtest_2` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `blobtest_9_ibfk_9` FOREIGN KEY (`id`) REFERENCES `blobtest_1` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `blobtest_9_ibfk_10` FOREIGN KEY (`id`) REFERENCES `blobtest_1` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `blobtest_9_ibfk_11` FOREIGN KEY (`id`) REFERENCES `blobtest_6` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `blobtest_9_ibfk_12` FOREIGN KEY (`id`) REFERENCES `blobtest_7` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `blobtest_9_ibfk_13` FOREIGN KEY (`id`) REFERENCES `blobtest_3` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `blobtest_9_ibfk_14` FOREIGN KEY (`id`) REFERENCES `blobtest_3` (`id`) ON DELETE NO ACTION ON UPDATE SET NULL,
  CONSTRAINT `blobtest_9_ibfk_15` FOREIGN KEY (`id`) REFERENCES `blobtest_6` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16
1 row in set (0.00 sec)

Shane,

do you have a query which caused this or idea how to repeat the issue?

I'll try make a standalone testcase sometime but in the mean time here's the blobtester app to reproduce it.

Shane,

thank you for the test case. It consistently works, so, I think, I can just set this bug to "Verified".

Cause was query updating BLOB field and searching problematic key same time.

Failing query:

update  `blobtest_2` as `t1`,`blobtest_4` as `t2` set `t1`.`data3`='ðFáõvz°÷¸ß¯^E<^GÏ<84>Z³¦¶=VÞ<8b>æð^Y<90>{dýõþ:f2TÈp<80>²rB<81      >Vc' where `t1`.`id`=`t2`.`id` and `t1`.`id`<=0

we also hit this problem though there isn't any blob column exists in the index :

130508  9:59:45  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_sid_bt_st_im` of table `tc13`.`tc_biz_order_0217`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 16; hex d9c24802000000000001e90300000000; asc 130508  9:59:45  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_sid_bt_st_im` of table `tc13`.`tc_biz_order_0211`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 16; hex d30e992a000000000001e90300000000; asc 130508  9:59:45  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_sid_bt_st_im` of table `tc13`.`tc_biz_order_0210`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 16; hex d2b29005000000000001e90300000000; asc   H             ;
130508  9:59:45  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_sid_bt_st_im` of table `tc13`.`tc_biz_order_0217`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 16; hex d9c24802000000000001e90300000000; asc   H           *            ;
130508  9:59:45  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_sid_bt_st_im` of table `tc13`.`tc_biz_order_0211`. Last data field length 9 bytes,
InnoDB: key ptr now exceeds key end by 1 bytes.
InnoDB: Key value in the MySQL format:
 len 16; hex d30e992a000000000001e90300000000; asc    *            ;
                ;

found a 5.7 specific version of this, filed separately.
Bug 16886196 - ASSERTION AFTER "WARNING: USING A PARTIAL-FIELD KEY PREFIX IN SEARCH."

Posted by developer:
 
I suspect that this bug has been fixed in some release, maybe already in MySQL 5.1 or 5.5.
The reason is that the following fix added a debug assertion, ut_ad(0), which would trip in our internal testing "all the time" if the message were issued:

Bug#14578060 INNODB: WARNING: USING A PARTIAL-FIELD KEY PREFIX IN SEARCH

That fix is for a different bug that only seemed to be triggerable in MySQL 5.6.6 and 5.6.7 according to developer comments in the bug report. The fix went into MySQL 5.6.8.

I do not know when exactly this older bug was fixed. Has anyone repeated it in MySQL 5.5?

Posted by developer:
 
Sveta Smirnova wrote in an email on Friday, August 15, 2014:
I cannot repeat this bug anymore. You can close it.