Announcement

Collapse
No announcement yet.

Lot of database errors since upgrade to 5.2.3 MySQL server has gone away

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lot of database errors since upgrade to 5.2.3 MySQL server has gone away

    Immediately after the upgrade to 5.2.3 I started getting a dozen of these errors per day. They stopped for a bit, started up again now.

    I'd like to think it's just because my shared server is overloaded from booming traffic, but seems like too much of a coincidence that the database errors, which used to come in infrequently, started happening a dozen or so a day since the upgrade.


    EXAMPLE:

    Database error in vBulletin 5.2.3:




    Invalid SQL:

    UPDATE session SET `lastactivity`=1470790190,`styleid`=11,`languageid`=1

    WHERE (`sessionhash` = '61024334009e4ff92aec37673a5f9b37')

    /**session**/;




    MySQL Error : MySQL server has gone away

    Error Number : 2006

    Request Date : Tuesday, August 9th 2016 @ 07:49:50 PM

    Error Date : Tuesday, August 9th 2016 @ 07:52:21 PM

    Script : http:///forums/forum/general-topics..............

    Referrer :

    IP Address : 192.185.XXX.XX

    Username : Guest

    Classname : vB_Database_MySQLi

    MySQL Version :

  • #2
    Oops posted this in wrong place. I am not vbcloud. Please move to
    http://www.vbulletin.com/forum/forum...roubleshooting

    Comment


    • #3
      i believe you may need to increase your wait_timeout(try 60-120 seconds)...

      someone probly knows better than me, but if you want to test a quick config tweak i believe that is the one

      Comment


      • #4
        MySQL server has gone away

        This is a server error, and you should contact your host.

        Ask your host to increase the max_packet_size to 16MB and the wait_timeout to at least 30 seconds.
        MARK.B | vBULLETIN SUPPORT

        TalkNewsUK - My vBulletin 5.5.6 Demo
        AdminAmmo - My Cloud Demo

        Comment


        • #5
          I'll contact hostgator. I did read this:

          http://support.hostgator.com/article...not-be-changed

          PHP has been configured to use the following settings on our shared and reseller servers:


          safe mode = Off (cannot adjust)
          memory_limit = 256M (MAXIMUM)
          max_execution_time = 30 (MAXIMUM in seconds)
          max_input_time = 60 (MAXIMUM in seconds)
          post_max_size = 64M (MAXIMUM)
          upload_max_filesize = 64M (MAXIMUM)
          enable_dl = Off (cannot adjust)

          The above settings either CANNOT be changed or exceeded on Shared and Reseller accounts. These limits are set to prevent extremely high resource usage in shared environments. We have found that a majority of the PHP scripts will work within these limits, and the scripts that require more resources are best suited for a VPS or dedicated server.

          ---

          Will advise.

          ALSO, hostgator wrote as follows:

          have reviewed the available service, access, and performance logs for the server from around the time of the reported error. I can confirm that the server was performing well and that the MySQL service was online and responding to requests.

          The logs do indicate that your account was hitting the 25 concurrent process limit around the same time as the reported error.

          [Tue Aug 09 22:53:04 2016] uid 33048 (mdawg) processes: `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) /opt/php56/bin/php-cgi /home2/mdawg/public_html/forums/index.php `---> (mdawg) dovecot/imap [mleus+truepassage.com 68.7.XXX.XX] `---> (mdawg) dovecot/imap [XXX+XXX.com 68.7.XXX.XX] `---> (mdawg) dovecot/imap [XXX+XXX.com 68.7.XXX.XX] `---> (mdawg) dovecot/imap [YYY+YYY.com 68.7.XXX.XX]

          I have checked the access logs for your site from around this time, and the full list of requests made during the 10 minute period of 10:50PM can be seen here: [redacted]

          The portion of your error shown below indicates that the process was running more than 3 minutes.

          MySQL Error : MySQL server has gone away
          Error Number : 2006
          Request Date : Tuesday, August 9th 2016 @ 10:51:17 PM
          Error Date : Tuesday, August 9th 2016 @ 10:54:23 PM

          When I test the specific query that was shown in the error, it easily completes in well less than a second. Since additional processes cannot spawn after the 25 concurrent limit is reached, and with the rate of requests we see in the access logs from around that time, I would suggest working to prevent the processes from stacking up and causing the process limit to be reached by lowering the rate of requests the site is making back to itself, as we see most of them were made from the domains IP itself.
          Last edited by MDawg; Wed 10th Aug '16, 3:00pm.

          Comment


          • #6
            Originally posted by Mark.B View Post
            MySQL server has gone away

            This is a server error, and you should contact your host.

            Ask your host to increase the max_packet_size to 16MB and the wait_timeout to at least 30 seconds.
            Hostgator responded as such:

            Those are set to:




            [[email protected] /home2/mdawg/public_html]# mysql information_schema -e "SELECT VARIABLE_NAME,VARIABLE_VALUE FROM GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'MAX_ALLOWED_PACKET' OR VARIABLE_NAME = 'WAIT_TIMEOUT'"

            +--------------------+----------------+

            | VARIABLE_NAME | VARIABLE_VALUE |

            +--------------------+----------------+

            | WAIT_TIMEOUT | 3600 |

            | MAX_ALLOWED_PACKET | 16777216 |

            +--------------------+----------------+



            How are those? Seem okay.

            Comment


            • #7
              The issue has nothing to do with the PHP settings you listed.

              Basically, this means that MySQL is closing the connection before the query is finished. Since nothing was changed with this table, something new is happening either in your version of PHP or how MySQL operates. Session handling code hasn't really changed in a long time.

              How big is your session table?
              How big is your cache table?
              How big is your cacheevent table?

              Make that persistent connections is turned off in your /core/includes/config.php file.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API - Full / Mobile
              Vote for your favorite feature requests and the bugs you want to see fixed.

              Comment


              • #8
                persistent connections ARE turned off in my /core/includes/config.php file
                (set to 0, off)


                The table sizes are as follows:




                | Tables | Columns | Total Rows |

                | cache | 6 | 14156 |

                | cacheevent | 2 | 34530 |

                | session | 22 | 36 |

                --

                Also got a slightly different database error today:

                Database error in vBulletin 5.2.3:






                [Showing truncated query, original length: 276382]

                [First 500 chars]

                Invalid SQL:




                SELECT node.nodeid

                FROM node as node

                JOIN tagnode AS tagnode32 ON

                (node.nodeid = tagnode32.nodeid)

                JOIN tagnode AS tagnode33 ON

                (node.nodeid = tagnode33.nodeid)

                JOIN tagnode AS tagnode34 ON

                (node.nodeid = tagnode34.nodeid)

                JOIN tagnode AS tagnode35 ON

                (node.nodeid = tagnode35.nodeid)

                JOIN tagnode AS tagnode36 ON

                (node.nodeid = tagnode36.nodeid)

                JOIN tagnode AS tagnode37 ON

                (node.nodeid = tagnode37.nodeid)

                JOIN ta

                [Last 500 chars]

                5,27,29,31,32,35,40,43,45,54,58,63,69,71,77,79,81,87,91,96,100,101,3,8,14,28,30,33,36,44,4 6,56,59,64,66,67,72,78,80,82,89,92,94,97,98,26,37,41,47,53,55,57,60,65,85,93,99,6,20,34,39 ,42,48,61,68,70,73,84,86,21,49,62,74,75,76,83,90,12,17,38,50,88,95,66383,22,51,23,52,19,18 ,25,24,16) ) AND node.showapproved > 0 AND node.viewperms > 1 AND node.showpublished > 0 ))

                OR starter.parentid = 9 AND node.userid IN (0)))







                ORDER BY node.created DESC,node.nodeid ASC

                LIMIT 500



                /**getSearchResults**/;




                MySQL Error : Too many tables; MySQL can only use 61 tables in a join

                Error Number : 1116

                Request Date : Thursday, August 11th 2016 @ 12:56:57 PM

                Error Date : Thursday, August 11th 2016 @ 12:56:59 PM

                Script : http:///forums/search?searchJSON={"tag":["000"]}

                Referrer :

                IP Address : 208.115.XXX.XX

                Username : Guest

                Classname : vB_Database_MySQLi

                MySQL Version :

                ---

                Due to this issue
                http://www.vbulletin.com/forum/forum...ll-of-a-sudden
                I have had to force PhP version 5.6.24 onto my server, since vb 5.2.3 will not run with PhP 7.0.9 , however these database errors started immediately after upgrading to 5.2.3 and before I went down to PhP 5.6.24

                Comment


                • #9
                  Was looking more for the total table size in bytes. Unless we're doing a table scan, the number of rows isn't that important. Even though your tables are very small, please truncate the cache and cacheevents tables.

                  How many tags do you allow on posts? I think that query could probably be optimized though. Will add a JIRA.

                  Are all the "MySQL Gone Away" errors the same query? What version of MySQL are you using?
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API - Full / Mobile
                  Vote for your favorite feature requests and the bugs you want to see fixed.

                  Comment


                  • #10
                    Are your tables using the INNODB engine?
                    Translations provided by Google.

                    Wayne Luke
                    The Rabid Badger - a vBulletin Cloud demonstration site.
                    vBulletin 5 API - Full / Mobile
                    Vote for your favorite feature requests and the bugs you want to see fixed.

                    Comment


                    • #11
                      Can you run this query and send me the results as CSV attachment on a Private Message?

                      show table status;
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API - Full / Mobile
                      Vote for your favorite feature requests and the bugs you want to see fixed.

                      Comment


                      • #12
                        Yes the my sql going away errors all seem to be the same or similar query.

                        I allow 20 tags per post.

                        mySQLi 5.5.48-37.8

                        I don't know what INNODB is?

                        Where / how do I run the query?

                        Comment


                        • #13
                          phpMyAdmin or whatever tool your hosting provider gives to run a query on the server.
                          Translations provided by Google.

                          Wayne Luke
                          The Rabid Badger - a vBulletin Cloud demonstration site.
                          vBulletin 5 API - Full / Mobile
                          Vote for your favorite feature requests and the bugs you want to see fixed.

                          Comment


                          • #14
                            You can also run query from AdminCP > Maintenance > Execute SQL Query. You just have to add your admin userid to "canrunqueries" permissions in core config file.

                            GIPHY for vB5 AutoLinker Auto-Create Flag Report Topic Social Icons in Postbit Clear Cache Cron DragDrop Upload Topic AJAX AutoUpdate Custom Avatars Selector Stop Links in Posts...and more!

                            Comment


                            • #15
                              Originally posted by Wayne Luke View Post
                              Are your tables using the INNODB engine?
                              I can confirm that some tables in this database are using the InnoDB storage engine, but not all of them. Of the 255 tables, 74 of them are using InnoDB and 181 are using MyISAM.


                              Originally posted by Wayne Luke View Post
                              Even though your tables are very small, please truncate the cache and cacheevents tables.
                              I wrote you a PM about this, and it might be a coincidence but the database errors continue galore and ever since this truncation was done private messages and the inbox are loading very very slowly.

                              This is disturbing database errors coming in at about 15 per minute. Every since truncation I mean.
                              Last edited by MDawg; Tue 16th Aug '16, 8:59pm.

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X