[Forum] Database error has me stumped

[ComoEstaEso-com]

Hello,

I receive a couple dozen (55 today, 40-50 other days) eMails every morning, at around the same time.

I have not been able to correlate the eMails with any of the Scheduled Tasks in the CP.
(( vB ACP >> Scheduled Tasks >> Scheduled Task Manager ))

Thoughts? Ideas?
What could I check?

Many eMails state:
MySQL Error : MySQL server has gone away


Here are two eMails:

Database error in vBulletin :

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User comoesta_vbullet already has more than 'max_user_connections' active connections /home1/comoesta/public_html/forums/includes/class_core.php on line 313

MySQL Error :

Database error in vBulletin 4.0.7:

Invalid SQL:

SELECT *
FROM session
WHERE userid = 0
AND host = '67.195.115.153'
AND idhash = '76a9d106cbbfd80a91e4c39ccc382d5c'
LIMIT 1;

MySQL Error : MySQL server has gone away
Error Number : 2006

And here's another one:

Database error in vBulletin :

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User comoesta_vbullet already has more than 'max_user_connections' active connections /home1/comoesta/public_html/forums/includes/class_core.php on line 313

MySQL Error :
Error Number :

Thank you very much for your help!!

[IBxAnders]

Your host appears to be limited your database to X number of connections - this is normal, let's say 100 (but we don't know for sure). It looks like when some query takes longer to complete in the morning (presumably scheduled tasks) it starts locking the database tables. This means that new connections are being opened but unable to quickly complete, so you will start using more and more connections. When this reaches the maximum allowed connections you will get the error message you are reporting, additionally, when the extended query fails to complete you might get the "MySQL server has gone away" error.

You need more database power for your forum. If you can't - I would recommend switching table engines to InnoDB - this will help with the locking and running out of connections issue a little bit.

[ComoEstaEso-com]

Thank you for the answer!
I will try and figure out what task is causing this.
I will start tonight by disabling one task, and seeing if the database times out.

For reference, I will update the results here and post what I find.
Thank you for your time

[Wayne Luke]

For your first error, you should have the host make sure the wait_timeout for MySQL is set to at least 120 seconds. No query should require that much time but we do use deferred queries on some features to lower resource usage.

[ComoEstaEso-com]

For your first error, you should have the host make sure the wait_timeout for MySQL is set to at least 120 seconds. No query should require that much time but we do use deferred queries on some features to lower resource usage.

I did a MySQL query: SHOW VARIABLES LIKE '%wait%';

and the results came back:
Variable_name Value
innodb_lock_wait_timeout 50
table_lock_wait_timeout 50
wait_timeout 7800

These are two reference pages I was directed to in the mIRC #MySQL channel:
Server System Variables
Direct wait_timeout entry

I was told the default setting is 28800
Which is in seconds, thus about 480 minutes... and that's 8 hours.

So, our setting is at 7800 (seconds), which is 130 minutes... 2.17 hours.

Should I check what processes are started approximately 2.17 hours before the eMail says it timed out?

Thanks a lot for the help!

[Wayne Luke]

Do your have logging turned on for MySQL? If so I'd look at that. Your lock timeouts are at 50 seconds. Shouldn't be a problem for innodb tables but myisam tables lock the entire table. The errors could be caused by conflicting processes, such as trying to create a backup while the forum is open and someone tries to write to the database.

[ComoEstaEso-com]

w00t!!

Do your have logging turned on for MySQL? If so I'd look at that. Your lock timeouts are at 50 seconds. Shouldn't be a problem for innodb tables but myisam tables lock the entire table.

Thanks for the reply!

I ran a query on the database:
SHOW VARIABLES LIKE 'log'

That returned
Variable_name Value
log OFF

Should I turn on the logging?

The errors could be caused by conflicting processes, such as trying to create a backup while the forum is open and someone tries to write to the database.

I think that's it!

I had the Site Map generator set at 2 * * *
AND
also had the Automatic Database Backup plugin (vB.org) set at 2 * * *


I just changed times for them (non-conflicting with anything else).
Let see tomorrow if any errors come in.
I'll update the thread tomorrow and let everyone know how it went.

THANK YOU!