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

[Maltair]

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 :

[Maltair]

Oops posted this in wrong place. I am not vbcloud. Please move to

http://www.vbulletin.com/forum/forum/vbulletin-5-connect/vbulletin-5-connect-questions-problems-troubleshooting

[IggyP]

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

[Mark.B]

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.

[Maltair]

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.

[Maltair]

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:




[root@gatorXXXX /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.

[Wayne Luke]

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.

[Maltair]

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/vbulletin-5-connect/vbulletin-5-connect-questions-problems-troubleshooting/vbulletin-5-support-issues-questions/4348989-can-t-log-in-to-admin-cp-all-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

[Wayne Luke]

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?

[Wayne Luke]

Are your tables using the INNODB engine?

[Wayne Luke]

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

show table status;

[Maltair]

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?

[Wayne Luke]

phpMyAdmin or whatever tool your hosting provider gives to run a query on the server.

[glennrocksvb]

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.

[Maltair]

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.

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.