Mysqli statement execute error problem on Dreamhost

[Chromaniac]

This error has been reported by other users and I have gone through the responses. Apparently this is a problem with the server hosting the forum and I agree with that. I have talked to Dreamhost and they blame plugins I must be running on my forum. They also point to most hit components on the server which are JS and CSS files. They also point to some IPs.

The problem is however slightly complicated. I am getting this issue for the last couple of days and it lasts only for 10-15 minutes. And it does not happen during the period where my forum has the most traffic. In fact, I face zero issues when my forum is getting its peak traffic. Not even an issue when a cronjob is running in the background taking a sql dump, compressing it and uploading it to Amazon S3.

I have not installed any new plugin in the recent days. None of the other sites on the VPS has enough traffic to cause this issue. The baffling part is that the issue starts at around 3:10pm IST and lasts till 3:30pm and fixes on its own.

I have requested Dreamhost to look into the issue again. I am on a VPS so I have enough server resources available to me. Though I believe I am using a shared MySQL server so someone other user on the same MySQL server might be causing this issue at my end.

I am looking for ideas on what could be causing this issue if my own forum is the problem.

Mysqli statement execute error : Prepared statement needs to be re-prepared

Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 632
Zend_Db_Adapter_Abstract->update() in XenForo/Session.php at line 805
XenForo_Session->saveSessionToSource() in XenForo/Session.php at line 541
XenForo_Session->save() in XenForo/Controller.php at line 412
XenForo_Controller->updateSession() in XenForo/Controller.php at line 356
XenForo_Controller->postDispatch() in XenForo/FrontController.php at line 354
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
XenForo_FrontController->run() in /home/domain/domain.co/index.php at line 13

 

[Paul B]

Apparently it's a MySQL issue: XF 1.2 - An exception occurred: Mysqli statement execute error : Prepared statement needs to be re-prepared

 

[Chromaniac]

so someone with access to the same mysql server is probably running a heavy duty backup that is hurting my site?
let's see if dreamhost can be considerate enough and move me to another server. i had gone through a similar issue few months ago when i was facing sql issues through the day. they moved me to the server i am now and everything went fine.
another option is to get a dedicated mysql vps that would double my hosting costs each month. let's see if dh can save me that money. :\ thanks!

 

[Chromaniac]

They also highlighted this error from the logs:

/dh/apache2/apache2-ps380171/logs/error.log.3.gz:[Wed Jul 22 02:42:22
2015] [error] mod_fcgid: domain=www.domain.co too many
/dh/cgi-system/php56.cgi processes (current:20, max:20), skip the spawn
request

Can anyone explain this to me?

 

[Paul B]

That's a limit imposed by your host.

 

[Chromaniac]

that part i get. is this associated to mysql being inaccessible? or number of visitors trying to load the forum?

 

[Rob]

Basically your forum is using the max allowed connections. Time for a new host, or better package.

 

[PlayStage]

Hello,

Though I believe I am using a shared MySQL server so someone other user on the same MySQL server might be causing this issue at my end.

If you are the sole benefiter of that VPS and you use it for your own site/s then you are confusing things.

I suspect that whoever covered the installation did a LAMP setup.

As far I can tell from your posts here you shouldn't have switched to a VPS as you do not have the experience to handle all problems that might occur. This translates into the fact that your provider might mislead you when you have a problem.
Why?

I have talked to Dreamhost and they blame plugins I must be running on my forum. They also point to most hit components on the server which are JS and CSS files. They also point to some IPs.


You should move to a shared hosting provider who are offering either nginx or litespeed.

Or the second suggestion would be to hire someone to take care of your server (anything background related).

Finally to provide a direction to the root cause of your problem: Apache and more cgi process spawning needed.


Kind regards,
George.

 

[WSWD]

so someone with access to the same mysql server is probably running a heavy duty backup that is hurting my site?

