MySQL stops functioning periodically - any ideas?

[Richard]

Hi All,

I've been trying to build a Ruby-on-Rails plus MySQL application. I'm
running Ruby 1.8.2, Rails 1.1.4 and MySQL 5.0.15-nt over
WinXP-Pro/SP2. I run under an Administrative account.

I tried building the first example from Agile Web Development with
Rails, 1st ed. Somehow the DB got corrupted and I couldn't recover.
Instead of retrying that, I tried building the first example from Ruby
For Rails, which worked fine.

So I started building my own app when a daemon attacked MySQL again.
The symptoms are:
1. The mysql client rejects root, despite the fact that My.cnf
provides root's credentials and has been working for days without
supplying a username nor password.
2. That rejection continues if I try with "mysql -u root -p" and
provide the root pswd when prompted
3. mysqladmin rejects root in the same way as mysql does. (I'm
running the server on the client machine.)
4. When I check the server with "telnet localhost 3306", I get a
trash response ... sort of an amalgamation of Arabic, Farsi and Hebrew
.... none of which I speak :-)
5. Rebooting does not change anything.
6. I stopped the server by use of Contol Panel/Computer
Management/Services' Stop command
7. Restarted server with "net start MySQL"; same rejection of root

Is it possible the password table got corrupted? Is there anyway to
delete it and re-enter the root password? Or am I unable to connect
with port 3306 somehow? How could I test that further?

Any suggestions would be greatfully received.

Thanks in advance,
Richard

[Gordon Burditt]

Re: MySQL stops functioning periodically - any ideas?

>I've been trying to build a Ruby-on-Rails plus MySQL application. I'm

>running Ruby 1.8.2, Rails 1.1.4 and MySQL 5.0.15-nt over
>WinXP-Pro/SP2. I run under an Administrative account.
>
>I tried building the first example from Agile Web Development with
>Rails, 1st ed. Somehow the DB got corrupted and I couldn't recover.
>Instead of retrying that, I tried building the first example from Ruby
>For Rails, which worked fine.
>
>So I started building my own app when a daemon attacked MySQL again.

Which daemon, running on which host, using what login?

Mysql will reject connections from a host if too many aborted
connections come from that host. Use "mysqladmin flush-hosts"
to reset this (if you can get connected at all, which doesn't
seem to be the case here). Are you connecting with "localhost"
or something else? I'm not sure it can lock out connections
via the local UNIX-domain socket (or does that feature even
exist on a server running on Windows?). Note that "localhost"
and "127.0.0.1" are *NOT* the same thing (on UNIX at least).

Another possibility for resetting the error is to stop the daemon
as gracefully as possible while still doing it (which may be a
reboot), and restart it WITHOUT restarting any misbehaving clients
which may be seen as "attacking" the server (leave the web server down).

Examine the error log (on my system it's in /var/db/mysql/*.err,
yours is probably different). Are you getting "host is blocked"
errors? This is my assumption, and if you aren't, my proposed
solution is less likely to work.

>The symptoms are:
>1. The mysql client rejects root, despite the fact that My.cnf
>provides root's credentials and has been working for days without
>supplying a username nor password.
>2. That rejection continues if I try with "mysql -u root -p" and
>provide the root pswd when prompted
>3. mysqladmin rejects root in the same way as mysql does. (I'm
>running the server on the client machine.)

You can't connect to the daemon from your host. Period. Try from
a different host if you have an administrative login that will work
from a different host (you had to specifically GRANT it).

>4. When I check the server with "telnet localhost 3306", I get a
>trash response ... sort of an amalgamation of Arabic, Farsi and Hebrew
>... none of which I speak :-)

It's a binary protocol, and this is normal, which means the daemon
is still accepting connections.

>5. Rebooting does not change anything.
>6. I stopped the server by use of Contol Panel/Computer
>Management/Services' Stop command
>7. Restarted server with "net start MySQL"; same rejection of root
>
>Is it possible the password table got corrupted? Is there anyway to
>delete it and re-enter the root password? Or am I unable to connect
>with port 3306 somehow? How could I test that further?

On a UNIX system you can stop the daemon and restart it with
--skip-grant-tables (and probably --skip-networking if there's the
equivalent of the UNIX-domain socket connection on Windows). Then
reset the root password and restart the daemon. Beware that this
lets anyone in. I suspect the procedure is similar for Windows.

[Richard]

Re: MySQL stops functioning periodically - any ideas?

Hi Gordon,

THANK YOU!!

Thank for taking time to address all the bases. I focused on the
"--skip-grant-tables" because I recall seeing something about that. My
"Definitive Guide t MySQL 5" covered that pretty well.

So I stopped the MySQL service in the Services applet; ran MySQLd.exe
with the skip option in one Command Window. Opened a second Command
window and ran "mysql -u root". That led to a Windows Security Alert
dialog which blocked my program but allowed me to unblock it. Then I
checked with a show databases. Looks good!!

