MySQL Bugs: #18937: editing a select's blobs and then viewing shows only "placeholder"

Bug #18937	editing a select's blobs and then viewing shows only "placeholder"
Submitted:	10 Apr 2006 9:52	Modified:	20 Dec 2006 10:04
Reporter:	Jim Michaels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.20	OS:	Windows (WIndows XP Pro MP)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Result Set

Description:
1. when I edit blobs (images) in QB and click the view button, that all look the same. a few bytes that in ascii that reads "placeholder", instead of my image.

2. sometimes you get error messages about "nil" when all data is filled in! (this was before I changed image to a DEFAULT NULL type - don't know if that makes a diff - shouldn't!). QB is sometimes generating incorrect statements during the edit/apply process.  sometimes the data you enter ends up as new rows instead of on the same rows, even when numbered on the autonumbered column.  intermittent bug.

How to repeat:
CREATE TABLE `photos` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  `image` blob NOT NULL,
  `mime_type` varchar(25) NOT NULL default '',
  `comment` longtext,
  PRIMARY KEY  (`image_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

Drag the table into the query window to do a select.
Click the edit button.
Input 4 imagetypes (image/jpeg) and some random comments, and load 4 jpeg images into each row of the image column, then click apply.
you may get improper error messages about nil. (I did. QB doesn't always genenerate correct statements here during editing - keep trying).  

Suggested fix:
put in a jpeg, png, gif, bmp, xfig viewer, maybe some other formats

I found out there is a viewer in there. I am not sure what I did wrong or why I couldn't get it to function the first time.  in the process of not really looking at what I was doing I obliterated my original files with "<placeholder>".  

But I did observe some frustratingly bizzarre behavior for a while when I first started up the browser with regards to edit/apply and blobs.

Thanks for your bug report.  I was able to reproduce this under XP and XP64 using the supplied table.  I filled it up with JPG pics.

I've conducted new test on MySQL 4.1.21-debug-log BK on Suse with 1.1.20 on WinXP Pro SP2.
Table in question is (field `comment` longtext or any other field has no consequence on error so you can try recreating report on smaller table!):
CREATE TABLE `bigtbl1test` (
  `rbr` int(10) unsigned NOT NULL auto_increment,
  `tip` enum('1','2') NOT NULL default '1',
  `ptip1` varchar(4) NOT NULL default '',
  `ptip2` varchar(4) NOT NULL default '',
  `ptip3` varchar(4) NOT NULL default '',
  `depth4` tinyint(3) unsigned NOT NULL default '0',
  `naziv` varchar(20) NOT NULL default '',
  `img` mediumblob,
  `comment` longtext,
  PRIMARY KEY  (`rbr`),
  KEY `indtip` (`tip`),
  KEY `indp1` (`ptip1`),
  KEY `indp2` (`ptip2`),
  KEY `indp3` (`ptip3`),
  KEY `inddepth4` (`depth4`),
  KEY `indNaziv` (`naziv`),
  KEY `pInd1` (`tip`,`ptip1`),
  KEY `pInd2` (`tip`,`ptip1`,`ptip2`),
  KEY `pInd3` (`tip`,`ptip1`,`ptip2`,`ptip3`),
  KEY `fullInd` (`tip`,`ptip1`,`ptip2`,`ptip3`,`depth4`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Synopsis:
UPDATE table.blobfield SET... doesn't use proper PK thus failing to update leaving default BLOB value which is what reporter saw.
Snip from attached log file:
060615 12:56:46	      1 Query       INSERT INTO `dbimg`.`bigtbl1test` (`tip`,`img`) VALUES ('1','<placeholder>')
		      1 Query       SET NAMES binary
		      1 Query       UPDATE `dbimg`.`bigtbl1test` SET `img`='˙Ř˙ŕ\0JFIF\0\0\0\0\0\0\0˙í\0Photoshop 3.0\08BIM\0\0\0\0\0\0˙Ű\0C\0
...
\0˙Ů' WHERE (nil)
However, MySQL Querybrowser do issue an error visible at the bottom so we know picture didn't get to table.
This should be S1 or S2 since I can't find workaround.

General log showing the error

Attachment: 18937-munja.zip (application/x-zip-compressed, text), 4.05 KiB.

Changing status to more appropriate one. Synopsis is missleading. It should read: Unable to update BLOB field via MySQL Querybrowser 1.1.20.

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