Tuesday, November 17, 2009

Recovering from "database cleaning" - mySQL - innoDB

OR: "How to cover your ass when your belt snaps and you forgot to wear your braces.  (Proof of the wear clean underwear rule.)
OR: When work is a little like duck hunting with Dick Cheney.

Today we had a bit of a scare. Someone decided to "clean up the databases".

Who needs to worry about this:

When you can just do it yourself :
mysql> drop foo;
"oh... you mean that 'foo' was in use ?  ? I was sure  it was useless." 
Rule 1: Always backup  before you delete. 
Always. Always. Always.

Memorize this: # mysqldump -u username -ppassword database_name > FILE.sql 

Thankfully the db was for an app with only a small amount of data.  So though a loss would be sad, it would not be catastrophic.  The other saving grace is that the app involves  undergrad students, not say, Vice Presidents, or Dept heads.  This is good as undergrad students are slightly above janitors in the pecking order.

I was training a new person when I got pulled in to the issue. So she got to experience the heavy breathing and waving of hands. Now that she has been exposed to the dark underbelly of our procedures on her first day, If she decides to stay, at least she has seen the worst.

This brings us to:
Rule 2: Backups are nice, but Restores are nicer.

It turns out that the restore plan was to copy all the files back in to place in the database directory in question.  We kept restoring the files from backups, and guess what, they all had the original creation date still. No data! We questioned our backups. But something seemed fishy to me, so I poked around and noticed that the other database directories had additional files,  e.g. .myi and .myd files - and this directory had only .frm files.  
From this I discovered -(thanks Google: )

That it was an innodb database not a myisam db, and so the data was stored elsewhere (by default in an ibdata1 file, in the directory one level up)

So I was pretty much able to restore the ibdata file and not lose anything. And Someone was pretty happy.

Sunday, November 15, 2009

Win 7 - VMware Fusion - sloww..

[UPDATE] +2G memory later... problem resolved...

My darling little MacBook with VMware Fusion/Windows 7 when intially installed was unconscionably slooow.
I had all the settings on the VM decently set. 1 processor and 1 GB mem for Win 7,
no 3D graphics. Set Win user exeperience to optimize for performance in GUI.

But after installing VMware tools (If you can't figure that out you should be shot in the head because Fusion prompts you about 11000 times to do it.)
...And installing piles of windows updates and rebooting a few times, things seem better.  This makes sense, because windows generally has to load lots of patches after an install.  I have seen windows grind to a halt in if you download the windows updates, and install, but don't reboot. My hunch is that the cause has something to do with memory use.

We'll see....

Model Name:    MacBook Pro
  Model Identifier:    MacBookPro5,5
  Processor Name:    Intel Core 2 Duo
  Processor Speed:    2.26 GHz
  Number Of Processors:    1
  Total Number Of Cores:    2
  L2 Cache:    3 MB
  Memory:    2 GB
  Bus Speed:    1.07 GHz

OS X version 10.5.8
build 9l31a

VMware Fusion 2.0.6