Too many connections [1040]

action_man · Post by **action_man** » Wed Apr 23, 2008 8:25 pm

General Error
SQL ERROR [ mysqli ]

Too many connections [1040]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

I have a site just for my own pleasure, so no other people visit, well very nearly nobody, but tonight its saying too many connections ...

pentapenguin · Post by **pentapenguin** » Wed Apr 23, 2008 8:34 pm

Sorry there's nothing you can do to fix this error yourself -- you need to ask your host to either up the maximum number of connections or to fix the problem.

davss · Post by **davss** » Thu May 08, 2008 1:51 pm

Hi there!

Usually the problem lies in wrong username and password in config.php. What if those details are correct and your database still keeps randomly crashing giving you the above error message?

I upgraded phpBB 2.022 to phpBB 3.0.0 (3.0.0 -> 3.0.1 upgrade cause login problems) and first thing we got was our MySQL database died. We had no such problems before. That on the same host. Now we decided to move away from that hosts to a brand new one fully patched Centos server (details below). The same problem occurred with only one forum on a dedicated root server (same as the old one)! We run 'show full processlist;' query in MySQL DB and it looks like there is a connection pulling problem.

Old server config:
mysqld Ver 5.0.22 for redhat-linux-gnu on i686 (Source distribution)
PHP Version => 5.1.6

New server config:
mysqld Ver 5.0.22 for redhat-linux-gnu on i686 (Source distribution)
PHP Version => 5.1.6

What we've tried to resolve the problems [BELOW ARE EXPERIMENTAL CODE CHANGES]:
config.php

$dbms = "mysql"; //changed from mysqli to mysql. Worked fine in phpBB2 with set to mysql4

or set commented out line in:
includes/constants.php

define('PHPBB_DB_NEW_LINK', true); //currently disabled it to test above solution

There is another problem which may be related to it namely even though the forum's up and not crashing settings changes don't get stored in the DB and am getting a white blank screen upon submit in ACP.

Will keep you updated on both problems.

Regards,
David
http://www.davss.com
http://www.webcoder.eu

Post by **KevC** » Thu May 08, 2008 2:49 pm

davss wrote: Usually the problem lies in wrong username and password in config.php.

Not with that error.
That specifically says there's too many connections. That's purely a hosting issue.

davss · Post by **davss** » Thu May 08, 2008 3:04 pm

But there was no other people accessing the new server apart from myself (we changed the URL)?

jmjm003 · Post by **jmjm003** » Thu May 08, 2008 8:41 pm

It's called shared hosting. The same thing happened to me. The host tracked down the Mysql DB hog and all was fine. Keep in mind, I only have about 10 or so members connected at a time. So it wasn't me causing the error, it was someone else causing it.

Contact your host and let them know about the problem. They will find who is causing it. If they do not, I suggest finding a new host.

Post by **KevC** » Thu May 08, 2008 8:46 pm

davss wrote:But there was no other people accessing the new server apart from myself (we changed the URL)?

On shared hosting there can be many many sites on one server. The total for that server is being hit.

timothy_cn · Post by **timothy_cn** » Fri May 09, 2008 3:55 am

I got the same error.
And I'm using my own pc server, there's very few connections, essentials only me for testing, because I just set it up days ago. So I don't think all problem comes from the MYSQL sever side. Therefore I don't think you can solve this by simply increase the MAX_CONNECTIONS of MYSQL server. There may be some flaws in the forum side.
I'm checking this, and report the results here if I could figure it out.
Good luck to me...

jmjm003 · Post by **jmjm003** » Fri May 09, 2008 8:56 pm

What server OS are you running?

davss · Post by **davss** » Wed Aug 27, 2008 8:26 pm

I think we have solved the problem of MySQL DB falling over.

Basically, by default tables are MyISAM which means each time DB is read whole table gets locked hence no other operations could be carried out at the same time.
What we did was converted most tables to InnoDB that supports row locking apart from php_posts that has to stay MyISAM to support full text search.

Such conversion solves the above problems for large forums such us ours with plenty of users (10.000) and posts (80.000).

Regards,
David
webcoder.eu

Guybor · Post by **Guybor** » Wed Sep 24, 2008 2:18 am

davss wrote:Basically, by default tables are MyISAM which means each time DB is read whole table gets locked hence no other operations could be carried out at the same time.
What we did was converted most tables to InnoDB that supports row locking apart from php_posts that has to stay MyISAM to support full text search.

How did you go about doing this?

davss · Post by **davss** » Thu Oct 16, 2008 12:57 pm

It worked for us and the same error never came back.

Please note I am writing this article in a hurry and haven't tested the below steps (we changed on our forums long time ago).

I do not take any responsibility for any data loss although with a proper back up you should be OK. I'll try to improve this post as time allows.

1) Make your forum read only
2) Do a FULL back up in ACP
3) make a copy of the backed up file in your /store/ folder in a safe place outside your phpbb code folder
4) gunzip/untar your file in the /store/ folder so it's ending with .sql only (if you used compression e.g. sql.gz in ACP backup you'll need to gunzip i.e. uncompress YOUR_FILE.sql.gz)
5) edit the uncompressed .sql file in a UTF-8 editor and find/replace 'MyISAM' to 'InnoDB' (to dbl check spelling find ENGINE= entry in CREATE TABLE SQL statements. Please note, too large files might cause problems when opening and I used vim)
6) drop all your phpbb tables (an article at http://davss.com/forum/viewtopic.php?f= ... 20b00669f2)
7) change all capitals and run this command (your DB connection details should be in config.php file)

Code: Select all

mysql -u DB_USERNAME -pDB_PASSWORD DB_NAME < YOUR_SQL_FILE.sql

8) go to ACP Maintenance section and restore from the backup file again (this will preserve your special characters that usually get messed up when importing phpbb tables via command line)

Turn back on your forum. If anything goes wrong just move the .sql file and copy back the original file (3) and restore in ACP

Some useful stuff to read:
http://dev.mysql.com/doc/refman/5.0/en/ ... nnodb.html
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Good luck,
David

davss · Post by **davss** » Mon Oct 20, 2008 3:21 pm

Make sure you keep your phpbb_posts table as MyISAM otherwise you won't be able to recreate your indexes later on and the following error will occur:

MySQL fulltext indexes can only be used with MyISAM tables.

eiffel · Post by **eiffel** » Wed Jun 03, 2009 6:14 pm

davss wrote:It worked for us and the same error never came back.

Does this work - I've been having trouble transferring to InnoDB so would like to know if this is *the* solution for the "Too many connections" bug?

Have other people tried this?

Post by **Paul** » Wed Jun 03, 2009 6:30 pm

That error is not a bug, but a configuration issue. You will need to contact your host to solve the error. Changing it to innodb will not solve it.

advertisements