MySQL Bugs: #19956: Problems with VARCHAR primary key and BLOB fields

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

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

mysqltest test case

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

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

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.

corrected test case

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

Looks as if this isn't fully fixed yet, see

http://forums.mysql.com/read.php?25,105434,105434

Fixed in 5.0.23, not .22 as this was a security hotfix release only.

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');

Verified using 5.1.14-beta and fresh pull from repository.

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

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

pushded to 5.1.15

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.

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.

pushed to 5.0.36

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.