Thanks so much for giving me a simple way out of this mess. I'm
proposing to implement a client's Receivables system with Ruby on Rails
and MySQL. The first two seem to be really solid and I love them. I
love MySQL 5.0 on paper, but I got to assure myself that MySQL won't
bomb like this frequently. If you'r interested, I'll post (or mail
you) with responses to your other questions and my outcome on this
reliability issue.

With warm regards,
Richard

Gordon Burditt wrote:

I've been trying to build a Ruby-on-Rails plus MySQL application. I'm
running Ruby 1.8.2, Rails 1.1.4 and MySQL 5.0.15-nt over
WinXP-Pro/SP2. I run under an Administrative account.

I tried building the first example from Agile Web Development with
Rails, 1st ed. Somehow the DB got corrupted and I couldn't recover.
Instead of retrying that, I tried building the first example from Ruby
For Rails, which worked fine.

So I started building my own app when a daemon attacked MySQL again.

>
Which daemon, running on which host, using what login?
>
Mysql will reject connections from a host if too many aborted
connections come from that host. Use "mysqladmin flush-hosts"
to reset this (if you can get connected at all, which doesn't
seem to be the case here). Are you connecting with "localhost"
or something else? I'm not sure it can lock out connections
via the local UNIX-domain socket (or does that feature even
exist on a server running on Windows?). Note that "localhost"
and "127.0.0.1" are *NOT* the same thing (on UNIX at least).
>
Another possibility for resetting the error is to stop the daemon
as gracefully as possible while still doing it (which may be a
reboot), and restart it WITHOUT restarting any misbehaving clients
which may be seen as "attacking" the server (leave the web server down).
>
Examine the error log (on my system it's in /var/db/mysql/*.err,
yours is probably different). Are you getting "host is blocked"
errors? This is my assumption, and if you aren't, my proposed
solution is less likely to work.
>

The symptoms are:
1. The mysql client rejects root, despite the fact that My.cnf
provides root's credentials and has been working for days without
supplying a username nor password.
2. That rejection continues if I try with "mysql -u root -p" and
provide the root pswd when prompted
3. mysqladmin rejects root in the same way as mysql does. (I'm
running the server on the client machine.)

>
You can't connect to the daemon from your host. Period. Try from
a different host if you have an administrative login that will work
from a different host (you had to specifically GRANT it).
>

4. When I check the server with "telnet localhost 3306", I get a
trash response ... sort of an amalgamation of Arabic, Farsi and Hebrew
... none of which I speak :-)

>
It's a binary protocol, and this is normal, which means the daemon
is still accepting connections.
>

5. Rebooting does not change anything.
6. I stopped the server by use of Contol Panel/Computer
Management/Services' Stop command
7. Restarted server with "net start MySQL"; same rejection of root

Is it possible the password table got corrupted? Is there anyway to
delete it and re-enter the root password? Or am I unable to connect
with port 3306 somehow? How could I test that further?

>
On a UNIX system you can stop the daemon and restart it with
--skip-grant-tables (and probably --skip-networking if there's the
equivalent of the UNIX-domain socket connection on Windows). Then
reset the root password and restart the daemon. Beware that this
lets anyone in. I suspect the procedure is similar for Windows.

[Richard]

Re: MySQL stops functioning periodically - any ideas?

Hi Gordon,

Just a few words of "follow up":

I reset my root password from the mysql no-password login. I compared
the old encrypted version with the new. That hints at the possibility
that the root password had been corrupted.

But I feel it's more likely that the MySQL security scheme would
re-encrypt a new password to preclude enabling a malicious user to
generate a slew of plain- and cyber-text combinations. On the other
hand, such a user could probably figure out how to crack systems by
studying the source code or just use the "--skip-grant-tables"
convenience.

I do have to address the security issue presented by the
"--skip-grant-tables" when I recommend MySQL to clients. But that can
wait until I get my current Ruby-Rails-MySQL project working. This
issue my force me to check out ODBC and Microsoft Access.

I have gone though the full cycle of processing and everything is back
to normal. I kept a good record of all my steps in an MS Word
document. That stuff would be too long and ugly to reproduce here,
where font control, fore- and back-ground coloring is unavailable, etc.
(at least to my knowledge).

Again, thank you for your excellent advice and
Best wishes,
Richard




Gordon Burditt wrote:

I've been trying to build a Ruby-on-Rails plus MySQL application. I'm
running Ruby 1.8.2, Rails 1.1.4 and MySQL 5.0.15-nt over
WinXP-Pro/SP2. I run under an Administrative account.

I tried building the first example from Agile Web Development with
Rails, 1st ed. Somehow the DB got corrupted and I couldn't recover.
Instead of retrying that, I tried building the first example from Ruby
For Rails, which worked fine.

