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

W

WildWayz

Member
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 :)
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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()
Click to expand...

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) {
}
}
Click to expand...

@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. :)
 
Upvote 0 Downvote
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) {
  }
}
 
Upvote 0 Downvote
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.

ForestForTrees said:
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?
Click to expand...
In general, no. Transactions should resolve that if it were to occur in an actual manipulation query.
 
Upvote 0 Downvote
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=
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

AndryOsminin
  • Question Question
XF 2.2 MySQL statement prepare error [1146]
Replies
5
Views
49
philmckrackon
philmckrackon
mcatze
Duplicate [XF\Db\Exception] MySQL statement prepare error [1054]: Unknown column 'xf_mg_media_item.album_state' in 'where clause' in src/XF/Db/AbstractStatement
Replies
1
Views
211
Chris D
Chris D
jb9826
  • Question Question
XF 1.5 Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
Replies
0
Views
538
jb9826
jb9826
C
  • Solved
XF\Db\Exception: MySQL statement prepare error [2006]: MySQL server has gone away
Replies
7
Views
1K
canina
C
Spinix-
  • Question Question
MySQL query error: Prepared statement needs to be re-prepared
Replies
9
Views
952
Spinix-
Spinix-
Back
Top Bottom