# MySQL issues [1 of 2 SOLVED]

## Moreaulf

Hello,

I have a few issues with a Gentoo server running MySQL which I can't seem to find the solution for.

On the server I have three independent MySQL servers running using different ports.

Issue 1:

This worked all right but I found out that when I connected to the localhost(:3306) through a web site the connection was instant. When I connected to SERVER-IP-ADDRESS:3307 it would make a form of time out and the web page stalled for 1-10 seconds. I tried loading a web page without an SQL connection and it worked all right, as soon as I had a SQL connection on the page the connection was halted before the page load.

What could be up with this?

As a way of trying to solve this problem I tried to upgrade the MySQL server from version 5.0.24 to 5.0.34.

This did not solve the problem, but only make another issue...

Issue 2:

MySQL won't stop. This issue I found in the post below as well:

https://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html

Seem to be a problem experienced by others as well.

I've turned on debugs and viewed all the logs but haven't been able to figure anything out of that, yet.

Issue 1: Nothing found in any log at all

Issue 2: States "Normal shutdown" only

Thanks in advance for any help,

/ThomasLast edited by Moreaulf on Wed May 02, 2007 9:56 pm; edited 1 time in total

----------

## magic919

Issue 1 sounds like the server(s) listening on localhost only.  Have a look at netstat -tunlp

----------

## Moreaulf

Thank you for your reply magic919!

Netstat shows that the server is listening on the SERVER-IP-ADDRESS:3307

I've tried configuration the mysql both with bind-address SERVER-IP-ADDRESS and without any bindings. Same result - It works, but there's a timeout on up to 10 seconds everytime an SQL connection is made (once made the queries are fast).

----------

## frejen

What about the mysql log does it contain any useful information? Maybe you can try to monitor the mysql server by typing the following command from the mysql prompt.

```
SHOW FULL PROCESSLIST\G
```

Have you tried different types of queries, like SELECT, INSERT or UPDATE?

----------

## Moreaulf

The server isn't a busy one at the moment so there are nothing in the processlist. The command that seem to timeout the server is the initial connect command. All other services like SSH, FTP, HTTP and so on works instantly, it's only the MySQL service that has this timeout.

It doesn't matter if I try to connect through the Apache web server on the HTTP protocol or if I run a regular MySQL connection through Shell. In this case this happens:

 *Quote:*   

> mysql --host=SERVER-IP-ADRESS --port=3307 --user=SERVER-MYSQL-USERNAME -p
> 
> Enter password
> 
> [timeout of 1-10 seconds]
> ...

 

If it was a Windows system I'd go for the DNS service but on this system I don't know what it could be. Can't seem to find any logs hinting about something's wrong  :Sad: 

----------

## magic919

Have a look at skip name resolve option for MySQL.

----------

## Moreaulf

magic919: Great! Didn't knew of that command, and it wasn't far from what my Windows guess would be either.

Thank you very much, it did indeed solve the timeout problem!  :Smile: 

----------

## kashani

In regards to Mysql not stopping I've found that the init scripts included with Gentoo don't wait long enough. 

On my system I use larger Innodb log files. When starting Mysql the first time these files need to be fully generates, 512M each, which takes 20-30 seconds. The init script will fail, but Mysql will be running. Trying to stop Mysql fails because the init script things it was never start. If I kill Mysql, /etc/init.d/mysql zap, and then /etc/init.d/mysql start things will work fine.

The other possible issue is that the pid file is not defined correctly in /etc/mysql/my.cnf or that directories like /var/run/mysql are missing. I'd check that /etc/mysql/my.cnf and /etc/init.d/mysql agree were the pid files are stored and that all permissions are correct.

kashani

----------

## magic919

 *Moreaulf wrote:*   

> magic919: Great! Didn't knew of that command, and it wasn't far from what my Windows guess would be either.
> 
> Thank you very much, it did indeed solve the timeout problem! 

 

You did most of the work by providing such an excellent clue.  Hope Part2 goes well.

----------

## Moreaulf

kashani: Thank you for your attempts! Here's the information from my part:

There's a stop timeout of 120 seconds. Turning on debug to integer 4 shows every tenth of a seconds during the stop timeout. Since I've been running several MySQL services on the server I've deactivated all but one to make the testing easier and I have tried with "empty" data directories and with skipping innodb without any differences. I don't think that innodb is the problem.

Also, the zap solution doesn't work on it's own since the pids still exist and the process are running. I have to manually kill the processes with "kill -9 PID" and "rm /var/run/mysqld/mysqld.pid" to have a clean "/etc/init.d/mysql start"