So I started building my own app when a daemon attacked MySQL again.

>
Which daemon, running on which host, using what login?
>
Mysql will reject connections from a host if too many aborted
connections come from that host. Use "mysqladmin flush-hosts"
to reset this (if you can get connected at all, which doesn't
seem to be the case here). Are you connecting with "localhost"
or something else? I'm not sure it can lock out connections
via the local UNIX-domain socket (or does that feature even
exist on a server running on Windows?). Note that "localhost"
and "127.0.0.1" are *NOT* the same thing (on UNIX at least).
>
Another possibility for resetting the error is to stop the daemon
as gracefully as possible while still doing it (which may be a
reboot), and restart it WITHOUT restarting any misbehaving clients
which may be seen as "attacking" the server (leave the web server down).
>
Examine the error log (on my system it's in /var/db/mysql/*.err,
yours is probably different). Are you getting "host is blocked"
errors? This is my assumption, and if you aren't, my proposed
solution is less likely to work.
>

The symptoms are:
1. The mysql client rejects root, despite the fact that My.cnf
provides root's credentials and has been working for days without
supplying a username nor password.
2. That rejection continues if I try with "mysql -u root -p" and
provide the root pswd when prompted
3. mysqladmin rejects root in the same way as mysql does. (I'm
running the server on the client machine.)

>
You can't connect to the daemon from your host. Period. Try from
a different host if you have an administrative login that will work
from a different host (you had to specifically GRANT it).
>

4. When I check the server with "telnet localhost 3306", I get a
trash response ... sort of an amalgamation of Arabic, Farsi and Hebrew
... none of which I speak :-)

>
It's a binary protocol, and this is normal, which means the daemon
is still accepting connections.
>

5. Rebooting does not change anything.
6. I stopped the server by use of Contol Panel/Computer
Management/Services' Stop command
7. Restarted server with "net start MySQL"; same rejection of root

Is it possible the password table got corrupted? Is there anyway to
delete it and re-enter the root password? Or am I unable to connect
with port 3306 somehow? How could I test that further?

>
On a UNIX system you can stop the daemon and restart it with
--skip-grant-tables (and probably --skip-networking if there's the
equivalent of the UNIX-domain socket connection on Windows). Then
reset the root password and restart the daemon. Beware that this
lets anyone in. I suspect the procedure is similar for Windows.

[Gordon Burditt]

Re: MySQL stops functioning periodically - any ideas?

>I reset my root password from the mysql no-password login. I compared

>the old encrypted version with the new. That hints at the possibility
>that the root password had been corrupted.
>
>But I feel it's more likely that the MySQL security scheme would
>re-encrypt a new password to preclude enabling a malicious user to
>generate a slew of plain- and cyber-text combinations. On the other

I'm not sure whether MySQL uses a 'salt' like the traditional UNIX
crypt function or not (the same password may be encrypted many ways).

>hand, such a user could probably figure out how to crack systems by
>studying the source code or just use the "--skip-grant-tables"
>convenience.

If a user can log into the server and restart mysqld with different
options (meaning he has either 'root' (on Windows: Administrator)
or 'mysql' privileges), your security is *already* trashed. He can
grab a copy of all your files and analyze them on his own system.
Or he could delete your 'mysql' database and replace it with one
of his own (where he knows the passwords) and restart the daemon.
Or he could bring over a MySQL daemon that doesn't check passwords.
MySQL cannot protect itself against an administrator on the server.

The vulnerability window when the *real* administrator uses it and
some bad guy over the net happens to find it in that state is an
issue which can easily be worked around. If you have a "sealed
server" (nobody is allowed to log in (system logins, not database
logins) to the server machine besides admins), shut down the network
while you do this, or use --skip-networking. If you do not have a
"sealed server", then for UNIX I'd do it in single-user mode, where
the network is shut down, nobody can log in, and user's cron jobs
aren't running. I believe Windows has something similar ("safe
mode"?): no network, no other servers that might provide logins
from a remote system, mysqld started manually.

>I do have to address the security issue presented by the
>"--skip-grant-tables" when I recommend MySQL to clients. But that can
>wait until I get my current Ruby-Rails-MySQL project working. This
>issue my force me to check out ODBC and Microsoft Access.

I'm sure Microsoft Access cannot protect against an administrator
on the server either.

The security issue is much like worrying about locking the barn door
failing to stop a horse thief from selling a horse that has already
been stolen.

>I have gone though the full cycle of processing and everything is back
>to normal. I kept a good record of all my steps in an MS Word
>document. That stuff would be too long and ugly to reproduce here,
>where font control, fore- and back-ground coloring is unavailable, etc.
>(at least to my knowledge).

The closest equivalent would be posting in HTML, which is generally
frowned upon.