MySQL Bugs: #27657: Version 1.2.11 (GA) of MySQL-Query-Browser cannot read some field data properly.

Bug #27657	Version 1.2.11 (GA) of MySQL-Query-Browser cannot read some field data properly.
Submitted:	4 Apr 2007 20:19	Modified:	18 Dec 2008 12:31
Reporter:	Aaron Freed	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S4 (Feature request)
Version:	1.2.11 (General Availability)	OS:	Linux (CentOS 4.4 with updates as of 4/4/2007)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	"Cannot read binary data as text", "MySQL-Query_Browser", 3.1, 6.02, beta, Browser, bug, check, compiled, corrupt, data, database, error, file, formatted NaviCat, head, indented, instructions, libgtkhtml, MySQL, MySQL, problem, self, tabbed, table, table, test, text

Description:
When compiled from scratch using the sources (tarball) downloaded from the MySQL Community pages on a CentOS 4.4 (all updates current as of date this report lodged) system and then compiled on the same system with the version of GCC supplied with CentOS 4.4, using the instructions at http://forge.mysql.com/wiki/Building_MySQL_GUI_Tools_on_Linux

the program compiles perfectly, but some data (apparently that which is indented in the first line, though not sure that it is only this type) is misidentified as "binary data". In the table-view, the first line of the text in the field is properly displayed as expected, but attempts to view or edit the data in the pop-up text viewer result in the message "Cannot display binary data as text" being displayed rather than the expected data.

Testing with both NaviCat 6.02 for Linux and MySQL-Query-Browser 1.2.3Beta results in the correct and expected display of the text.
Testing with MySQL-Query-Browser 1.2.9rc for Windows also worked properly.

NOTE: I was forced to compile MySQL-GUI-Tools 1.2.11GA from sources because the provided RPM does not install--it does not recognize the "ld-linux.so.2" file installed on CentOS and insistently demands that "ld-linux-so.2(PRIVATE_CONFIGURATION) (or something similar) be installed first, but this particular dependency seems to be irresolvable.

How to repeat:
Simply run MySQL-Query-Browser (compiled from sources as detailed above). Bug will occur every time.

Suggested fix:
Uninstall 1.2.11 and reinstall 1.2.3beta. At least that works (mostly--e.g. the ability to build a view using drag-and-drop really doesn't work right, but that's the only bug I found so far.)

Thank you for the report.

Please provide output of SHOW CREATE TABLE for table you get message "Cannot display binary data as text"

