Thread

I need help figuring out how to troubleshoot the situation I’ve gotten myself into. I think I fratzed my database somehow.

MY SETUP
Have EE 1.6.8 running in MAMP environment on MacOS 10.5 computer.
Install is a sandbox-y version of a site that’s also live. I play with it on MAMP, figure stuff out, then duplicate efforts on live site.

WHAT I DID
Today I added some new fieldgroups, some new fields, two new weblogs, one new category group.

THE CRASH. SOME DETAILS
In midst of doing first test publish to 1 of new weblogs (with new field group, etc.), I managed to crash Safari browser (Safari crashes happen often enough due to ridiculous # of windows/tabs I keep open. Yeah, I’m weird. Don’t really flinch at this browser crash because they’ve happened before.) Incidentally, this crash happened when I attempted to do file upload inside of image matrix on the publish page.

Somehow that browser crash was bad enough to bring many things down. Couldn’t force quit Safari, nor another app.  I shut down as many apps as I could (including MAMP, VirtualHostX) and tried to reboot system (terminal: sudo shutdown -r now), but computer wouldn’t shutdown. Hence: press powerbutton. Ow, that was a bad one.

THE CONDITIONS OF THINGS AFTER RESTART
Restarted, launched MAMP, VirtualHostX, and set about starting again where I left off.
Except all the fields I created in today’s session are not there.
Wha—?? 

So I go about attempting to re-create my work.

Create new fieldgroup (ok)
a) Create new field.

After filling out the items for a new text field, I click Submit button.
Result: Error message:

MySQL ERROR:
Error Number: 1060
Description: Duplicate column name ‘field_id_43’
Query: ALTER TABLE exp_weblog_data ADD COLUMN field_id_43 text NOT NULL

The strange thing, though, is that the field did get added OK, despite the message.

b) Did that again with a new text area field.
Got similar message, but it did get created okay.

c) Now attempting to create third field, a Matrix field. Complete the process, click Submit.
Error message

MySQL ERROR:
Error Number: 1060
Description: Duplicate column name ‘col_id_16’
Query: ALTER TABLE exp_matrix_data ADD col_id_16 TEXT

But this time, the new field did NOT get created.

FROM THIS POINT, WHERE DO I TROUBLESHOOT?
Do I hang out in phpmyadmin/ mySQL and do things like check/repair/analyze/of flush the tables?

I have not been backing up this local MAMP-based database by doing periodic SQL dumps.
However, I have been backing it up through regular backups. (I guess if I wanted to make a really clean roll back, I could get the disk image from last backup of /Applications/MAMP/db/mysql/ee_thisdatabase and roll back to that point.)

Any advice for what to do would be greatly appreciated.

Yes, definitely sounds like a corrupted database. Do you have a recent backup? using phpMyAdmin or a similar tool you can try to repair your db, although rolling back to the last backup might be the better choice.

I have a backup from about 12 hours before all this happened. (I am so thrilled to be able to say that! Backups Tu Thurs and Saturday am in the wee hours. That’s what geek women ask for for their birthdays from their geek men: A backup system that just works. Srsly.)

It’s MacOS / Retrospect backup of the folder and files where MAMP writes the database.  Different than my phpmyadmin do database dump procedure that I do from time to time on live server sites.

Have pulled those files from off Retrospect, but have not yet Made The Attempt To Replace the Database (I’ll keep you posted here, because I’m sure others will be interested in finding out how it’s done).

In keeping with linking to other areas of teh internets w/ good how-to info, after I began this thread here in the forum, I found a post about how to restore a MySQL database from Time Machine backup—and the first comment is a beacon to me:

FYI for MAMP users - just restore the /Applications/MAMP/db/mysql/ folder!

(First I’ll take screenshots of the semi-built fields, because if I’m doing this over, I might as well make the do-over as easy as possible)

REMAINING QUESTION: What, if anything, can I do to avoid corrupting the database? Or, what are the signs of a corrupted database so that I can learn about and deal with this in shortest possible time next time?

Hooray, it works.

What I did:

In Retrospect, found location of /Applications/MAMP/db/mysql/ee_thisdatabase
Selected the entire directory
Restored to new folder on backup computer

Prepare to make the old, corrupt database go away by saving as much of work done yesterday as possible:
* Took screenshots of fields I had created
* Exported to file one template that I’d created yesterday

Shut down VirtualHostX, in MAMP, shut down APACHE and MySQL servers.

In Finder, located the database directory, selected the ee_thisdatabase folder and archived it into a zip file, changed name to note this was corrupted (hey. just in case)

Opened folder ee_thisdatabase, selected all, sent to trash

Opened backup folder containing earlier version of database. Found the ee_thisdatabase folder nested deep inside it (it’s how Retrospect rolls). Selected all contents of folder, moved to the folder inside the MAMP applications folder.

Inhaled. Exhaled.

Started up VirtualHostX.

In MAMP, started up servers again.

Opened localhost copy of the website. It loaded okay. Went to system folder, logged in. Works.
Checked the status of weblogs, custom fields, categories. All look good.
For grins, in Admin area, I cleared database cache (tho I think my settings are to not cache database anyhow)

I created a new, blank template. Opened up the template I’d exported, copied it, pasted into the new blank template.

Began reconstructing custom fields, using my screenshots as a guide. At each point along the way, IT JUST WORKED.

Am now back and running again. Whew!

MY LAST REMAINING QUESTION(s):

What, if anything, can I do to avoid corrupting the database?

Or, what are the signs of a corrupted database so that I can learn about and deal with this in shortest possible time next time?

I am afraid that’s really more a matter of setting up and supporting and maintaining a server; db corruption happens sometimes, but it should not happen too often, otherwise there’s probably an issue with your server.

If it happens, you’ll know because your site will stop working properly. In which case a backup is usually the best solution.

Yes, the problem with my server ... er computer—is the driving force. The database issue was an early warning signal.

Am now troubleshooting my MacPro, which no longer starts up. It may be faulty OS, RAM gone bad (and other stuff) or (please, no) a bad motherboard. I got bigger fish to fry, and learned stuff about MAMP databases in the process. I myself am not out of the woods, but as for this problem I mentioned here on the forum, you can mark it solved and close it.

Thanks for letting us know. And good luck with the troubleshooting… Post again as needed, please.