Fixing MySQL error 1036: table is read only

Recently I undertook formatting my hard drive, and therefore had to do some backups and restoring of my data including my local mysql databases.

For this I used rsync to back up my /var directory. The Problem is that the drive storeing the backed up files was running a FAT32 partion. This means all file owner ship was lost.

When I restored my files I was not able to access mysql databses getting the 1036 error.

After a few searches I found this article. It had a reason but no fix for people. So here is my extention of that article.

To see the problem

From the command line on a standard *nix install one can view who owns the files: by typing:

ls -l /var/lib/mysql

your results might look like:

total 20544-rw-r--r-- 1 mysql mysql        0 2010-05-02 15:57 debian-5.1.flag
drwxr-xr-x 2 root root	    12288 2010-05-04 15:03 drupal
-rw-rw---- 1 mysql mysql        6 2010-05-04 15:04 dublin.pid
-rw-rw---- 1 mysql mysql      125 2010-05-03 12:52 dublin-relay-bin.000001
-rw-rw---- 1 mysql mysql      125 2010-05-04 15:04 dublin-relay-bin.000002
-rw-rw---- 1 mysql mysql      106 2010-05-04 15:04 dublin-relay-bin.000003
-rw-rw---- 1 mysql mysql       78 2010-05-04 15:04 dublin-relay-bin.index
-rw-rw---- 1 mysql mysql 10485760 2010-05-04 15:04 ibdata1
-rw-rw---- 1 mysql mysql  5242880 2010-05-04 15:04 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2010-05-02 15:57 ib_logfile1
-rw-rw---- 1 mysql mysql       30 2010-05-04 15:04 master.info
drwxr-xr-x 2 root root	     4096 2010-05-04 15:03 mysql
-rw-rw---- 1 mysql mysql        6 2010-05-02 15:57 mysql_upgrade_info
-rw-rw---- 1 mysql mysql       31 2010-05-04 15:04 relay-log.info
drwxr-xr-x 2 root root	    16384 2010-05-04 15:03 zc139

If in some of the output of the, ls -l has the pattern "root root", this means that the unix user root owns these files and that mysql can not modify them. This is what is causeing the Read Only issue.

The Fix:

One needs super user privs to do this, most commonly sudo is uesed to acheve this.

To Change the owner of the files.

sudo chown -R mysql:mysql /var/lib/mysql 

Reboot Mysql so that the changes are detected.

sudo service mysql restart

 


From there it should work.

 

 

 

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
1 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Recent Tweets

#regex always feels more like real "code" then the languages themselves — 3 days 18 hours ago
manually splicing out a #drupal #features module as I over packed one and not I need to simplify them — 3 days 21 hours ago
@weyakin just saw that you are over @skytemplemedia now. I hope thy know what an awesome designer the have now! — 3 days 23 hours ago
@weyakin I could make you one — 4 days 13 hours ago
@tempus I have not gone back. The Android app even comes with a option to tunnel your searches through the TOR network — 4 days 14 hours ago
@cloudhead I just registered the freenode Chanel #lesscss perhaps it should be added to the site for support options — 5 days 21 hours ago
just installed #memcahed on a small cloud instance running a #views heavy #drupal site. went from 20 seconds page loads to 2 mins. — 5 days 21 hours ago
@JacobSingh Its not so un common. They want the masthead to be the title. The rest is sportive snippets for the rest of the site. — 1 week 20 hours ago
what do people use for #Drupal performance #testing? #DevOps — 1 week 1 day ago
@geekgirlweb but us programmers tend to be shite at design, we Don't know good or bad other then what we have been told by designers. #notme — 1 week 1 day ago

©2009 Sanguis Development. 462 North King Street, Northampton MA 01060
Phone: (413) 570-0659

AIM: joshbeaureg || Skype: sanguisdex
Syndicate content