Table is 'read only'

Question

When I want to execute an update query on my table I got an error saying:

1036 - Table data is read only.

How can I fix that?

Table attributes in /var/db/mysql are set to 777.

'Repair Table' function doesnt seems to help.

Is there anything I can do with that?

I had the same error in MaridDB 10.6 for table with ROW_FORMAT=COMPRESSED After removing this option all works as exprected — M-A-X, Nov 06 '21 at 14:33

score 38 · Answer 1 · edited May 12 '23 at 10:46

38

In my case, mysql config file had innodb_force_recovery = 1. Commenting that out solved the issue.

edited May 12 '23 at 10:46

Dharman

30,962
25
85
135

answered Nov 04 '16 at 12:44

Rahi

1,435
1
12
20

innodb_force_recovery is issue, comment this line or remove it – Thoman Dec 18 '16 at 11:08
1

In /etc/my.cnf I set: innodb_file_per_table=0 and innodb_force_recovery=0. then, restart mysql service and finally it works! Thanks – atwebceo Jun 07 '17 at 02:16
But the service mariadb doesn't start without `innodb_force_recovery = 5` in my case, but I have readonly tables for `mysql_upgrade` which needs to be connected to running service. It sounds like a dead-lock. – Ariel Bogdziewicz Jun 02 '23 at 05:43

score 33 · Accepted Answer · answered Mar 05 '12 at 23:55

33

who owns /var/db/mysql and what group are they in, should be mysql:mysql. you'll also need to restart mysql for changes to take affect

also check that the currently logged in user had GRANT access to update

answered Mar 05 '12 at 23:55

ncremins

9,140
2
25
24

Not restarting the MySQL server after changing permissions was my issue. Thanks. – Behnam Mar 24 '22 at 21:19

score 10 · Answer 3 · edited May 12 '23 at 10:47

10

In case you (like me) are trying to temporarily alter data via the MySQL Workbench interface:

If the table does not have a primary key, MySQL Workbench has no way of identifying the row you are trying to alter, so therefore you cannot alter it.
Solution in that case is to either alter the data via another route, or simply to add a primary key to the table.

edited May 12 '23 at 10:47

Dharman

30,962
25
85
135

answered Mar 07 '17 at 12:23

Fom

485
6
14

score 6 · Answer 4 · edited May 06 '14 at 14:12

6

You should change owner to MYSQL:MYSQL.

Use this command: chown -Rf mysql:mysql /var/lib/mysql/DBNAME

edited May 06 '14 at 14:12

Tim Visée

2,988
4
45
55

answered Oct 14 '12 at 10:41

kamranonline

315
1
5
14

score 3 · Answer 5 · edited Jun 20 '20 at 09:12

My situation is everytime I needed to edit "innodb_force_recovery = 1" in my.ini to force mysql to start, and the error log showed some error said:

Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace profile/profile_commentmeta which uses space ID: 1 at filepath: .\profile\profile_commentmeta.ibd

I didn't know why this file was not able to open and it caused so many other"table read only" problems to other databases too.

So here is how I fixed this problem in a simple way without hurting other files.

1 First of all, make sure if you add innodb_force_recovery = 1 below [mysqld] in my.ini file, and it is working, under path: X:\xampp\mysql\bin\my.ini

2 Then next step, export all the databases through localhost/phpmyadmin under the export tab, and store them somewhere, like this:

3 comment out the data filefolder to data-bak, then create a new data filefolder,

4 Next step, import all .sql database back from phpmyadmin panel, please also copy phpmyadmin filefolder from the old data-bak filefolder to the new data filefolder. If any file is necessary, go back to data-bak filefolder to copy and paste.

Now all fixed and done, don't need to force mysql to start everytime. Hope this also works for you.

score 1 · Answer 6 · answered Mar 05 '12 at 23:54

1

MySQL doesn't have write access to the database file. Check the permissions and the owner of the file.

answered Mar 05 '12 at 23:54

Borodin

126,100
9
70
144

if you change the permissions manually don't forget to issue a FLUSH PRIVILEGES command. – Jody Mar 05 '12 at 23:57

score 0 · Answer 7 · answered Apr 27 '19 at 03:40

0

On windows I use Xampp server I comment the line in my.ini innodb_force_recovery = 1 to #innodb_force_recovery = 1 the problem resolved

answered Apr 27 '19 at 03:40

vpgodara

329
2
2

score 0 · Answer 8 · answered Jun 03 '19 at 18:44

0

I solved the same issue by editing app. armour configuration file. Found the answer here: https://stackoverflow.com/a/14563327/3175566 1

answered Jun 03 '19 at 18:44

eddy85br

284
4
12

score 0 · Answer 9 · answered Sep 21 '20 at 19:53

0

maybe you get read only error from your table storage engine.
Check you Storage Engine, maybe if it is MRG_MYISAM change it to MyISAM and try again.

answered Sep 21 '20 at 19:53

Rahman Pampal

1

score 0 · Answer 10 · answered Mar 24 '22 at 19:15

If you are running selinux in enforcing mode then check your /var/log/messages for audit faults. If you see the tell-tale "****" messages about selinux blocking write access to your table files in / then you need to relabel those files so that they have this label:

system_u:object_r:mysqld_db_t:s0

What you could have is a broken label from copying those files over from a user directory (such as during a recovery attempt).

There's a great resource for selinux here:

https://docs.fedoraproject.org/en-US/Fedora/11/html/Security-Enhanced_Linux/sect-Security-Enhanced_Linux-SELinux_Contexts_Labeling_Files-Persistent_Changes_semanage_fcontext.html

Just remember that you will need to do this for all of those files, which could be many. Then you will want to run the "restorecon -R -v " command to get the recursive (-R) application of the new labels. There is no support for -R in the semanage command, as far as I could tell.

For reference, the semanage command to relabel looks like this:

semanage fcontext -a -t mysqld_db_t 'filename'

The quoting of the file name is critical for the command to work.

score 0 · Answer 11 · answered Jun 09 '22 at 11:34

In my case there was a table with read-only state set and when I tried to restart mysql service it would not even start again and with no descriptive error.

Solution was to run fsck on the drive (with many fixes), which was advised after Ubuntu reboot.

I'm running Ubuntu in VirtualBox under Windows and it often hangs or is having functionality problems.

score -1 · Answer 12 · answered Sep 19 '13 at 09:14

One other way to receive this error is to create your table with a "Like" statement and use as source a merged table. That way the newly create table is read-only and can't "receive" any new records.

so

CREATE TABLE ic.icdrs_kw37 LIKE ic.icdrs ... #<- a merged table.

then:

REPLACE INTO ic.icdrs_kw37 ...  # -> "Table is read-only"

bug or feature?

Table is 'read only'

12 Answers12

Linked

Related