Author Topic: Can't connect to MySQL Server  (Read 8566 times)

laschmoove

  • Sr. Member
  • ****
  • Posts: 29
Can't connect to MySQL Server
« on: November 30, 2009, 12:52:33 PM »
Hi everybody,

we are trying to move our supervisor from our old Linux box to a Windows Server 2008. During installation of the qube supervisor (x64) I'm getting the following error:

ERROR: Can't contact database: Can't connect to MySQL server on 'localhost' (10061) (2003)

This message is repeated endlessly.

I also tried to install MySQL manually prior to the supervisor installation with the same result.

Does anybody have an idea for me how to solve this?

Thanks for your help,
B.

P.S. Yes, port 3306 is open.
« Last Edit: November 30, 2009, 12:59:10 PM by laschmoove »

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #1 on: November 30, 2009, 02:49:13 PM »
Did you copy the qb.conf from your linux supervisor over to the new windows supervisor?  There are some settings in the qb.conf that would be linux-specific (look for database_* in the qb.conf).

Is MySQL started and running on the windows host?

laschmoove

  • Sr. Member
  • ****
  • Posts: 29
Re: Can't connect to MySQL Server
« Reply #2 on: November 30, 2009, 03:40:13 PM »
Hi jburk,

I did not even get that far. But now it seems like I've got it. I killed all mysql processes and then (re)configured the pfxmysql service manually. Finally I added the old services again.

But thank you for your idea anyway. I'm sure that I would have made that mistake in the next step...

Cheers,
B.

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #3 on: November 30, 2009, 05:51:54 PM »
Nice work. 

Which version of Qube are you installing on the Windows host?  I'll take a look at the installer and try to see what may have happened.

michael.graf

  • Sr. Member
  • ****
  • Posts: 26
Re: Can't connect to MySQL Server
« Reply #4 on: December 02, 2009, 08:28:48 AM »
we get these errors too with our installation (sup on windows server 2003 x86) and have been getting them for months, across the past few sup updates. I just need to restart the pfxmysql and supervisor. Then it goes away and comes back several weeks later Maybe it is related to the level of activity in the queue (we run a few thousand jobs a month). Everything seems to continue running ok but the occasional stuck job in the queue that is not really running. When I get one of these I know it is time to restart the pfxmysql, hehe.

our pfxmysql uses 3300 and I did not change anything with the mysql config. so maybe this was the issue from the beginning, the port not being open, as you mentioned port 3306.
« Last Edit: December 02, 2009, 08:43:11 AM by michael.graf »

michael.graf

  • Sr. Member
  • ****
  • Posts: 26
Re: Can't connect to MySQL Server
« Reply #5 on: December 02, 2009, 08:33:26 AM »
my apologies. we actually get the error

ERROR: database query error: localhost via TCP/IP - MySQL server has gone away (2006)
INFO: reconnecting to database: localhost

so different error. guess i need to check before i go by memory. hehe.
« Last Edit: December 02, 2009, 08:41:54 AM by michael.graf »

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #6 on: December 02, 2009, 06:51:34 PM »
I'll be talking to the developer who handles our windows installer tommorrow (he's out on vacation), and will get some more info on how the pfxmysql service differs from a stock mysql service.  I know stock MySQL inside and out, but there must be something slightly tweaked (or that needs tweaking) in the pfxmysql if it's always packing it in.

I'll be posting some more on this topic later in the day tomorrow.  Your patience is appreciated.

We have sites that run 10's of thousands of jobs every day, so a few thousand a month shouldn't be a problem.  But those big sites have to have a heavily optimized MySQL configuration to stand up to the load.
« Last Edit: December 02, 2009, 06:54:50 PM by jburk »

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #7 on: December 22, 2009, 07:10:48 AM »
I've been digging around the MySQL docs lately and came across something that might pertain to this problem where the mysql server on windows just seems to go away.

Windows only has about 4000 ports available, compared with around 64,000 on Linux and OSX.  And it seems that the ports don't get freed up very quickly on Windows; apparently it can take 2 to 4 minutes to free up a port after it's been released.  

(see the section labeled "Limited number of ports" at http://dev.mysql.com/doc/refman/5.1/en/windows-vs-unix.html)

So it's conceivable that it would be easy to encounter a scenario where it's possible to exhaust the number of ports that the supervisor could use to connect to MySQL.

The supervisor runs a up to certain number of separate processes, limited by the value of supervisor_max_threads in the supervisor's qb.conf.  Each of these threads maintains a separate connection to the MySQL server on a different port.  

Each supervisor thread is also set to only service so many requests from workers and clients before it shuts down. This is set by supervisor_max_clients.

In the event that you have 1 or more jobs where the agenda items run very quickly (have a high dispatch rate) or if there is some other scenario which is causing a high rate of requests to be processed by the supervisor, you will have a lot of supervisor processes hitting their max_clients limit and shutting down, disconnecting from a port which Windows might not free up for 2 to 4 minutes.  When more requests come in and there is not a free supervisor process to handle the request, and the number of supervisor processes is less than max_threads, another supervisor process is started up, which connects to MySQL on the next available port.

If this scenario continues for long enough, the number of free ports on the supervisor will eventually drop to 0, and the MySQL server will appear to be dead.

Possible solution:

One way to alleviate this problem of fast port turnover would be to increase the number of requests handled by each supervisor process.

supervisor_max_clients is 256 by default. Increasing this number to 1024 should cut down the rate of port consumption.

A side-effect of this will be that the number of supervisor processes will take longer to decrease during times of low load. This will mean that the Qube supervisor will spend a larger amount of time at its maximum memory footprint.
« Last Edit: December 22, 2009, 03:42:52 PM by jburk »

mus

  • Jr. Member
  • **
  • Posts: 3
Re: Can't connect to MySQL Server
« Reply #8 on: January 28, 2010, 12:22:53 PM »
Hi

On a new Windows Server 2003 VM, I installed Qube Core 5.5.1 followed by Qube Supervsor 5.5.1. While installing the Supervisor, I received the endless message above.

ERROR: Can't contact database: Can't connect to MySQL server on 'localhost' (10061) (2003)

Prior to that, the DOS window output the following:
INFO:MySql Client Version : 5.1.22-rc
detecting current storage state...
tables already exists. upgrading...
current storage version: 0
upgrading: 1 to 2

I suppose the Supervisor were not installed successfully.
What did I miss ?


jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #9 on: January 28, 2010, 07:56:55 PM »
is the pfxmysql service running?

What do the commands:
    sc query pfxmysql
    sc qc pfxmysql
return?

mus

  • Jr. Member
  • **
  • Posts: 3
Re: Can't connect to MySQL Server
« Reply #10 on: January 29, 2010, 01:16:03 AM »
Both return "Failed 1060:"

The specified service does not exist as an installed service.

Must I install mysql first ? Latest version ?

I initially thought it comes standard with the Supervisor installer for Windows ?

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #11 on: January 29, 2010, 01:47:17 AM »
Can you dig around in your Services tab and see if you have the Qube Supervisor and any MySQL installed?

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Can't connect to MySQL Server
« Reply #12 on: January 29, 2010, 01:48:12 AM »
do you have the firewall turned off, are you installing as Administrator, etc?

mus

  • Jr. Member
  • **
  • Posts: 3
Re: Can't connect to MySQL Server
« Reply #13 on: February 02, 2010, 06:31:56 AM »
Apparently the machine i'm working on has some issues. Created it from a VM template. After re-doing Windows Server 2003 from scratch , everything works fine now. Thanks for your help anyway. I'm now working on installing and configuring the workers and clients.