Rights on the /var/run/mysqld directory seems fine as well, "mysql" user and group all the way. The pids are generated in the directory during the start and the pid of the running service in in the file. When using the debug the stop command states different variables including the pid location and it's the correct one.

----------

## kashani

Odd. Does a regular kill work or does it always require the -9? Try doing a regular kill and see how long it take to close if it does at all. It's possible that some process is blocking and Mysql is waiting to close cleanly. Running a show processlist from the Mysql command line before closing might show something as well. 

kashani

----------

## Moreaulf

A regular kill doesn't close the process, at least not in a reasonable time ( < 1 minute). I have to use the -9 (which make the process close immidiately, which would be expected)

The only thing the SHOW PROCESSLIST; shows is the show processlist-command itself.

----------

## kashani

This might be my last idea on this, but how much RAM do you have and how much are you allocating to your Mysql servers? My guess on this is that flushing Innodb logs and writing tables to disk requires more memory than is easily available which causes Mysql to take a long time to shutdown. If you're running nothing on the server does each Mysql server start and stop cleanly? If it's one particular instance does that instance have a larger data set than the other or using large ibdata logs or files? If yes, it might be RAM starvation.

kashani

----------

## Moreaulf

Thank you for all your help kashani!

Although the server is a low RAM one (512 MB) I don't think that's the problem.

When I've been testing this the server hasn't been under any other load at all, no other connections than the one I'm using. Before the update of MySQL this worked like a charm - even without any load. I've tried deactivating innodb but the result is the same, even with completely empty databases (only mysql, test and information_schema from the mysql_install_db in the data dir).

If I really need to use the stop feature I guess I just have to downgrade the mysql server to an earlier version, that is known to solve the problem... I can't think of anything else to test either  :Sad: 

Thanks once again. If someone else stumbles upon this thread before it's stated "SOLVED" and got the solution, please write a reply since there might be others with the same problem.

----------

## g0rg0n

i thought i had same problem of mysql won't stopping.. but the actual problem was,

 *Quote:*   

> kashani wrote:
> 
> In regards to Mysql not stopping I've found that the init scripts included with Gentoo don't wait long enough. 

 

```

gentoo ~ # /etc/init.d/mysql stop

 * Caching service dependencies ...                                       [ ok ]

 * Stopping mysql ...

 * Stopping mysqld (0)                                                    [ !! ]

gentoo ~ # 

```

wait a little and run init script again shows

```

gentoo ~ # /etc/init.d/mysql stop

 * Stopping mysql ...

/etc/init.d/mysql: line 335: /var/run/mysqld/mysqld.pid: No such file or directory

 * Stopping mysqld (0)                                                    [ ok ]

gentoo ~ # 

```

and i can restart mysql with init script fine after that

cheers~

----------

## nyk

Same problem here. I tried the start-stop-daemon line from the init script and mysql is still alive two minutes later but died somewhere in the following minutes. So by setting the timeout to 10mins, the init script might work.

But the real question is WHY does mysql take so long to die?

It's not RAM shortage here and I also commented out the bin-log line from my.conf. I have 611M of data in mysql tables, but this shouldn't be a problem, or at least never was before.

----------

## CaptainSpam

Just to add my own two pieces of fractional currency, I've had this problem myself.  It gets to "Stopping mysqld (0)", times out, and reports a [ !! ] error.  Though I have found that giving a normal MySQL shutdown command through mysqladmin (mysqladmin -u root -p shutdown) seems to stop the database (and all its subprocesses) cleanly and quickly.  Granted, the init scripts wonder what's going on when it tries to stop it, and I try mysqladmin before trying the init scripts (if I try it afterward, mysqladmin reports problems communicating with the socket, but it does go down), but it still seems to get the job done much faster.

Now, I'm certain there's reasons the init scripts don't use mysqladmin (namely, it would need database-root's password hard-coded into conf.d), but that seems to shut things down nicely.  And another Gentoo user I've talked to said he doesn't have this problem at all (the init scripts shut things down quickly for him).  So I've got no idea what's causing it, but as far as I can tell, the way init's handling it doesn't seem to be agreeing with mysqld for everybody.

Not sure if this helps any, but that's all I've got.

----------

## kashani

I noticed that mysql-5.0.40 hit the tree this weekend and it resolves a few bugs relating to shutdown. I'd read through the bug and see if it relates to your systems. 

https://bugs.gentoo.org/show_bug.cgi?id=174790

kashani

----------

## CaptainSpam

Yep, .40 seemed to do the trick for me.  The init scripts kill it off cleanly now.  Problem solved!

----------

## nyk

Solved here too! 

Many thanks to the one who fixed it!

----------

