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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Maltair
    Senior Member
    • Feb 2009
    • 575
    • 5.7.5

    #16
    I am dying here from these errors daily, which seem to be even worse since truncation was done at your suggestion.

    Hostgator has offered to convert all tables to InnoDB.

    Comment

    • Wayne Luke
      vBulletin Technical Support Lead
      • Aug 2000
      • 73976

      #17
      That is the next step. There aren't a lot of configuration options in a shared hosting environment. You can also ask them to move you to a less populated MySQL Server.
      Translations provided by Google.

      Wayne Luke
      The Rabid Badger - a vBulletin Cloud demonstration site.
      vBulletin 5 API

      Comment

      • Maltair
        Senior Member
        • Feb 2009
        • 575
        • 5.7.5

        #18
        After conversion of all but two tables (those two may not be converted yet and are properly set to myisam) to innodb, database errors are still coming in occasionally but not as bad. Am hoping that when 5.2.4 comes out and I am able to go back to PhP 7.0.9 that things will improve, including speed of posting and private messages.

        Comment

        • Wayne Luke
          vBulletin Technical Support Lead
          • Aug 2000
          • 73976

          #19
          Still the MySQL Gone Away errors? Do you know what your wait_timeout value is set to? You can view it in the AdminCP under Maintenance -> Diagnostics -> Server Variables. Look at the MySQL variables.
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment

          • Maltair
            Senior Member
            • Feb 2009
            • 575
            • 5.7.5

            #20
            Yes, got a few more just today same ilk "MySQL gone away errors"
            wait_timeout 3600

            Comment

            • Ordovicium
              Senior Member
              • Nov 2001
              • 292
              • 4.2.x

              #21
              I am facing this too. It started a week ago.
              Last edited by Ordovicium; Wed 21 Sep '16, 11:55am.
              Sorry for my English
              Let'z talk German

              Comment

              • Mark.B
                vBulletin Support
                • Feb 2004
                • 24286
                • 6.0.X

                #22
                Originally posted by Ordovicium
                I am facing this too. It started a week ago.
                Same as for above, this is actually a server error and we're quite limited in what to suggest.

                What is your wait_timeout value set to?
                MARK.B
                vBulletin Support
                ------------
                My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
                My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

                Comment

                • Maltair
                  Senior Member
                  • Feb 2009
                  • 575
                  • 5.7.5

                  #23
                  I just upgraded to vb 5.2.4 and reverted to PhP 7.0.9 (versus 5.6.24) - let's hope this solves the database errors (My SQL server has gone away) because they have been coming in at up to 100 per day. I have occasionally received such errors in the past, but was getting flooded by them as soon as I upgraded to 5.2.3 so it must have something to do with 5.2.3 and not just the shared server.

                  Comment

                  • Maltair
                    Senior Member
                    • Feb 2009
                    • 575
                    • 5.7.5

                    #24
                    No such luck, even after update still database errors, two just came in like this:

                    Database error in vBulletin 5.2.4:

                    Invalid SQL:
                    INSERT IGNORE INTO session (`sessionhash`,`userid`,`host`,`idhash`,`lastactivity`,`location`,`styleid`,`languageid`,` loggedin`,`inforum`,`inthread`,`incalendar`,`badlocation`,`useragent`,`bypass`,`profileupd ate`,`apiclientid`,`apiaccesstoken`,`emailstamp`)

                    VALUES('28a378b80a08f9e87e81a3460052ee88',0,'96.246.179.64','565aa4beee0d7c12ed3254ca29f8d 7fe',1474566592,'',11,1,0,0,0,0,0,'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:49.0) Gecko/20100101 Firefox/49.0',0,0,0,'',0) /**session**/;

                    MySQL Error : MySQL server has gone away
                    Error Number : 2006
                    Request Date : Thursday, September 22nd 2016 @ 12:49:52 PM
                    Error Date : Thursday, September 22nd 2016 @ 12:51:08 PM
                    Script : http:///forums/forum/XXXXXX
                    Referrer : https://www.google.com/
                    IP Address : 96.246.XXX.XX
                    Username : Guest
                    Classname : vB_Database_MySQLi
                    MySQL Version :

                    Comment

                    • Maltair
                      Senior Member
                      • Feb 2009
                      • 575
                      • 5.7.5

                      #25
                      Also just got three of these in rapid succession:


                      Database error in vBulletin 5.2.4:


                      [Showing truncated query, original length: 15766]
                      [First 500 chars]
                      Invalid SQL:
                      /** saveDbCache */ DELETE FROM cacheevent WHERE (cacheid = 'node_64199_lvl1data' AND event = 'nodeChg_64199') OR
                      (cacheid = 'node_64200_lvl1data' AND event = 'nodeChg_64200') OR
                      (cacheid = 'node_64201_lvl1data' AND event = 'nodeChg_64201') OR
                      (cacheid = 'node_64202_lvl1data' AND event = 'nodeChg_64202') OR
                      (cacheid = 'node_64203_lvl1data' AND event = 'nodeChg_64203') OR
                      (cacheid = 'node_64204_lvl1data' AND event = 'nodeChg_64204') OR
                      (cacheid = 'node_64205_lvl1data' AND event
                      [Last 500 chars]
                      vl1data' AND event = 'nodeChg_69972') OR
                      (cacheid = 'node_70021_lvl1data' AND event = 'nodeChg_70021') OR
                      (cacheid = 'node_70609_lvl1data' AND event = 'nodeChg_70609') OR
                      (cacheid = 'node_70910_lvl1data' AND event = 'nodeChg_70910') OR
                      (cacheid = 'node_70921_lvl1data' AND event = 'nodeChg_70921') OR
                      (cacheid = 'node_70986_lvl1data' AND event = 'nodeChg_70986') OR
                      (cacheid = 'node_71545_lvl1data' AND event = 'nodeChg_71545') OR
                      (cacheid = 'node_71838_lvl1data' AND event = 'nodeChg_71838');

                      MySQL Error : Deadlock found when trying to get lock; try restarting transaction
                      Error Number : 1213
                      Request Date : Thursday, September 22nd 2016 @ 02:17:26 PM
                      Error Date : Thursday, September 22nd 2016 @ 02:17:30 PM
                      Script : http:///forums/forum/XXXX
                      Referrer :
                      IP Address : 192.185.XXX.XX
                      Username : Guest
                      Classname : vB_Database_MySQLi
                      MySQL Version :

                      Comment

                      • glennrocksvb
                        Former vBulletin Developer
                        • Mar 2011
                        • 4011
                        • 5.7.X

                        #26
                        I think it's time to go to VPS from shared hosting? Or move to a better hosting.

                        Flag Icon Postbit Insert GIPHY Impersonate User BETTER INITIALS AVATAR Better Name Card Quote Selected Text Bookmark Posts Post Footer Translate Stop Links in Posts +MORE!

                        Comment

                        • Wayne Luke
                          vBulletin Technical Support Lead
                          • Aug 2000
                          • 73976

                          #27
                          Originally posted by MDawg
                          No such luck, even after update still database errors, two just came in like this:

                          Database error in vBulletin 5.2.4:

                          Invalid SQL:
                          INSERT IGNORE INTO session (`sessionhash`,`userid`,`host`,`idhash`,`lastactivity`,`location`,`styleid`,`languageid`,` loggedin`,`inforum`,`inthread`,`incalendar`,`badlocation`,`useragent`,`bypass`,`profileupd ate`,`apiclientid`,`apiaccesstoken`,`emailstamp`)

                          VALUES('28a378b80a08f9e87e81a3460052ee88',0,'96.246.179.64','565aa4beee0d7c12ed3254ca29f8d 7fe',1474566592,'',11,1,0,0,0,0,0,'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:49.0) Gecko/20100101 Firefox/49.0',0,0,0,'',0) /**session**/;

                          MySQL Error : MySQL server has gone away
                          Error Number : 2006
                          Request Date : Thursday, September 22nd 2016 @ 12:49:52 PM
                          Error Date : Thursday, September 22nd 2016 @ 12:51:08 PM
                          Script : http:///forums/forum/XXXXXX
                          Referrer : https://www.google.com/
                          IP Address : 96.246.XXX.XX
                          Username : Guest
                          Classname : vB_Database_MySQLi
                          MySQL Version :
                          This should be a simple query taking only milliseconds. Especially if the table is INNODB as it should be in vBulletin 5. That uses row level locking so there really shouldn't be much a wait to execute this. Do you have access to the MySQL Logs? My guess is no, but it wouldn't hurt to ask to see if we can get more information.

                          For the CACHE errors, you may have to periodically empty this. You can do it in the AdminCP under Maintenance.
                          Translations provided by Google.

                          Wayne Luke
                          The Rabid Badger - a vBulletin Cloud demonstration site.
                          vBulletin 5 API

                          Comment

                          • Maltair
                            Senior Member
                            • Feb 2009
                            • 575
                            • 5.7.5

                            #28
                            Just cleared cache.


                            Hostgator told me that there is no memcached on or available on my server, and:

                            Here is the row count of the database:


                            Here is the size of each

                            This is in MB


                            I just asked about the mySQL logs.


                            If you recall we truncated the cache and cacheevents tables and converted all but two tables to INNODB. The two that were not converted and remained ISAM including their error codes were:

                            # mysql -sse "show table status where Engine='MyISAM';" mdawg_XXX
                            language MyISAM 10 Dynamic 1 657620 658220 281474976710655 2048 600 2 2015-04-02 00:50:08 2016-08-03 11:25:00 2016-08-23 12:14:19 utf8_unicode_ci NULL phrase MyISAM 10 Dynamic 13739 171 3656292 281474976710655 3417088 1293604 290774 2016-08-03 11:24:33 2016-08-21 23:01:17 2016-08-23 12:14:21 utf8_unicode_ci NULL

                            Table: language
                            Error: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

                            Table: phrase Error: The used table type doesn't support FULLTEXT indexes

                            Comment

                            • Wayne Luke
                              vBulletin Technical Support Lead
                              • Aug 2000
                              • 73976

                              #29
                              The cache and cacheevent tables are always changing. So they may need to be emptied periodically. Neither are overly large.

                              If you disable your addons do the issues stop? Does hostgator limit resources by number of queries that can be ran?
                              Translations provided by Google.

                              Wayne Luke
                              The Rabid Badger - a vBulletin Cloud demonstration site.
                              vBulletin 5 API

                              Comment

                              • Maltair
                                Senior Member
                                • Feb 2009
                                • 575
                                • 5.7.5

                                #30
                                I don't have too many addons, but as I mentioned these database errors increased in volume geometrically since 5.2.3 and I just received about fifty of them today since upgrading to 5.2.4 I had not added anything new since 5.2.3 or 5.2.4

                                As far as mqSQL logs, hostgator responded: "As this is a shared server, you do not have access to the MySQL logs; however, these rarely have errors/warnings related to specific databases and are generally just regarding service restarts."

                                From hostgator: "The number of queries that can be run are not limited unless it is found that your account is causing an issue for the rest of the server." --- I've received no such notice from hostgator and while my forum has good traffic, nothing excessive.
                                Last edited by Maltair; Thu 22 Sep '16, 8:45pm.

                                Comment

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