MySQL Workbench Edit Table Data is read only

Question

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

User · Answer

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

User · Answer

if the table does not have primary key or unique non-nullable defined  then MySql workbench could not able to edit the data

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

According to this bug  the issue was fixed in Workbench 5 2 38 for some people and perhaps 5 2 39 for others mdash can you upgrade to the latest version  5 2 40    Alternatively  it is possible to workaround with   SELECT      FROM my table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Uncheck the marked check  it will enable the grid edit

User · Answer

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

[mysql] MySQL Workbench Edit Table Data is read only

Examples related to mysql

Examples related to mysql-workbench