mysql> show create table Howtos_Original
    -> ;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                  |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Howtos_Original | CREATE TABLE `Howtos_Original` (
  `Record ID` int(10) unsigned NOT NULL auto_increment,
  `Copyrighted` enum('Yes','No') NOT NULL default 'No',
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Category` enum('Linux','Windows','Cluster','OSX','Solaris','Broken-Pls Research','Fix Needed','URGENT','Boss Request!','Other','MySQL','PHP','C/C++','Basic/GAMBAS','Shell','Perl','MS Exchange','MS Active Directory','MS SQL','Firewall','Antivirus','Antispam','Email','TEST','TEST-FIX','Submit Bug Report','Review','Pending','Misc','Reported bug','Bug Pending Fix','Bug Found','DNS','WINS','DHCP','TCP/IP','NIS/NFS','Mac OS/X','Browser/Opera','Browser/Mozilla','Browser/NetScrape','VNC/TightVNC','YUM/Up2Date','AptGet/AptGetforRPM/SynAPTic','Email/Outlook','Email/Other','Email/Thunderbird','Email/Evolution','Printing/Queues/PrintServer','Rights/Security/Access','AntiSpam','AntiVirus','AntiSpyware','Scanner','backup','Application','ssh/rsh/telnet','XWindow','yum/apt-get/pkgadd','Network Security','Network','VMware','SGE5.3','SGE6.08') NOT NULL default 'Linux',
  `Keywords` text,
  `Title` text,
  `Problem` longtext,
  `Resolution` longtext, <====It is often this field misidentified as Binary...
  `Exhibit_1` longtext,
  `Exhibit_2` longtext,
  `Exhibit_3` longtext,
  `HTML` longtext,
  `Binary` longblob,
  PRIMARY KEY  (`Record ID`),
  FULLTEXT KEY `Keyword Search` (`Keywords`),
  FULLTEXT KEY `Full Text` (`Problem`,`Resolution`,`Title`)
) ENGINE=MyISAM AUTO_INCREMENT=294 DEFAULT CHARSET=latin1 CHECKSUM=1 |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thank you for the feedback.

Please find `Record ID` of one of rows where `Resolution` is not displayed correct, when send us output of SELECT `Resolution` FROM `Howtos_Original` WHERE `Record ID`=recorded_record_id

File containing data that MySQL-Query-Browser thinks is "binary"

Attachment: broken265 (application/octet-stream, text), 10.63 KiB.

Data submitted as "Field Data that MySQL-Query-Browser thinks is binary" is a file containing the result of  

SELECT `Resolution` FROM `Howtos_Original` WHERE `Record_ID`=265;

run from the mysql text client as per the request of Sveta Smirnova.

Thank you for your help with this!

Thank you for the content.

Verified as described.

I could upload the entire database for your review if you wish, but it is large.  The tarball weighs in at 1474962 bytes, larger than your file upload limit....

Please keep me posted as to the disposition of this bug.  I am glad that you were able to duplicate it and quite curious to know what is causing it..... (Something to do with the record content?  Leading white-space?  White-space in the title?  "illegal" characters?  Something of the sort, since not all records are affected.  (Most of thos affected have rather a lot of content.  Record ID 265 was one of the shortest ones I could find to upload.

Aaron,

Looking at the data you submitted, I see it has some illegal characters (0xffff).
That cannot be displayed as UTF8, so the QB is falling it back to binary.
If you correct these bad characters, it should be able to display your data correctly.

Additionally, I've changed the code for the field editor to check for UTF8 characters (instead of ascii).

So that specific row is now displayed although with broken chars, but still, if 
some row invalid UTF8 sequences, it will not be displayed.

In response to:
[27 Apr 16:44] Alfredo Kojima

>>Additionally, I've changed the code for the field editor to check for UTF8
>>characters (instead of ascii).

>>So that specific row is now displayed although with broken chars, but still, if

>>some row invalid UTF8 sequences, it will not be displayed.

Version 1.2.3 beta of QB (MySQL-Query-Browser) had no problem displaying the field content, so it seems odd that 1.2.11 (production release) does.  The average consumer probably doesn't know what characters are legal and which ones are not, let alone what "UTF8" is.  In any event, simply reporting that it cannot display "binary" data is unhelpful to say the least.  How would the average user know to go back and edit the data to remove the invalid characters that are causing the row to be misinterpreted as binary?  And, how does the user actually go and edit the data, since exporting the data does not reproduce the illegal characters, and MySQL Query Browser does not allow the user to even see the contents of the row, let alone edit them.

I am glad you changed your filter to check for UTF8 characters, but I have to wonder if that really is "enough".  Perhaps the filters from version 1.2.3 beta of MySQL-Query-Browser are the solution to the problem.  Alternatively, a bit of code that pops up a dialog saying "Illegal UTF8 characters were found in this record.  Would you like to open the editor to remove them manually, or shall I strip them out for you?" and that actually allows one or the other remedial action to be performed would be far more appropriate, and, I should think, not significantly more difficult than implementing the "ASCII-only"/"UTF8-only" filter was--it's just a matter of offering the user the use of the editor to correct their own mistakes, rather than throwing a meaningless and misleading error that suggests that MySQL-Query-Browser has suddenly developed a random inability to differentiate between binary and ASCII data.

I'd be willing to give the new UTF8-patched version of MySQL-Query-Browser a try if pointed to its location and assured that it can be installed on CentOS 4.4 without encountering the irresolvable dependency errors of the origional production (GA) release.

Thank you, in advance, for your kind attention to this matter!

In response to:
[27 Apr 16:44] Alfredo Kojima

>>Additionally, I've changed the code for the field editor to check for UTF8
>>characters (instead of ascii).

>>So that specific row is now displayed although with broken chars, but still, if

>>some row invalid UTF8 sequences, it will not be displayed.

Version 1.2.3 beta of QB (MySQL-Query-Browser) had no problem displaying the field content, so it seems odd that 1.2.11 (production release) does.  The average consumer probably doesn't know what characters are legal and which ones are not, let alone what "UTF8" is.  In any event, simply reporting that it cannot display "binary" data is unhelpful to say the least.  How would the average user know to go back and edit the data to remove the invalid characters that are causing the row to be misinterpreted as binary?  And, how does the user actually go and edit the data, since exporting the data does not reproduce the illegal characters, and MySQL Query Browser does not allow the user to even see the contents of the row, let alone edit them.

I am glad you changed your filter to check for UTF8 characters, but I have to wonder if that really is "enough".  Perhaps the filters from version 1.2.3 beta of MySQL-Query-Browser are the solution to the problem.  Alternatively, a bit of code that pops up a dialog saying "Illegal UTF8 characters were found in this record.  Would you like to open the editor to remove them manually, or shall I strip them out for you?" and that actually allows one or the other remedial action to be performed would be far more appropriate, and, I should think, not significantly more difficult than implementing the "ASCII-only"/"UTF8-only" filter was--it's just a matter of offering the user the use of the editor to correct their own mistakes, rather than throwing a meaningless and misleading error that suggests that MySQL-Query-Browser has suddenly developed a random inability to differentiate between binary and ASCII data.

I'd be willing to give the new UTF8-patched version of MySQL-Query-Browser a try if pointed to its location and assured that it can be installed on CentOS 4.4 without encountering the irresolvable dependency errors of the origional production (GA) release.

Thank you, in advance, for your kind attention to this matter!

Many thanks for writing a bug report. GUI tools on Linux are not supported. You will find more informations here:
http://www.mysql.com/support/supportedplatforms/tools.html