| Bug #20086 | Can't get data from key partitioned tables with VARCHAR key | ||
|---|---|---|---|
| Submitted: | 26 May 2006 6:57 | Modified: | 4 Jul 2006 9:18 |
| Reporter: | McEase Tu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.1.9 | OS: | Linux (Debian Linux 2.6.8-3-686-smp) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[26 May 2006 14:06]
Hartmut Holzgraefe
Verified, this fails consistently with MyISAM, with InnoDB it fails about half of the time, when changing the key from VARCHAR(16) to CHAR(16) all works well. I'm attaching a test case for MyISAM and InnoDB, the InnoDB test in there will be repeated 5 times to produce a high likelyhood of failure.
[26 May 2006 14:14]
Hartmut Holzgraefe
mysqltest test case
Attachment: bug20086.tar.gz (application/x-gzip, text), 692 bytes.
[14 Jun 2006 16:35]
Sergey Petrunya
Mikael,
I've investigated and this bug seems not to be related to partition pruning: If one disables partition pruning the bug still occurs.
Below is a diff that I've used to disable partition pruning:
===== opt_range.cc 1.220 vs edited =====
***************
*** 2325,2331 ****
if (!part_info)
DBUG_RETURN(FALSE); /* not a partitioned table */
! if (!pprune_cond)
{
mark_all_partitions_as_used(part_info);
DBUG_RETURN(FALSE);
--- 2325,2332 ----
if (!part_info)
DBUG_RETURN(FALSE); /* not a partitioned table */
! // disable partition prining:
! // if (!pprune_cond)
{
mark_all_partitions_as_used(part_info);
DBUG_RETURN(FALSE);
[15 Jun 2006 19:26]
Alexander Barkov
The problem is in this part of code:
{
uint len= field->pack_length();
ulong nr1= 1;
CHARSET_INFO *cs= field->charset();
cs->coll->hash_sort(cs, (uchar*)field->ptr, len, &nr1, &nr2);
hashnr^= (uint32)nr1;
}
hash_sort() is executed with the full length returned from field->pack_length(),
which is wrong for VARCHAR where the first one byte or two bytes stand for
real data length, then goes data itself, then goes garbage. It seems we need
a new method in the Field class, something likie field->hash(), which will
calculate hash according to the datatype.
[21 Jun 2006 8:03]
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/commits/7995
[21 Jun 2006 9:52]
Alexander Barkov
To Doc team: ensure that we write in the manual that all users using PARTITION BY KEY and not NDB Cluster need to dump and restore tables to upgrade to new version.
[30 Jun 2006 12:59]
Alexander Barkov
Fixed in 5.0.24 and 5.1.12.
[4 Jul 2006 9:17]
Jon Stephens
Changed category to Partitioning.
[4 Jul 2006 9:18]
Jon Stephens
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 bug fix. More information about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
[4 Jul 2006 9:24]
Jon Stephens
Documented in 5.1.12 changelog. Added note to Partitioning Limitations section of 5.1 Manual. Closed.
Description: I created a simple innodb table and partitioned it by key (varchar type). For the partition column, if I inserted some data with the same prefix then I can't retreive the shorter one later. I also recreae this bug under Debian Linux 2.6.16-1-amd64-k8-smp and Windows XP (mysql 5.1.7 non-installation version). How to repeat: mysql> create table test.test (id varchar(16) primary key) engine=innodb partition by key(id) partitions 5; Query OK, 0 rows affected (0.01 sec) mysql> insert into test.test values ('a'); Query OK, 1 row affected (0.00 sec) mysql> insert into test.test values ('aa'); Query OK, 1 row affected (0.00 sec) mysql> insert into test.test values ('aaa'); Query OK, 1 row affected (0.00 sec) mysql> select * from test.test where id = 'a'; Empty set (0.00 sec) mysql> show create table test.test; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` varchar(16) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk PARTITION BY KEY (id) PARTITIONS 5 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)