Server outrages

[unconnected]

Gary, if you've successfully started mysql server, run mysql client and execute 'drop database prpnet1470' query in it.

[MyDogBuster]

I'm going to rename this thread Gary's Great Adventure - All I ever wanted to know about MYSQL but were to afraid to ask

[gd_barnes]

Haha. Good one Ian. I will move the applicable posts over to the "Server outrages" thread after the issue is completely resolved.


...trying the recent suggestions shortly.

[gd_barnes]

I seem to be in a vicious loop here.

As previously specified I added the command:
innodb_force_recovery = 5
to the var/mysql/my.cnf file

I rebooted the machine and checked the MySQL status using:
sudo service mysql status

It looked good.

After logging into MySQL I tried to drop prpnet1470 with:
drop database prpnet1470;

I got the following error:
ERROR 2013 (HY000): Lost connection to MySQL server during query

So...MySQL isn't starting properly due to this error and I am unable to drop the offending DB due to this error.

Any suggestions?

I will try increasing the force_recovery even higher to see if that does anything.

[gd_barnes]

So here is my latest attempt to start MySQL after changing my.cnf to:
innodb_force_recovery = 6

gary@jeepford:~$ sudo service mysql stop
* Stopping MySQL database server mysqld [ OK ]
gary@jeepford:~$ sudo service mysql start
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

So...MySQL seems to start OK but is still giving this error message.

I'm stuck.

[rebirther]

modify /etc/my.cnf with the following settings:



net_read_timeout=600
net_write_timeout=180
wait_timeout=86400
interactive_timeout=86400
max_allowed_packet=128M



Maybe the timeout is too short

[gd_barnes]

I picked another random database to drop that is no longer used: prpnet1466

The database dropped just fine.

Trying Reb's suggestion next...

[gd_barnes]

Adding the parameters in Reb's recent suggestion made no difference when attempting to drop database prpnet1470.

Maybe I could just manually give myself access to all of the root and mysql files where prpnet1470 is located and delete them all. Just a crazy thought.

[rebirther]

Originally Posted by gd_barnes

Adding the parameters in Reb's recent suggestion made no difference when attempting to drop database prpnet1470.

Maybe I could just manually give myself access to all of the root and mysql files where prpnet1470 is located and delete them all. Just a crazy thought.

How big is this database? If its bigger than max_allowed_packet=128M increase the value

[gd_barnes]

Originally Posted by rebirther

How big is this database? If its bigger than max_allowed_packet=128M increase the value

That's not the problem. I dumped one huge database that was 4.2 GB and it dumped fine.

Problem database prpnet1470 is about 200 MB so I increased the packet size to 256M, rebooted the machine, got MySQL up and running and tried to dump it. No difference.

Error:
Error 2013: Lost connection to MySQL server during query when dumping table `CandidateTestResult` at row: 251210

This error occurs after dumping 131.6 MB of the file.

[gd_barnes]

A little more info:

I am able to do selects on all of the databases with the exception of the CandidateTestResult table on database prpnet1470. I can do selects on other tables in that one.

So no data will be lost except a small amount on prpnet1470.

But I cannot do any deletes on any table. My suspicion is that no table can be updated in any way...insert, update, or delete. That is why all of the prpnet servers start up just fine but when they try to receive a completed test an SQL error happens.

This begs the question: Why does one corrupt database affect MySQL in total such that other databases cannot be updated?