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

XF 1.3 Mysqli statement execute error 2 pages full of them

Discussion in 'Troubleshooting and Problems' started by XxUnkn0wnxX, Apr 19, 2014.

  1. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    i have two page full of this error:

    Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214

    every day i get errors something like or similar
    how do i stop them from occoring at all? is it the lack of ram for the Mysql data base? to many threads happening at the same time? is there a way to reduce/stop these errors from happening ?

    i wish to run a clean and healthy server and site with no errors....

    [​IMG]

    Code:
    Error Info
    Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214
    Generated By: 2pac, Today at 1:43 AM
    Stack Trace
    
    #0 /home/unkn0wn/public_html/forums/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
    #1 /home/unkn0wn/public_html/forums/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
    #2 /home/unkn0wn/public_html/forums/library/XenForo/Model/Like.php(186): Zend_Db_Adapter_Abstract->query('?????INSERT IGN...', Array)
    #3 /home/unkn0wn/public_html/forums/library/Waindigo/Rewards/Extend/XenForo/Model/Like.php(37): XenForo_Model_Like->likeContent('post', 8879, 1, 20, NULL)
    #4 /home/unkn0wn/public_html/forums/library/XenForo/ControllerPublic/Post.php(393): Waindigo_Rewards_Extend_XenForo_Model_Like->likeContent('post', 8879, 1)
    #5 /home/unkn0wn/public_html/forums/library/XenForo/FrontController.php(347): XenForo_ControllerPublic_Post->actionLike()
    #6 /home/unkn0wn/public_html/forums/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #7 /home/unkn0wn/public_html/forums/index.php(13): XenForo_FrontController->run()
    #8 {main}
    
    Request State
    
    array(3) {
      ["url"] => string(47) "http://portalcentric.net/forums/posts/8879/like"
      ["_GET"] => array(0) {
      }
      ["_POST"] => array(4) {
        ["_xfRequestUri"] => string(49) "/forums/threads/regarding-rte-modding-tools.1403/"
        ["_xfNoRedirect"] => string(1) "1"
        ["_xfToken"] => string(8) "********"
        ["_xfResponseType"] => string(4) "json"
      }
    }
    
    i have also been experiencing error pages when i save large amounts of data say i change 15-20 fields in permissions on a user group i also have around 10+ different user groups.

    like when i would save the loading indicator on my browser keeps spinning i tends to get this error when it times out. and i have to press the back button and try again 3 times before it actually saves.

    i also get this same issue when trying to update my add ones when i try to import/upload 3-5 zip files or xml files at the same time or check for updates. this all using the Add One Install and upgrade (add one) i also sometimes get the internal server error pages when i am rebuilding templates its like it times out...

    it is like there is some much going on real time data being stored and obtained while me trying to store more data the data base cannot handle it..

    this cannot go on any more how do i stop and fix this what do i need to do to improve the performance on my server? what services such as Mysql how should they be improved..

    but i am unsure what the error could be
    could be to much information processed by the data base at the same time
    + how would i fix this? how would i limit this what multi threading module would i need for Mysql?

    this the error i get when it times out..

    Code:
    Internal Server Error
    
    The server encountered an internal error or misconfiguration and was unable to complete your request.
    
    Please contact the server administrator, webmaster@portalcentric.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.
    
    More information about this error may be available in the server error log.
    and as you can see i get a 500 error code when i preform the action save/POST

    Code:
    Hidden - [19/Apr/2014:15:14:33 +1000] "POST /forums/admin.php?user-group-permissions/new-member.2/save HTTP/1.1" 500 547 "http://portalcentric.net/forums/admin.php?user-group-permissions/new-member.2/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:28.0) Gecko/20100101 Firefox/28.0"
     
    Last edited: Apr 19, 2014
  2. Tracy Perry

    Tracy Perry Well-Known Member

    If on a shared hosting account you are probably bumping the resource limits.
    If on a VPS, then you will need to do some optimization.
    If all of them have (or most)
    Code:
    Waindigo_Rewards_Extend_XenForo_Model_Like
    in them then you should disable that add-on and see if it helps.
     
  3. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    well this my apache error logs from me doing the save action:

    Code:
    [Sat Apr 19 15:15:13 2014] [warn] [client hidden] mod_fcgid: read data timeout in 40 seconds, referer: http://portalcentric.net/forums/admin.php?user-group-permissions/new-member.2/
    [Sat Apr 19 15:15:13 2014] [error] [client hidden] Premature end of script headers: admin.php, referer: http://portalcentric.net/forums/admin.php?user-group-permissions/new-member.2/
    [Sat Apr 19 15:15:22 2014] [warn] mod_fcgid: process 1059 graceful kill fail, sending SIGKILL
    i restarted MySQL and int he 1st minute i was able to save but then the next minute i could not...

    and i am on a dedicated server...

    well what is there to optimize?

    i am unsure what settings to tweak in relation to these issues

    i know that there is a lot of information being processed by MYSQL and i know that there are many people doing activitys such as talking in SB, posting threads updating there profile then there are con jobs running then there is the auto promotion system, rebuilding templates <-- some times, trophies and goals and many other tasks running in the back ground. how do i open up you could say the hole/PIPE line to allow more tasks to be done without any time out..

    increasing the time out is one but does not solve the issue
    could having a faster uplink help? atm its 100mbps

    here is my my.cnf preferences but i would not have a clue what would optise it or how i can allowcate more ram to mysql or if that will even improve its preformance...

    Code:
    [mysqld_safe]
    log-error="/var/log/mysqld.log"
    pid-file="/var/run/mysqld/mysqld.pid"
    [mysqld]
    thread_cache_size=128
    slow_query_log=1
    symbolic-links=0
    datadir="/var/lib/mysql"
    innodb_flush_method=O_DIRECT
    open_files_limit=3072
    innodb_file_per_table=1
    character_set_server=utf8
    innodb_additional_mem_pool_size=30M
    wait_timeout=60
    log-output=TABLE
    innodb_buffer_pool_size=937M
    key_buffer_size=32M
    table_open_cache=8K
    table_definition_cache=3072
    max_user_connections=300
    innodb_log_buffer_size=29M
    socket="/var/lib/mysql/mysql.sock"
    max_heap_table_size=119M
    myisam_sort_buffer_size=16M
    innodb_thread_concurrency=4
    query_cache_size=200M
    table_cache=8192
    connect_timeout=10
    collation_server=utf8_unicode_ci
    long_query_time=5
    max_connections=8000
    max_allowed_packet=16M
    tmp_table_size=120M
    default-storage-engine=MyISAM
     
  4. Tracy Perry

    Tracy Perry Well-Known Member

  5. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    well here is what i am will to share for SPECS:

    [​IMG]
    Code:
    Processor Information
    Total processors: 4
    
    Processor #1
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E3-1220L V2 @ 2.30GHz
    
        Speed
            2300.022 MHz
    
        Cache
            4096 KB
    
    Processor #2
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E3-1220L V2 @ 2.30GHz
    
        Speed
            2300.022 MHz
    
        Cache
            4096 KB
    
    Processor #3
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E3-1220L V2 @ 2.30GHz
    
        Speed
            2300.022 MHz
    
        Cache
            4096 KB
    
    Processor #4
    
        Vendor
            GenuineIntel
    
        Name
            Intel(R) Xeon(R) CPU E3-1220L V2 @ 2.30GHz
    
        Speed
            2300.022 MHz
    
        Cache
            4096 KB
    Code:
    Memory Information
    
    Memory: 3904120k/4718592k available (5357k kernel code, 524708k absent, 289764k reserved, 6984k data, 1284k init)
    
    Code:
    System Information
    
    Linux HIDDEN 2.6.32-431.11.2.el6.centos.plus.x86_64 #1 SMP Tue Mar 25 21:36:54 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
    
    Code:
    Current Memory Usage
    
                 total       used       free     shared    buffers     cached
    Mem:       3922428    3248484     673944          0     197320    1697952
    -/+ buffers/cache:    1353212    2569216
    Swap:      4194296     202412    3991884
    Total:     8116724    3450896    4665828
    
    Code:
    Current Disk Usage
    
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/vda1       892G  240G  608G  29% /
    tmpfs           1.9G     0  1.9G   0% /dev/shm
    /usr/tmpDSK     4.0G  714M  3.1G  19% /tmp
    
    Yes i am running centos.plus.x86_64

    i should have aprox 4gb ram
    MySQL Ver: 5.5.36
    PHP Ver: 5.4.26
    Apache Ver: Apache/2.2.26 (Unix)

    also with default-storage-engine=MyISAM i am unsure i had this as either defult or i just copied some configuration that some one else said it would help optimse mysql..

    all this stuff like innodb_file_per_table
    and query_cache
    i know what time out means but the rest i am unsure..

    i know what cache means but is that cache being stored in Ram? or HDD?

    like i dont know what to tweak cozz i don't what what each string refers to or how mysql works... back to front.

    i know from a basic point of view that there are a lot information being processed frequently and i understand me me coming along saving the user group with signification changes could overload this. there is not enough room for any more data to be processed so it gives me that error..

    but on the technical side i am unsure what this could cause. is it lack of ram?

    is is not enough ram allocated to these buffers, table caches?

    or if i wanted to limit the amount of queries run at the same time like

    when i am saving the user group it has to wait till it is my turn or wait until there are enough free resources to process my request instead of trying hard to do it all at the same time...

    i bean reading about

    mysql-engines-myisam-vs-innodb

    http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb

    and this is somewhat familiar to my issue:

    If a read is slow or hasn't completed and a read/write is waiting on the first read to finish then the MyISAM table referenced in the read is held in a locked state till the resultset is made available to the query. This also causes a rise in the load average on the server and slows your site down. During this time no reads or writes can complete ofcourse as MyISAM only has table-level locking.

    so if i set it to innodb would it help?

    but wouldn't xenforo DB would need to be using innodb to make it compatible?

    some where in the article it said tall the tables would need to be converted

    also it say
    http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
    that innodb uses more ram.. then the other one..
     
    Last edited: Apr 19, 2014
  6. Sheratan

    Sheratan Well-Known Member

    I found your source of problem.

    Do you have a backup copy of your default mysql configuration?

    A protip:
    You can mess with Apache Configuration.
    You can mess with PHP Configuration.

    But never ever put some strange configuration to your MySQL without knowing their impact to your database.

    The result of messing with database is from corrupted database to database is missing a ka unusable.
     
  7. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    i don't have /etc/httpd/conf.d/fcgid.conf

    no such file all i have is these:

    Code:
     httpd.conf.work.ACzX76dxTrck9Y8Z  modsec2.conf.cpbackup4     php5.version
    ../                           httpd.conf.work.gphi4fkleBCq7N4i  modsec2.conf.cpbackup5     php.conf
    access.conf                   httpd.conf.work.nzSFQNb92dZvj9zI  modsec2.conf.cpbackup6     php.conf.cache
    error_log                     httpd.conf.work.Yux5Jn1FZDTP5pdb  modsec2.conf.cpbackup7     php.conf.fastcgi
    extra/                        includes/                         modsec2.conf.cpbackup8     php.conf.suphp
    httpd.conf                    magic                             modsec2.conf.cpbackup9     php.conf.yaml
    httpd.conf_back               mime.types                        modsec2.conf,v             php.version
    httpd.conf.bak                mod_antiloris.conf                modsec2.user.conf          sites/
    httpd.conf.datastore          mod_bandwidth.conf                modsec2.user.conf.default  srm.conf
    httpd.conf.ea-make-install    modsec2.conf                      modsec2.user.conf.none     ssl.crt/
    httpd.conf.ea-make-install,v  modsec2.conf.cpbackup1            modsec2.whitelist.conf     ssl.key/
    httpd.conf.ea-orig            modsec2.conf.cpbackup10           original/
    httpd.conf.easyapache_save    modsec2.conf.cpbackup2            pagespeed.conf
    httpd.conf,v                  modsec2.conf.cpbackup3            pagespeed_libraries.conf
    
    Nope...

    i could probably ask my hosting if they could give a txt version of all the default config..

    but it doesn't change the fact that i am having this issue.. even with defualt config may not resolve it

    as i add more features, more groups more add ones therefore more work for mysql and next thing i know i am having these issues again..

    i also have around 90+ add ones installed on my site...

    and i also have google ads i have a lot of stuff going on + many members i think 6k or more still growing...

    so need to tweak this some how to allow for more speed but not pushing it to the edge where it is unable to handle the load.. which is near that edge now...
     
  8. Tracy Perry

    Tracy Perry Well-Known Member

    ANYTIME you edit an important file like my.cnf, I would suggest you copy the working one to something like my.cnf.original and then do your mods to it (in this example to my.cnf) and save the modded one also as my.cnf.041914.rev-a... with the numbers being the date and the different revisions.

    I'm sorry I can't be more help right now... I'm tracking down a problem with an add-on displaying unsecure content on my SSL sites.
     
  9. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    k then. thanks any way

    i have contacting my hosting if they could help me out...
     
  10. Tracy Perry

    Tracy Perry Well-Known Member

    Do you have phpMyAdmin installed so you can look at the DB's and see if they are INNODB or MYIasm?
    And I'm not a programmer by any means - but forcing the default to MYIAsm may have forced some of the add-ons to use it instead of INNODB.
     
  11. Tracy Perry

    Tracy Perry Well-Known Member

    If nothing else, give @MattW or @Slavik a shout. They may be able to help you through it for a reasonable fee.
     
    XxUnkn0wnxX likes this.
  12. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    also out of curiosity i am not doing this any time soon.. how safe is upgrading mysql?

    [​IMG]

    i know it is once way only and you have to stop the service and back up all the databases and so on.. i know how to do that already but
    what about data loss? data corruption?

    and on the original site they say about checking and rebuilding tables and other stuff like that... which i has no clue how to do... would nice if there was some command or something i could run to check if all my data bases are supported by mysql 5.6

    and if there where any inconsistency how would i upgrade my data bases to support 5.6?

    this just for theory wise.. or is 5.6 offers better preformance then 5.5
    in the image as u can see MyISAM is the default storage engine for 5.5
     
  13. MattW

    MattW Well-Known Member

    It's fine to do. I've done it on my Cpanel VPS, and it went without a hitch.

    As for the original issue. What are you table types on the database? Are they all MyISAM?
     
  14. Tracy Perry

    Tracy Perry Well-Known Member

    I've got a feeling they are but he hasn't responded when I asked if he had phpMyAdmin to easily check with. I've been up all night playing on modifying the sites and about to go to bed or I'd kick into Skype with him.
     
  15. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    yes i have phpMyAdmin...

    as for my db's i am unsure how to cheack if they have the MyISAM

    i have done basic data base editing but only basic nothing complex and i only used phpMyAdmin.
     
  16. Tracy Perry

    Tracy Perry Well-Known Member

    For anyone reading... @XxUnkn0wnxX's site is back up and running.
    Resolved by removing mysql, re-installing it, resetting the root password, exporting the existing DB and creating a new user and DB for his forum.
     
  17. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    well my hosting said this about it:

    Hello,

    Thank you for contacting us!

    It appears that this is a series of issues leading to the Internal Server Errors (500 status code). Looking at the situation, it appears that the MySQL queries are getting "dead locked", which occurs when two threads essentially lock eachother. While the transaction (query) sits deadlocked in MySQL, FastCGI (the PHP handler) waits a specific amount of time until it stops waiting for MySQL. When this happens, the PHP process killed that is making these transactions to MySQL.

    The problem should be avoidable by preventing the deadlocks. However, these depend entirely on the queries being run. We suspect that something that XenForo is doing is responsible for this. Allowing FastCGI to wait longer may allow the queries to complete, but it also may not. It's possible (and actually rather likely) that these locks will simply sit and stay in that condition, so no amount of increasing in time allocated will correct the problem.

    If possible it would be best to avoid the situations where this comes up. Particularly when trying to modify a large amount of data. Because of the scope of the data, you're bound to have issues with locking. We checked to make sure that this site is using InnoDB for its tables (which offers better locking implementation than MyISAM), but it doesn't appear to be enough.

    Please let us know if you have any other questions or concerns!

    Thank you,
     
  18. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    and it looks like my tables already using InnoDB

    [​IMG]

    although how do i get bump thread to use innoDB

    i also have some tables saying Memory..

    so if i change the default engine in my.cnf will site still be ok?

    but it looks like my WP is using MyISAM:

    [​IMG]

    any tips on converting these..
     
  19. Martok

    Martok Well-Known Member

    Don't change the engine for the non-InnoDB tables. They are MyISam or Memory for a reason. Changing them is likely to break your site.
     
  20. XxUnkn0wnxX

    XxUnkn0wnxX Active Member

    k then..

    well i changed the setting my the configurtion file to be InnoDB as default...

    but even with that i stll get the 500 internal errors even some times when i try to load the home page of the forums...
     

Share This Page