Bug #63867 Unable to SELECT * from table without PRIMARY KEY defined
Submitted: 28 Dec 2011 15:19 Modified: 3 Feb 2012 2:14
Reporter: Oleg Preobrazhenskyy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S1 (Critical)
Version:5.2.36, 5.2.37 OS:Any
Assigned to: CPU Architecture:Any

[28 Dec 2011 15:19] Oleg Preobrazhenskyy
Description:
5	17:14:40	SELECT * FROM users_open_hours LIMIT 0, 1000	Fetching...

0	17:14:40	SELECT * FROM users_open_hours LIMIT 0, 1000	Error: `trunk`.`ilance_users_open_hours`: table data is not editable because there is no primary key defined for the table

How to repeat:
create any table with no PK defined.
and use SELECT * in sql editor
[28 Dec 2011 15:45] MySQL Verification Team
Please try new version 5.2.37 and check for duplicate http://bugs.mysql.com/bug.php?id=63845. Thanks.
[28 Dec 2011 17:18] Oleg Preobrazhenskyy
The same thing with 5.2.37
[28 Dec 2011 17:42] Valeriy Kravchuk
Please, send the output of:

show create table users_open_hours\G

I can edit this table, for example:

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
[28 Dec 2011 17:58] Oleg Preobrazhenskyy
Thanks but the design of this table is exactly what do we need.
No PK defined right for our application goals.

Instead of I would be happy enough if MySQL Workbench team will resolve the error listed in topic.
[28 Dec 2011 18:17] Valeriy Kravchuk
For Workbench developers to fix something a repeatable test case is needed. 

I can SELECT * (or Edit Data...) from simple table (like the one I presented in my previous comment) without PRIMARY KEY. Please, check if it works with that table in your case. If it does, we do really need exact CREATE TABLE for the problematic table.
[28 Dec 2011 18:32] Oleg Preobrazhenskyy
Any table without PRIMARY KEY is suitable.
If you want mine, please:

CREATE TABLE `users_open_hours` (
  `user_id` int(11) NOT NULL DEFAULT -1,
  `day` int(11) NOT NULL DEFAULT 0,
  `from` varchar(7) NOT NULL DEFAULT 9,
  `to` varchar(7) NOT NULL DEFAULT 18,
  `closed` tinyint(4) NOT NULL DEFAULT '0',
  UNIQUE KEY `user_day` (`user_id`,`day`),
  KEY `user_id` (`user_id`),
  KEY `day` (`day`)
);
[28 Dec 2011 19:07] Valeriy Kravchuk
While it works with my table (that has no keys), it does not work with yours. I get exactly the same Action Output messages you had reported initially. This is a bug.
[28 Dec 2011 19:13] Alfredo Kojima
The problem occurs with tables with no PK but does have UNIQUE KEYs
[11 Jan 2012 18:17] Alfredo Kojima
bug #63949 is a duplicate
[18 Jan 2012 16:49] Alfredo Kojima
A workaround is to pass a function to the column list, like
SELECT concat('work','around'), col1, col2 ... FROM table_with_uniq_key
[18 Jan 2012 17:02] Brandon Johnson
More details:

This only occurs when a table has no primary key, but at least one NOT NULL unique key (the 1st NOT NULL unique key is where the error will occur). This occurs in all table engines.

Queries for Proof of concept:

-- Create the table. It doesn't have to have data to prove this concept.
CREATE TABLE testid(id int unsigned NOT NULL default 0, 
                    id2 int unsigned default 0, 
                    value varchar(255), 
                    value2 varchar(255), 
                    UNIQUE(id), 
                    UNIQUE(id2));

-- This query errors.
select * from testid;

-- This query works.
select id2, value, value2 from testid;

-- This query errors.
select id from testid;

Change the create table statement around a bit:

-- Create the table. It doesn't have to have data to prove this concept.
CREATE TABLE testid(id int unsigned default 0, 
                    id2 int unsigned NOT NULL default 0, 
                    value varchar(255), 
                    value2 varchar(255), 
                    UNIQUE(id), 
                    UNIQUE(id2));

-- This query errors.
select * from testid;

-- This query errors.
select id2, value, value2 from testid;

-- This query now succeeds, as it's dying on id2.
select id from testid;
[24 Jan 2012 2:17] Alfredo Kojima
bug #64117 is a duplicate
[3 Feb 2012 2:14] Philip Olson
Fixed as of 5.2.38:

Workbench would freeze when a "SELECT" statement was executed on a table without private keys, and that included at least one "UNIQUE NOT NULL" column.