Intermittent SQL errors - Too many connections [1040]

[ShadowmanUK]

Rich,

I know this is an SQL question but it is related to phpbb honest!

From time to time I get this error when trying to access my forum:-

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.

It can happen any time, sometimes quite a few times in a row. Its hard to say but it might have got worse since I put mChat on, but to be honest I can't really remember, I know mChat must be doing SQL calls so I guess the forum is doing more work? I can usually tell when it is going to happen as I get a red X on mChat and it makes a funny noise.

Anyway I contacted my host about it and they said they've not seen any issues so I'm starting to note down each time it happens, have you seen this before? Is there anything in phpmyadmin I can look at to send to my host (I assume its their problem) to help my case?

TIA

[RMcGirr83]

There are so many reasons for this so a few questions

• Are you on a shared host
• Are you using the MX record check/blacklist in the ACP
• What type of search mechanism do you have active

[ShadowmanUK]

* Are you on a shared host - Yes

* Are you using the MX record check/blacklist in the ACP - I've not changed the defaults but on checking the settings in Security it is set to Check e-mail domain for valid MX record: = Yes

* What type of search mechanism do you have active - Again the defaults Full text native with Enable fulltext updating set to Yes

Thanks

[RMcGirr83]

* Are you on a shared host - Yes

FWIW, mysql limits the number of connections allowed to the database (I think to 151 or so). If another account on your server is sucking up the connections, or not releasing them, this could be the reason why. Generally, shared server hosts will allow a max of 20 connections per account. I have no idea how many your host is allowing. You may want to ask.

This is what sort of sucks if you are on a shared environment, you are at the mercy of your "neighbors". Just one rotten neighbor can freeze all the other neighbors.

* Are you using the MX record check/blacklist in the ACP - I've not changed the defaults but on checking the settings in Security it is set to Check e-mail domain for valid MX record: = Yes

The MX check and blacklist check are to external sites which may or may not be responding quickly enough...could be another reason. Try deactivating it...then again the MX check is only used at registration, if I recall correctly.

* What type of search mechanism do you have active - Again the defaults Full text native with Enable fulltext updating set to Yes

The full text mysql search will store the search words in the database (as opposed to the other option which create a cache file) which is why I asked about that.

Thanks

No problem.

[ShadowmanUK]

Yeah the naughty neighbour sounds like the most likely one.... Hmmmm

Ok I mean its not like I pay the earth for my hosting as its self funded I can't really afford to but the package does come with 3 databases to use so I'd like to hope it could run small forum like mine, they even state in their on-line help

Databases are designed to offer an organized mechanism for storing, managing and retrieving information through the use of tables. In terms of your website, a database can be used to add features to your website such as a shopping cart, a blog or a forum.

I've just had a look at the database status in phpMyadmin got this

Connections ø per hour %
max. concurrent connections 101 --- ---
Failed attempts 1,023 k 144.21 0.62%
Aborted 30 k 4.27 0.02%
Total 166 M 23.40 k 100.00%

(Doesn't format well I know... )

Not sure if any of that info will be of use for support or not I'm pretty sure they know centrally if the shared SQL isn't performing well!

Well I guess all I can do for now is keep a log of when it happens and then raise another case and hope something gets sorted, its just a bit poor really.. god know's what'll happen if it ever gets really busy.

[ShadowmanUK]

Rich,

A bit more if I may.

Ok so lets say I decide to go for a dedicated server. What would be the best way to migrate the forum from old shared server to new dedicated?

I can think of two ways but I'm not sure which is best.

Option 1

1. Backup current database via phpBB ACP
2. Create a new MySQL database (same version of MySQL) on the new server (unsure whether it needs to be the exact same name or not?
3. Install latest version of phpBB
4. FTP backup of old phpBB database to store on new server
5. Restore database
6. FTP all forum files from old server and make database name change in config.php (if new db name is different)
7. Done

Option 2

1. Backup current database via phpMyAdmin (anything special needed or just an export?)
2. Create a new database on new server via phpMyAdmin and restore the backup
3. FTP all forum files from old server
4. Done

Thanks

[RMcGirr83]

Option 2 (nothing special via phpmyadmin) or use mysqldumper (google for it).

FWIW, I don't think you need a dedicated server, a VPS should do.

[ShadowmanUK]

Cheers

Yeah I need to have a think really. Pro's and Con's for both. The dedicated one I'm looking at costs a bit more than a VPS but its simplified so a lot of it is on-rails and nice user interfaces and they take care of all security updates etc etc which I like.

You can get root access if you want but then your own your own.

The VPS option is slightly cheaper but a lot more flexible, root access and everything that comes with it, but I'd have to keep on top of my own updates etc... so.

It needs more thought but the forum side of it sounds easy enough to migrate thanks for that. :thumbup:

[RMcGirr83]

So, how's it going? Any update?

[ShadowmanUK]

I started logging each time it happens and sent that in to support on the 17th as it was happening all the time that day, they acknowledged the fault and said they were looking at it. Its only happened once since. I will chase them again in a few days and keep monitoring. Fingers crossed.

[ShadowmanUK]

Well I've not seen the errors for a while but the forum is pretty slow sometimes so I'm looking into an VPS further. Could I ask you another question?

According to my forum Board Stat's I'm using:- MySQL(i) 5.0.68-percona-3-log

What do I need to look out for when I get a new VPS, does the version of MySQL have to be the same or higher for it to work? Also it looks like I'm using MySQLi does that matter, will a backup and a restore using phpMyadmin just "sort that out" for me, I don't have to worry about that side of things?

Sorry to ask, I'm getting more confident with the forum side of things but I'm weak on the database side

TIA

[ShadowmanUK]

I've checked with the VPS people I'm looking to go with and asked about the MySQL version that comes pre-installed and they have stated they use this:-

mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1

Do you forsee any problems with backing up the old via phpAdmin and restoring to the new based on those versions?

[RMcGirr83]

No I don't see that you would have any problems. A Mysqli database is very similar to MySql. MySQLi supports some things that the old MySQL extension doesn't. Things like prepared statements, multiple statements, and transactions off the top of my head.

[ShadowmanUK]

Thanks its good to have a bit of reassurance, I mean the main reason for going to VPS would be to improve the forum speed etc, and if I found I couldn't port it easy or had to start again from scratch (losing all posts & users) it would be a bit of nightmare really.

[RMcGirr83]

You should be fine and if you get stuck just holler.