• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 An exception occurred: Mysqli statement execute error : Prepared statement needs to be re-prepared

#1
Hi guys

I've been running Xenforo for a while now and never come across this problem until end of last week:

Code:
An exception occurred: Mysqli statement execute error : Prepared statement needs to be re-prepared in /home/jpbsuppo/public_html/forums/library/Zend/Db/Statement/Mysqli.php on line 214

    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 825
    Zend_Db_Adapter_Abstract->fetchOne() in XenForo/Error.php at line 55
    XenForo_Error::unexpectedException() in XenForo/Application.php at line 363
    XenForo_Application::handleException()
I was running 1.2.1, so I got the web host to restart MySQL as mentioned, then ran the upgrade to 1.2.3. It has since fallen over 4 times since last week. Nothing has changed plugin-wise and the server is on shared hosting. The web host is not having any reports from other clients with regards to this, and they have never seen the error on Xenforo either - but a restart will fix it for up to 24hrs.
The forum isn't heavily active - maybe 3 posts a day (we're about to launch our first paintball marker - will pick up after that!).

Any ideas? Thanks :)
 

Mike

XenForo developer
Staff member
#2
Is it happening at a particular time? If so, there's likely a backup process going on. Unfortunately, this appears to be internal to MySQL so there's not much we can do within XenForo. Your host may be able to increase the MySQL table_definition_cache and/or table_open_cache values to help resolve the issue.
 
#3
Thanks Mike - the backup runs every night at 2am, but this seems to crash at any time throughout the day. I'll suggest the MySQL config change above.
 
#4
I have just experienced the same issue. Installed XenForo 10 hours ago, imported a vBulletin site and everything was working fine... until 30 minutes ago, when the site was down for a while, displaying the following error message:

An exception occurred: Mysqli statement execute error : Prepared statement needs to be re-prepared in /home/host4534r/public_html/library/Zend/Db/Statement/Mysqli.php on line 214

  1. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
  2. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
  3. Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 825
  4. Zend_Db_Adapter_Abstract->fetchOne() in XenForo/Error.php at line 55
  5. XenForo_Error::unexpectedException() in XenForo/Application.php at line 363
  6. XenForo_Application::handleException()
So once the site is back online, I go to admin control panel to find a single entry in Server Error Log:

Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Prepared statement needs to be re-prepared - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, 35 minutes ago
Stack Trace
#0 /home/host4534r/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/host4534r/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/host4534r/public_html/library/XenForo/Model.php(219): Zend_Db_Adapter_Abstract->query('?????SELECT rou...', 'public', 2)
#3 /home/host4534r/public_html/library/XenForo/Model/RoutePrefix.php(41): XenForo_Model->fetchAllKeyed('?????SELECT rou...', 'original_prefix', 'public')
#4 /home/host4534r/public_html/library/XenForo/Model/RoutePrefix.php(54): XenForo_Model_RoutePrefix->getPrefixesByRouteType('public', true)
#5 /home/host4534r/public_html/library/XenForo/Link.php(479): XenForo_Model_RoutePrefix->getPrefixesForRouteCache('public')
#6 /home/host4534r/public_html/library/XenForo/Link.php(396): XenForo_Link::_loadHandlerInfoForGroup('public')
#7 /home/host4534r/public_html/library/XenForo/Link.php(362): XenForo_Link::_getPrefixHandler('public', 'threads', true)
#8 /home/host4534r/public_html/library/XenForo/Link.php(93): XenForo_Link::_buildLink('public', 'threads', Array, Array, 'threads')
#9 /home/host4534r/public_html/showthread.php(31): XenForo_Link::buildPublicLink('canonical:threa...', Array)
#10 {main}
Request State
array(3) {
["url"] => string(131) "http://www.comunidadhosting.com/dom...to-con-los-de-mi-servidor-y-que-funcione.html"
["_GET"] => array(1) {
["t"] => string(5) "16858"
}
["_POST"] => array(0) {
}
}
@Mike said it's "likely a backup process going on" in a previous reply and that it "appears to be internal to MySQL so there's not much we can do within XenForo". Well... all I can say is that despite the fact I'm really liking XenForo over vBulletin, this error (and the related downtime) never ever happened to me in several years using vBulletin and the same web hosting provider.

