Bug #19956 Problems with VARCHAR primary key and BLOB fields
Submitted: 19 May 2006 19:20 Modified: 25 Jan 2007 3:45
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.0, 5.1 OS:Linux (linux)
Assigned to: Martin Skold CPU Architecture:Any
Tags: BLOB, primary key, varchar

[19 May 2006 19:20] Hartmut Holzgraefe
Description:
SELECT statements with a BLOB or TEXT field in the selected
field list and a WHERE condition doing a primary key lookup
on a VARCHAR primary key produce empty result sets.

Any of the following makes the problem go away:
- remove the BLOB/TEXT field from the field list
- use a regular UNIQUE index on the VARCHAR field
  instead of making it the PRIMARY KEY
- use CHAR(n) instead of VARCHAR(n) for the PK

How to repeat:
see attached test case
[19 May 2006 19:47] Hartmut Holzgraefe
mysqltest test case

Attachment: bug19956.tar.gz (application/x-gzip, text), 986 bytes.

[21 May 2006 9:15] 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/6677
[22 May 2006 9:58] 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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.22 changelog; closed.
[22 May 2006 17:54] Hartmut Holzgraefe
corrected test case

Attachment: bug19956.tar.gz (application/x-gzip, text), 982 bytes.

[29 Jul 2006 16:36] Hartmut Holzgraefe
Looks as if this isn't fully fixed yet, see

http://forums.mysql.com/read.php?25,105434,105434
[29 Jul 2006 16:47] Hartmut Holzgraefe
Fixed in 5.0.23, not .22 as this was a security hotfix release only.
[13 Dec 2006 8:47] Geert Vanderkelen
Re-opening this bug as something similar is happening with 5.1.14.
Using following test case, you get an error like:
  ERROR 1032 (HY000) at line 14: Can't find record in 't1'
Modifying the TEXT field to VARCHAR is a work-around.

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` varchar(255) NOT NULL,
  `tag` int(11) NOT NULL,
  `doc` text NOT NULL,
  `type` varchar(150) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES ('sakila',1,'Some text goes here','text',CURRENT_TIMESTAMP);
SELECT id, tag, doc, type, modified FROM t1 WHERE id IN ('flipper','orka');
[13 Dec 2006 9:13] Geert Vanderkelen
Verified using 5.1.14-beta and fresh pull from repository.
[14 Dec 2006 15:55] 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/16961

ChangeSet@1.2365, 2006-12-14 16:52:50+01:00, mskold@mysql.com +1 -0
  bug#19956  Problems with VARCHAR primary key and BLOB fields:put back removed error handling for HA_ERR_KEY_NOT_FOUND
[15 Dec 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/17027

ChangeSet@1.2290, 2006-12-15 09:03:21+01:00, mskold@mysql.com +2 -0
  bug#19956  Problems with VARCHAR primary key and BLOB fields:added test case
[27 Dec 2006 17:38] Tomas Ulin
pushded to 5.1.15
[28 Dec 2006 12:50] Jon Stephens
The 5.0 fix was documented in the 5.0.23 changelog; when the the 5.0.22 security-fix release was made, all other bugfix entries were simply moved to the 5.0.23 changelog.
[28 Dec 2006 13:01] 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://dev.mysql.com/doc/en/installing-source.html

Documented fix for 5.1 in 5.1.15 changelog.
[24 Jan 2007 7:47] Tomas Ulin
pushed to 5.0.36
[25 Jan 2007 3:45] 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://dev.mysql.com/doc/en/installing-source.html

Moved bugfix report in 5.0.23 changelog to 5.0.36 changelog. Please advise if this was not the corrext way to handle this.