If you are on a VPS, nobody else should be using your MySQL server (which SHOULD be your VPS, unless they're doing something very screwy at Dreamhost). The errors you highlighted are because you're hitting limits. In that case, it's just a matter of upping those limits on your VPS, and since all Dreamhosts VPSs are managed (as far as I recall), this is something they should change for you.

If you are really on shared hosting and not a VPS, all of these errors and such make perfect sense.

 

[Chromaniac]

Dreamhost VPS does not come with their own MySQL component. You use one of their shared MySQL servers. They do offer MySQL VPS that I can switch to but that basically doubles the cost of my hosting. Though I guess it would still be cheaper than competing offerings!

I do not have the option to run MySQL on my hosting VPS. At least that's how I think it works. And yes, I understand that regular VPS powered by WHM or Plesk are completely independent solutions.

DreamHost PS MySQL - DreamHost

This is different from the standard shared MySQL server which an account uses by default, as the shared MySQL server shares other users databases and resources. With the PS MySQL, your databases and resources are protected from other users in the same was a web private server is.

As for running out of resources is concerned... I upgraded the RAM on my server from 1500MB to 8GB just to see if that helps. And nope. That did not. And no, I do not somehow magically get tons of traffic during this 20 minute period on a daily basis. I also disabled all cronjobs set on the server for testing today. No luck. I also checked XenForo's own internal cron jobs and none of them are set for this time period.

Dreamhost have finally agreed to move me to another MySQL server. I hope that fixes this problem just like back in March when I was facing similar issues but through the day. Moving to my current MySQL server fixed everything back then.

Also... My traffic has not increased much in that time period. I believe it has in fact decreased in the last couple of months.

If the migration to another MySQL server does not help, I would probably end up getting their MySQL VPS for the time being. While I look for another option.

There is this host that a lot of folks on XenForo recommend for VPS... Cannot remember the name. Any recommendations would be helpful.

 

[WSWD]

Dreamhost VPS does not come with their own MySQL component. You use one of their shared MySQL servers. They do offer MySQL VPS that I can switch to but that basically doubles the cost of my hosting. Though I guess it would still be cheaper than competing offerings!

That's the most ridiculous thing I've ever heard. A VPS should function exactly like a dedicated server, in which you can install whatever you want. You should absolutely not be using some sort of shared MySQL server, rather running MySQL right on your VPS. Shame on Dreamhost for even setting things up like that.

Upgrading the RAM to 8GB is likely not going to do anything (as you found out) because you aren't hitting a RAM limit. The errors you posted have nothing to do with RAM, they deal with Apache and PHP processes. Again, none of those should ever be limited on a VPS...well...the limits are going to be there, but you simply raise them. The host should never be able to put such limits on your VPS.

 

[Chromaniac]

Still no solution. I have tried following steps through the last couple of days.

Got Dreamhost to move me to another MySQL server. No change.
Disabled all other sites on the VPS. No change.
Disabled all plugins on the VPS. No change.
Disabled all cron jobs on the VPS. No change.
Took a SQL dump and created a new fresh database. No change.
Updated to latest stable edition of XenForo and the latest version of the theme I am running. No change.

The problem starts at around 3:10pm and lasts till 3:30pm. Rest of the day, there is not a single error in the logs. During this period, 80% of the hits would result in MySQL error. It's baffling because I cannot think of one thing that could be causing the problem during this very specific period on a daily basis.

I plan to order a MySQL VPS today on Dreamhost today and see if that changes anything.

Just curious... Almost all the tables in my database are InnoDB. 3 are MEMORY and 5 are MyISAM. Is this supposed to be normal? Any advise besides move to another host? Because the problem does not appear to be related to lack of resources on the VPS because if that was the case, the forum would struggle through the day and not during a very specific time period every day. Dreamhost is running version 5.1.56 of MySQL. XenForo requirements are fulfilled easily. They cannot offer me a more recent version of MySQL right now which was another thing I wanted to try.

I am also planning to install XenForo and the plugins that I definitely need from scratch and then see if that helps.

 

[WSWD]

The problem starts at around 3:10pm and lasts till 3:30pm.

Any CRONs running on your server during that specific time, either Xenforo or otherwise? Also, what timezone are you in? Could that perhaps be when the Dreamhost backups are made?

 

[Chromaniac]

Dreamhost guys have confirmed that they noticed no abnormality during this time period wrt load or otherwise.
XenForo cronjobs are usually scheduled to start at around 4pm. And they run pretty darn fast. I even ran Sitemap to test and it was down in a matter of seconds. Nothing in the list that could explain a 20 minute access problem.

My WordPress blogs on the same VPS connecting to same database server do not give any errors. XenForo uses something called prepared mysql statements and this is the part that is somehow breaking during this period. My Yourls installation works fine during the same period. I even have a Vanilla installation that works fine on the same server. I wish I was a database guy to understand the concept of prepared statements. But I am not!

Googling gives a very few instances of people facing this problem. One case was resolved by the web host who found some issues in the 'client script'. Could not get any more detail from that post and it was also from 2012. Most of the other threads are on this forum where the devs say that the issue is with the configuration of mysql server. In either of the cases... The problem started on its own without me changing any XenForo setting or upgrading it or installing any new plugin or upgrading any of the existing ones! It's literally driving me crazy.

I am considering starting a cloud instance on Dreamhost Dreamcompute and installing a newer version of MySQL on it to see if that would help. Every day I try a new experiment only to see it fail miserably. It's 12:12pm right now. In around 3 hours, my forum would go down with MySQL errors for 20 minutes.

 

[PlayStage]

Any CRONs running on your server during that specific time, either Xenforo or otherwise? Also, what timezone are you in? Could that perhaps be when the Dreamhost backups are made?

I bet not even dreamhost knows they are performing backups.

 

[Martok]

I bet not even dreamhost knows they are performing backups.

Yes they so, they make the backups available to admins in their control panel.

 

[Chromaniac]

Yes. They have a very comprehensive backup plan that is very visible and pretty useful. They do not offer any guarantees but from what I have seen, I have access to quite frequently backed up copies of my databases and other data. It's good to have. Plus as I mentioned in the thread that if the database server was indeed overloaded, I would be facing the same issue with my WordPress blog and Yourls installation!

 

[Martok]

I used to use DreamHost (I was on shared hosting) and they were fine initially, though went through patches of poor hardware performance. I considered their VPS solution but didn't like the fact that MySQL was separate. I eventually moved to Nimbus Hosting for their VPS solution. Best move ever.

Good luck in trying to get this resolved.

 

[Chromaniac]

I basically love how DH manages multiple domains and everything is so simple in the backend. I can access all of my sites through a single ftp login. It is just so complicated on a WHM server. Buying a domain, ssh, vpn, computing instance, dedicated and managing them from the same panel is pretty freaking impressive! I have never had so much fun managing a vpn account to be honest.

 

[PlayStage]

Learn the power of command line and you will never need any web panel ever.