MySQL Bugs: #63845: Cannot query data in table without primary key

Bug #63845	Cannot query data in table without primary key
Submitted:	22 Dec 2011 23:21	Modified:	27 Dec 2011 15:09
Reporter:	Bob Dankert	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	5.2.36	OS:	Windows (W7 64)
Assigned to:		CPU Architecture:	Any
Tags:	no primary key, query table

Description:
When I try to query data from a table without a primary key, it gives me an error that there is no primary key and the table is not editable.  Exact details in "How to repeat"

How to repeat:
If I eecute these three commands in the sql editor:

  create table test (a int);
  insert into test (a) values (1),(2),(3);
  select * from test;

I get the following two rows in the output tab below, and never get the results that I inserted.  

first row:
  icon = blue circle
  action = select * from test LIMI 0, 5000
  message = Fetching...

second row:
  icon = red x
  action = select * from text LIMI 0, 5000
  message = Error:  `dbname`.`test`: table data is not editable because there is no primary key defined for the table

Suggested fix:
You should be able to query records from a table without a primary key.  The table may not be editable, but you should at least be able to query it.

Also, in case this helps, here's the output of a show create table for the test table I created:

show create table test;

'test', 'CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

query

Attachment: select_rows.png (image/png, text), 129.92 KiB.

Please prior attached picture. Thanks.

MySQL Workbench CE for Windows version 5.2.36  revision 8542

Configuration Directory: C:\Users\miguel\AppData\Roaming\MySQL\Workbench

Data Directory: C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE

Cairo Version: 1.8.8

Rendering Mode: OpenGL is available on this system, so OpenGL is used for rendering.

OpenGL Driver Version: 3.3.11318 Compatibility Profile Context

OS: Microsoft Windows 7 Ultimate Edition Service Pack 1 (build 7601), 64-bit

CPU: 4x AMD Phenom(tm) 9650 Quad-Core Processor, 8.0 GiB RAM

Active video adapter (0): ATI Radeon HD 4650

Installed video RAM: 1024 MB

Current video mode: 1920 x 1080 x 4294967296 colors

Used bit depth: 32

Driver version: 8.920.0.0

Installed display drivers: aticfx64.dll,aticfx64.dll,aticfx32,aticfx32,atiumd64.dll,atidxx64.dll,atiumdag,atidxx32,atiumdva,atiumd6a.cap,atitmm64.dll

Current user language: Portuguese (Brazil)

I cannot repeat this bug today either.  When I submitted it, it was happening over and over.  If I am able to figure out what needs to exist to allow it to replicate again, I will update this post.

I'm experiencing the same issue. However, when I create the table as described above, it seems to be fine.

I'm trying to access a table from the Bugzilla database. This is a regular Bugzilla 4.0.4 installation. The specific table I'm trying to access is the bugs.cc table. This table also does not have any primary keys as it consists of two foreign keys only. Running the same query with phpMyAdmin shows results with no issues.

Here is the table:

DESCRIBE cc;

	bug_id	mediumint(9)	NO	PRI		
	who	mediumint(9)	NO	PRI		

SHOW CREATE TABLE cc;

	cc	CREATE TABLE `cc` (
   `bug_id` mediumint(9) NOT NULL,
   `who` mediumint(9) NOT NULL,
   UNIQUE KEY `cc_bug_id_idx` (`bug_id`,`who`),
   KEY `cc_who_idx` (`who`),
   CONSTRAINT `fk_cc_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON...

I'm running the MySQL server from a CentOS 5.7 VM. MySQL Workbench from a Windows 7 x64 VM.

I've tried restarting both the CentOS and Windows VMs already with no luck.

Any one else?

Found a work around that seems to work from another reported bug for Linux installations:

http://bugs.mysql.com/62893