What is it so different in the way XenForo works that may cause such error message?

Thanks in advance for your assistance with this issue. :)
 
#6
Thanks for your reply @Jeremy :)

I've followed @Mike's advice and modified MySQL's table_definition_cache (from 256 to 1,024) and table_open_cache (from 64 to 512). I hope this will help to avoid such situation.
 

ForestForTrees

Well-known member
#9
I get these errors periodically and they are unsettling. I understand that they are probably the result of a problem with my MySQL server but would like to get to the bottom of the error.

I got one this morning and found a post about XF from the Zend Framework DB mailing list archive to be helpful. Specifically, in the second post, BillKarwin wrote, "Some people have reported the error also happens if there's a concurrent mysqldump running."

I think that this may have happened to me this morning. At 11:47 AM today, I got one of these 'mysqli reprepared' type errors in my XF error log. Coincidentally, I also upgraded from 1.2.3 -> 1.2.4 this morning. The output of my pre-upgrade mysqldump has a last modified date of 11:47 AM today as well.

Returning to the post I linked to above, BillKarwin wrote, "This error happens when a DDL operation (create, alter, drop, truncate, etc.) occurs in the moment between a SQL statement prepare() and execute()." This makes it sound like a concurrency thing, at least in this case. Perhaps someone made a request for a user account just as mysqldump was running. I find that a little reassuring to know what's up.

I'll try to implement Mike's suggestions above, but it will be a while before I can do enough research (mysqli, prepared statements, mysql configuration, etc.) to feel confident going ahead with them.

In the meantime, my big concern is data loss or a forum state inconsistency that could lead to data loss or downtime. Is this something I need to be worried about?

For reference, here's the exact text of this morning's error: (taken from admin.php?logs/server-error )
Code:
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Prepared statement needs to be re-prepared - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, Today at 11:47 AM
Stack Trace
#0 /home/ptpn/public_html/forum/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/ptpn/public_html/forum/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/ptpn/public_html/forum/library/XenForo/Model.php(219): Zend_Db_Adapter_Abstract->query('?????SELECT use...', Array, 2)
#3 /home/ptpn/public_html/forum/library/XenForo/Model/UserField.php(44): XenForo_Model->fetchAllKeyed('?????SELECT use...', 'field_id')
#4 /home/ptpn/public_html/forum/library/XenForo/ControllerPublic/Member.php(278): XenForo_Model_UserField->getUserFields(Array, Array)
#5 /home/ptpn/public_html/forum/library/XenForo/FrontController.php(337): XenForo_ControllerPublic_Member->actionMember()
#6 /home/ptpn/public_html/forum/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /home/ptpn/public_html/forum/index.php(13): XenForo_FrontController->run()
#8 {main}
Request State
array(3) {
  ["url"] => string(46) "http://www.mydomain.org/forum/members/someuser.1776/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}
 

Mike

XenForo developer
Staff member
#10
You can see the bug report details here: http://bugs.mysql.com/bug.php?id=42041 There are a number of people getting it -- it's simply tied to the binary/prepared statement protocol in MySQL.

In the meantime, my big concern is data loss or a forum state inconsistency that could lead to data loss or downtime. Is this something I need to be worried about?
In general, no. Transactions should resolve that if it were to occur in an actual manipulation query.
 

Phil

Active member
#17
Okay...So i have no acces to this file through PHPMy ADMIN

In the .my.cnf i can only see my ftp admin acces:

In the FTP if i edit the file :

[client]
password=
user=
 

Tracy Perry

Well-known member
#20
If you are on a shared hosting environment, then you will NOT have access to the my.cnf file. And generally on shared hosting they will not change their my.cnf paramaters for one of the "clients".
To have access to it you will have to be on a VPS or a dedicated server.
Normally it will be located in either /etc/mysql/my.cnf or in /etc/my.cnf (depending on the OS used).