Bug #64061 SELECT with a varchar remains in fetching... state forever, no results returned
Submitted: 18 Jan 2012 16:45 Modified: 26 Jan 2012 4:34
Reporter: David Frankenbach Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:5.2.37 CE OS:Windows (Win XP)
Assigned to: CPU Architecture:Any
Tags: SELECT, varchar

[18 Jan 2012 16:45] David Frankenbach
Description:
select count(*) from phrases where i_id = 41436966 
-- has a duration of 0.188 sec and a fetch of 0.000 seconds 
-- result count was 149 rows 
-- the table is pretty narrow, 1 int(11) PK, 6 tinyint fields and one varchar(50) PK field 

As soon as I try to look at the rows themselves: 

select * from phrases where i_id = 41436966 

the duration is 0.782 sec and the fetch displays as ? forever and the icon on the row remains as a blue donut the whole time no rows of data ever appear. 

I get an immediate "Error: 'dbname' 'phrases' table data is not editable because there is no primary key for the table. I don't intend to edit the data in the result grid anyway so I went to the options and unchecked the feature. I reran the SQL and got the same error message along with the same infinite fetch time. 

How to repeat:
The table definition is:

delimiter $$

CREATE TABLE `phrases` (
  `i_id` int(11) NOT NULL,
  `natt` tinyint(4) NOT NULL,
  `ncust` tinyint(4) NOT NULL,
  `nlive` tinyint(4) NOT NULL,
  `nstaf` tinyint(4) NOT NULL,
  `nsubj` tinyint(4) NOT NULL,
  `word` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `dormant` tinyint(4) NOT NULL,
  UNIQUE KEY `phrases$word$i_id` (`word`,`i_id`),
  KEY `phrases$i_id` (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

Here's some test queries and the duration/fetch timings 

select count(*) from phrases where i_id = 41436966 
-- 0.046/0.000 
select i_id, natt from phrases where i_id = 41436966 
-- 0.125/0.000 
select i_id, natt, ncust, nlive from phrases where i_id = 41436966 
-- 0.063/0.000 
select i_id, natt, ncust, nlive, nstaf, nsubj, dormant from phrases where i_id = 41436966 
-- 0.109/0.000 
select i_id, natt, ncust, nlive, nstaf, nsubj, dormant, word from phrases where i_id = 41436966 
-- 0.125/? 

Once the varchar(50) field word is added the resultset never returns.

Suggested fix:
Allow all fields of a table to be in a result set.
[18 Jan 2012 18:41] David Frankenbach
The query works in this release http://downloads.mysql.com/archives/MySQLGUITools/mysql-workbench-oss-5.2.21-rc-win32.msi
[25 Jan 2012 20:48] Rafael Antonio Bedoy Torres
Hello David,
Thanks for your Report, may you please provide more information about your system?
Please On Workbench click on Help-> System info and paste the info you got as a comment.

Thank you in advance!
[25 Jan 2012 21:30] David Frankenbach
Rafael - I had to drop back to an older release of the workbench so that I could get the data analysis done. Here's the system info from a release where the queries do work correctly.

MySQL Workbench OSS for Windows version 5.2.21
Cairo Version: 1.8.8
Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.
OpenGL Driver Version: 2.1.2
OS: Microsoft Windows XP Professional Service Pack 3 (build 2600)
CPU: 2x Intel(R) Core(TM)2 CPU          6400  @ 2.13GHz, 3.0 GiB RAM
Video adapter info:
Adapter type: NVIDIA GeForce 7900 GS
Chip Type: GeForce 7900 GS
BIOS String: Version 5.71.22.29.09
Video Memory: 262144 KB
[26 Jan 2012 4:34] Alfredo Kojima
Duplicate of bug #63867
[26 Jan 2012 4:37] Alfredo Kojima
If you are willing, please try the following workaround and report back. When selecting from a table that gives the error, add a dummy function to the select list, like:
select i_id, natt, ncust, nlive, nstaf, nsubj, dormant, word, concat('', '') from phrases where i_id =
41436966