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

Discussion in 'Troubleshooting and Problems' started by WildWayz, Nov 25, 2013.

  1. WildWayz

    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 :)
     
  2. Mike

    Mike XenForo Developer Staff Member

    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.
     
    OperaManiac and Chris D like this.
  3. WildWayz

    WildWayz Member

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

    japersonal Member

    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:

    So once the site is back online, I go to admin control panel to find a single entry in Server Error Log:

    @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. :)
     
  5. Jeremy

    Jeremy XenForo Moderator Staff Member

    vBulletin didn't utilize prepared statements.
     
    japersonal likes this.
  6. japersonal

    japersonal Member

    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.
     
  7. C.Stanley

    C.Stanley Active Member

    Did this work?
     
  8. japersonal

    japersonal Member

    Yes, it did, indeed. Error is gone. :)
     
    C.Stanley likes this.
  9. ForestForTrees

    ForestForTrees Well-Known Member

    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) {
      }
    }
     
  10. Mike

    Mike XenForo Developer Staff Member

    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 general, no. Transactions should resolve that if it were to occur in an actual manipulation query.
     
    ForestForTrees likes this.
  11. ForestForTrees

    ForestForTrees Well-Known Member

    Thanks so much, Mike. That's very helpful.
     
  12. Phil

    Phil Active Member

    So here i am :)

    A noob question...But where i can find this MySQL file to change this values .

    Thanks.
     
  13. Brogan

    Brogan XenForo Moderator Staff Member

    It should be in my.cnf.
     
    Phil likes this.
  14. Phil

    Phil Active Member

    And : "my.cnf." is where ? Sorry for that , but i´m discovring and learning :)

    Is that in the ftp xenforo files or in a specific MySQL File that i will find in "phpmyadmin"
     
  15. Brogan

    Brogan XenForo Moderator Staff Member

  16. Phil

    Phil Active Member

  17. Phil

    Phil Active Member

    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=
     
  18. Brogan

    Brogan XenForo Moderator Staff Member

    I recommend speaking to your host if you're not sure where the file is or how to edit it.
     
    Phil likes this.
  19. Phil

    Phil Active Member

    Done....Thanks , i´m on it ,)
     
  20. Tracy Perry

    Tracy Perry Well-Known Member

    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).
     
    Phil likes this.

Share This Page