After typing out that title I wonder if I’ve been spending too much time on forums, hehe. In any case, I ran into a problem today regarding my database tables being read-only so I thought I’d post this for the good of the internets. I had to dig pretty deep to find a solution to it. Hopefully some additional Google fodder will make life a bit easier for the next guy.
If you’re a regular reader here you know that I had some database problems this morning with this site. I ended up needing to import some databases from backups and manually tweak a few things to restore the lost data. Well it turns out that after a database import or a change of users and permissions your database tables can become read-only. I’m not entirely sure the reasoning behind this, but I found some discussion about it on a MySQL developers mailing list thread.
The solution that I found was to export (read: backup) all of my MySQL databases, and magically things become read-write again. I did this through the phpmyadmin web interface:
- Login to /phpmyadmin/ on your site.
- Click “Export” in the first column, near the bottom.
- Click “Select All” atop your list of databases.
- Select any special export options you want (optional)
- Click “Save as file” and a compression type. Click Go.
This will export and (optionally) compress your databases into an archive and present it to you as a download through your browser. Tuck it away for safe keeping somewhere and then check your database(s) again. Are they still read-only? If they are you are probably experiencing a different problem than I was, and I’m sorry this didn’t help. If they are now read-write, congratulations!
If anyone can shed any light on the reasoning behind this I’d be very interested to know. Until then, I’ve learned not to question the oddities like this but to simply share the method to the madness. Enjoy.