MySQL Bugs: #63867: Unable to SELECT * from table without PRIMARY KEY defined

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

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

Please try new version 5.2.37 and check for duplicate http://bugs.mysql.com/bug.php?id=63845. Thanks.

The same thing with 5.2.37

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)

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.

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.

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`)
);

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.

The problem occurs with tables with no PK but does have UNIQUE KEYs

bug #63949 is a duplicate

A workaround is to pass a function to the column list, like
SELECT concat('work','around'), col1, col2 ... FROM table_with_uniq_key

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;

bug #64117 is a duplicate

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.