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

[MDawg]

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.

[Wayne Luke]

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.

[MDawg]

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.

[Wayne Luke]

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.

[MDawg]

Yes, got a few more just today same ilk "MySQL gone away errors"

wait_timeout

3600

[Ordovicium]

I am facing this too. It started a week ago.

[Mark.B]

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?

[MDawg]

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.

[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 :

[MDawg]

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 :

[Glenn Vergara]

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

[Wayne Luke]

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.

[MDawg]

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:
http://spam.hgfix.net/5865718.txt

Here is the size of each
http://spam.hgfix.net/9727927.txt
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

[Wayne Luke]

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?

[MDawg]

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.