Too many connections [1040]

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
action_man
Registered User
Posts: 656
Joined: Wed May 22, 2002 10:15 am
Location: England Nr Europe
Contact:

Too many connections [1040]

Post by action_man »

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 ...
User avatar
pentapenguin
Former Team Member
Posts: 11030
Joined: Thu Jul 01, 2004 4:15 am
Location: GA, USA

Re: Too many connections [1040]

Post by pentapenguin »

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.
Support Resources: Support Request Template
If you need professional assistance with your board, please contact me for my reasonable rates.
User avatar
davss
Registered User
Posts: 51
Joined: Fri Dec 14, 2007 12:24 pm
Contact:

Re: Too many connections [1040]

Post by davss »

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
User avatar
KevC
Support Team Member
Support Team Member
Posts: 70389
Joined: Fri Jun 04, 2004 10:44 am
Location: Oxford, UK
Contact:

Re: Too many connections [1040]

Post by KevC »

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.
-:|:- Support Request Template -:|:-
Image
Cheap UK Hosting
"In the land of the blind the little green bloke with no pupils is king - init!"
User avatar
davss
Registered User
Posts: 51
Joined: Fri Dec 14, 2007 12:24 pm
Contact:

Re: Too many connections [1040]

Post by davss »

But there was no other people accessing the new server apart from myself (we changed the URL)?
jmjm003
Registered User
Posts: 275
Joined: Sat Dec 22, 2007 7:05 pm

Re: Too many connections [1040]

Post by jmjm003 »

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.
User avatar
KevC
Support Team Member
Support Team Member
Posts: 70389
Joined: Fri Jun 04, 2004 10:44 am
Location: Oxford, UK
Contact:

Re: Too many connections [1040]

Post by KevC »

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.
-:|:- Support Request Template -:|:-
Image
Cheap UK Hosting
"In the land of the blind the little green bloke with no pupils is king - init!"
User avatar
timothy_cn
Registered User
Posts: 5
Joined: Wed May 07, 2008 9:52 am

Re: Too many connections [1040]

Post by timothy_cn »

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... :mrgreen:
jmjm003
Registered User
Posts: 275
Joined: Sat Dec 22, 2007 7:05 pm

Re: Too many connections [1040]

Post by jmjm003 »

What server OS are you running?
User avatar
davss
Registered User
Posts: 51
Joined: Fri Dec 14, 2007 12:24 pm
Contact:

Re: Too many connections [1040]

Post by davss »

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
Registered User
Posts: 65
Joined: Wed Oct 03, 2007 2:55 am

Re: Too many connections [1040]

Post by Guybor »

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?
User avatar
davss
Registered User
Posts: 51
Joined: Fri Dec 14, 2007 12:24 pm
Contact:

Re: Too many connections [1040]

Post by davss »

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
User avatar
davss
Registered User
Posts: 51
Joined: Fri Dec 14, 2007 12:24 pm
Contact:

Re: Too many connections [1040]

Post by davss »

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.
Attachments
Picture 2.png
Picture 2.png (69.4 KiB) Viewed 3283 times
eiffel
Registered User
Posts: 23
Joined: Mon Jul 08, 2002 11:54 am

Re: Too many connections [1040]

Post by eiffel »

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?
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 27130
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: Too many connections [1040]

Post by Paul »

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.
Locked

Return to “[3.0.x] Support Forum”