MySQL "forgot" some of my data!

[Michael Vince]

daniel wrote:

>Our company has a few very large databases (>9gb) which required a shut down, 
>upgrade and restart last night, it this act has caused me all manour of 
>headache:
>
>  1. I did a full backup of all the data (Good Idea)
>  2. I ran "mysqladmin -uroot -p shutdown
>  3. I waited... and waited... and waited
>
>Twenty minutes passed and the server continued to say that it was indeed 
>shutting down.  The only problem was that it wasn't shutting down.  The 
>server's load was 0.00 and i could access mysql via the command-line client.
>
>Ctrl-C'ing the shutdown and restarting it didn't seem to help either.  What's 
>worse, logging in with the client had me freaking out when I couldn't find 
>some of my data:
>
>  mysql> SELECT * FROM campaigns LIMIT 0;
>  ERROR 1017 at line 1: Can't find file: 'campaigns.MYI' (errno: 2)
>
>After some investigation, we realised that a cron on another machine was 
>running every minute, talking to this database, and assuming that this was 
>the issue, we turned that stuff off too only to find that mysqladmin *STILL* 
>wouldn't actually shut anything down.
>
>So I decided to try a few other things:
>
>  # kill -s SIGTERM `cat /var/db/mysql/hostname.pid`
>
>nothing
>
>  # kill `cat /var/db/mysql/hostname.pid`
>
>still nothing
>
>  # kill -9 `cat /var/db/mysql/hostname.pid`
>
>That did it.  Bringing the database back up though confirmed it, we'd lost a 
>few whole tables and possibly some additional data as well.  No data 
>corruption though, just loss.  A full restore from our now roughly 24hours 
>old backup would be required.
>
>So my questions to you, deal list are:
>
>  1. Should mysqladmin shutdown take this long?
>    a. Is this normal even with a load of 0.00?
>
>  2. Is this the best way to shut down a database of this size?
>
>  3. What is most likely to be the cause of the data loss?
>
>Thanks for any insight here.  I hope this is a lesson to you all: Backups are 
>Groovy ;-)
>  
>
I have had this problem as well on a 30gig db, I dont remember exactly 
what it was but I had a hunch it was something to do with maybe my 
hostname change, I think I changed it from 'database' to 
'database.domain.com' via the 'hostname' command then edited the 
/etc/rc.conf file I think this stuffed up the running database pid file 
because it was looking for /var/db/mysql/hostname.pid instead of 
/var/db/mysql/hostname.domain.com.pid, but trying to bring it down 
manually didn't seem to work either.

It could be been something else all together, but I do believe it was 
config changes around the system that caused it, I do believe an instant 
clean shutdown of MySQL is possible (I can't test for you on mine, 
sorry) but it might require you to really restart you setup clean.

Mike