Author Topic: Read-only database after restoring from backup on OS X [solved]  (Read 1095 times)

Offline boardhead

  • Semi-Newbie
  • *
  • Posts: 23
Read-only database after restoring from backup on OS X [solved]
« on: October 03, 2016, 01:50:06 PM »
I had a corrupted smf_themes table that shut down my forums last friday.  I got the server up and running again by restoring the table from my hourly backups, but today I discovered that nobody was able to create a new account over the weekend due to a read-only error:

Table 'smf_themes' is read only
File: /Library/WebServer/Documents/exiftool/forum/Sources/Subs-Members.php
Line: 424

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.11, while your database is at version 2.0. The above error might possibly go away if you execute the latest version of upgrade.php.

(Note that the upgrade warning was misleading, and caused me to waste time trying to upgrade the database, but the upgrade failed due to the same read-only problem.)

Looking at the database files with "ls" showed the correct permissions:

Code: [Select]
-rw-rw---@  1 _mysql  _mysql  171200 Oct  3 12:51 /usr/local/mysql/data/smf/smf_themes.MYD
-rw-rw---@  1 _mysql  _mysql  215040 Oct  3 12:51 /usr/local/mysql/data/smf/smf_themes.MYI
-rw-rw---@  1 _mysql  _mysql    8678 Mar 31  2015 /usr/local/mysql/data/smf/smf_themes.frm

But it turns out that they were somehow still read-only by mysql.

It seems that OS X has additional permissions on top of the regular Unix filesystem.  It took me about 4 hours to figure this out, but the following commands fixed things for me (I'm not whether it was the xattr or chmod that actually fixed the problem):

1. cd /usr/local/mysql/data/smf
2. sudo xattr -c smf_themes.*
3. sudo chmod -N smf_themes.*
4. sudo launchctl unload /Library/LaunchDaemons/com.mysql.mysql.plist
5. sudo launchctl load /Library/LaunchDaemons/com.mysql.mysql.plist

The last two command just restart mysql, which is necessary after fixing the permissions.

I posted this hoping that I might be able to save someone some time if they have a similar problem.

- Phil