MySQL Workbench Edit Table Data is read only
MysqlMysql WorkbenchMysql Problem Overview
When trying Edit Table Data in MySQL Workbench 5.2.37, its in read only mode.
It is editable only if the table has a primary key.
Is there any fix to deal with table without primary key??
Thanks
As one of the suggestion I tried upgrading WB 5.2.40. But still this issue exists.. Could any one Help please..
Mysql Solutions
Solution 1 - Mysql
I'm assuming the table has a primary key. First try to run a unlock tables command to see if that fixes it.
If all else fails you can alter the table to create a new primary key column with auto-increment and that should hopefully fix it. Once you're done you should be able to remove the column without any issues.
As always you want to make a backup before altering tables around. :)
Note: MySQL workbench cannot work without a primary key if that's your issue. However if you have a many to many table you can set both columns as primary keys which will let you edit the data.
Solution 2 - Mysql
if the table does not have primary key or unique non-nullable defined, then MySql workbench could not able to edit the data.
Solution 3 - Mysql
If you set a default schema for your DB Connection then Select will run in readonly mode until you set explicitly your schema
USE mydb;
SELECT * FROM mytable
this will also run in edit mode:
SELECT * FROM mydb.mytable
(MySql 5.2.42 / MacOsX)
I hope this helps.
Solution 4 - Mysql
This is the Known limitation in MySQLWorkbench (you can't edit table w/o PK):
To Edit the Table:
Method 1: (method not working in somecases)
right-click on a table within the Object Browser and choose the Edit Table Data option from there.
Method 2:
I would rather suggest you to add Primary Key Instead:
ALTER TABLE `your_table_name` ADD PRIMARY KEY (`column_name`);
and you might want to remove the existing rows first:
Truncate table your_table_name
Solution 5 - Mysql
Hovering over the icon "read only" in mysql workbench shows a tooltip that explains why it cannot be edited. In my case it said, only tables with primary keys or unique non-nullable columns can be edited.
Solution 6 - Mysql
I was getting the read-only problem even when I was selecting the primary key. I eventually figured out it was a casing problem. Apparently the PK column must be cased the same as defined in the table. using: workbench 6.3 on windows
Read-Only
SELECT leadid,firstname,lastname,datecreated FROM lead;
Allowed edit
SELECT LeadID,firstname,lastname,datecreated FROM lead;
Solution 7 - Mysql
In MySQL Workbench you need an INDEX to edit, no need it to be PK (although adding a PK is a solution as well).
You can make a regular INDEX or compound INDEX. That's all MySQL WB needs to fix the Read only thing (I have v. 6.2 with MariaDB v. 10.1.4):
Just right click table, select "Alter table..." then go to "Indexes" tab. In the left pane put a custom name for your index, and in the middle pane checkmark one (make sure the vale will be unique) or more fields (just make sure the combination is unique)
Solution 8 - Mysql
According to this bug, the issue was fixed in Workbench 5.2.38 for some people and perhaps 5.2.39 for others—can you upgrade to the latest version (5.2.40)?
Alternatively, it is possible to workaround with:
SELECT *,'' FROM my_table
Solution 9 - Mysql
If your table is in read-only mode, checking first is the primary key. If the primary key is not defined, add as the following,
> ALTER TABLE scema_here
.table_name_here ADD PRIMARY KEY (your primary key here
);
Cheers ! :)
Solution 10 - Mysql
Guided by Manitoba's post, I found another solution. As a summary, the solutions are:
-
With a USE command
USE mydb; SELECT * FROM mytable
-
With an explicit schema prefix:
SELECT * FROM mydb.mytable
-
GUI
On Object Browser "SCHEMAS" pane, all database icons are initially not highlighted if you have the same issue. So you can right click on the database icon you wanted to be the default, select "Set as default schema".
Solution 11 - Mysql
1.)You have to make the primary key unique, then you should be able to edit.
right click on you table in the "blue" schemas ->ALTER TABLE, look for your primert key (PK), then just check the check-box, UN, the AI should already be checked. After that just apply and you should be able to edit the table data.
2.)You also need to include the primery key I your select statement
Nr 1 is not really necessary, but a good practice.
Solution 12 - Mysql
If your query has any JOINs, Mysql Workbench will not allow you to alter the table, even if your results are all from a single table.
For example, the following query
SELECT u.* FROM users u JOIN passwords p ON u.id=p.user_id WHERE p.password IS NULL;
will not allow you to edit the results or add rows, even though the results are limited to one table. You must specifically do something like:
SELECT * FROM users WHERE id=1012;
and then you can edit the row and add rows to the table.
Solution 13 - Mysql
MySQL will run in Read-Only mode when you fetch by joining two tables and columns from two tables are included in the result. Then you can't update the values directly.
Solution 14 - Mysql
Yes, I found MySQL also cannot edit result tables. Usually results tables joining other tables don't have primary keys. I heard other suggested put the result table in another table, but the better solution is to use Dbeaver which can edit result tables.