Multiple database errors (been going on for months)

[jt86]

I upgraded from vB4 to 5 a couple of months ago and have had endless problems ever since and seriously regret upgrading. vB5 has pretty much killed my forums. I've been a vBulletin customer for many years and have never had problems like this. I'm ready to give up on the software if you're unable to or unwilling to help.
My once busy forums (over 30,000 members) now barely get any posts. Members and moderators have reported not being able to post at all, or having to try several times before a post will actually post, and sometime can't even access the forums as they're so slow. We had no problems at all with vB4. My host has increased the memory on my server several times. The database crashes on a regular basis, leaving me being bombarded with thousands of sql error message emails.
The most we've gone without any error messages or problems is about 2 weeks. Very often, every few days I'm having to repair the cache and cacheevent databases to get rid of database error messages. It's got beyond a joke now and I'm at my wits end with it all. I did go through the ticket system originally but got precious little useful help, other that being repeatedly told to repair the database tables, and that it was an issue with my server rather than vBulletin. However after reading through posts on here it appears that database and other errors are very common in vB5.

Here are some of the database error messages that I get--


~~~~~~~~~~~~~~~~~~~~~~~~

Database error in vBulletin 5.1.0:

Invalid SQL:
SELECT * FROM cache
WHERE `cacheid` IN ('preloadTemplates_58');

MySQL Error : Can't find record in 'cache'
Error Number : 1032
Request Date : Thursday, April 24th 2014 @ 08:14:32 PM
Error Date : Thursday, April 24th 2014 @ 08:14:32 PM
Script : ******
Referrer :
IP Address : 36.250.176.40
Username : Guest
Classname : vB_Database_MySQLi
MySQL Version :


~~~~~~~~~~~~~~~~

Invalid SQL:
/** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data)
VALUES
('getSearchResults_27a79249a71bcab174dc8644463fc9d1', 1398384957, 1398384897, 0, '1', 'a:4:{i:10323;a:1:{s:6:\"nodeid\";s:5:\"10323\";}i:127083;a:1:{s:6:\"nodeid\";s:6:\"127083 \";}i:127125;a:1:{s:6:\"nodeid\";s:6:\"127125\";}i:127136;a:1:{s:6:\"nodeid\";s:6:\"127 136 \";}}')

ON DUPLICATE KEY UPDATE expires = VALUES(expires), created = VALUES(created),
locktime = VALUES(locktime), serialized = VALUES(serialized),data = VALUES(data);

MySQL Error : Incorrect key file for table './*******_forums/cache.MYI'; try to repair it
Error Number : 126
Request Date : Thursday, April 24th 2014 @ 08:14:57 PM
Error Date : Thursday, April 24th 2014 @ 08:14:58 PM
Script : **************
Referrer :
IP Address : 198.143.130.90
Username : Guest
Classname : vB_Database_MySQLi
MySQL Version :


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Database error in vBulletin 5.1.0:


[Showing truncated query, original length: 12650]
[First 500 chars]
Invalid SQL:
/** saveDbCache */ INSERT INTO cache (cacheid,expires,created,locktime,serialized,data)
VALUES
('vB_UserPerms42523', 1398478908, 1398392508, 0, '1', 'a:9:{s:12:\"groupintopic\";a:0:{}s:10:\"usergroups\";a:1:{i:0;s:1:\"2\";}s:14:\"moderator perms\";a:0:{}s:7:\"primary\";s:1:\"2\";s:9:\"secondary\";a:0:{}s:10:\"infraction\";a:0:{} s:13:\"channelAccess\";a:7:{s:8:\"selfonly\";a:0:{}s:7:\"canview\";a:47:{i:1;i:1;i:2;i:2;i :3;i:3;i:6;i:6;i:308907;i:308907;i:13;i:13;i:14;i:14;i:16;i:16;
[Last 500 chars]
tertotalcount\";s:2:\"11\";s:15:\"starterurlident\";s:18:\"newbie-i-need-help\";s:14:\"deleteusername\";N;s:14:\"lastauthorname\";N;s:11:\"edit_reason\";N;s:11:\"e dit_userid\";N;s:13:\"edit_username\";N;s:13:\"edit_dateline\";N;s:10:\"hashistory\";N;s:1 8:\"starternodeoptions\";s:3:\"138\";s:18:\"channelnodeoptions\";s:3:\"138\";}')

ON DUPLICATE KEY UPDATE expires = VALUES(expires), created = VALUES(created),
locktime = VALUES(locktime), serialized = VALUES(serialized),data = VALUES(data);

MySQL Error : Incorrect key file for table './******_forums/cache.MYI'; try to repair it
Error Number : 126
Request Date : Thursday, April 24th 2014 @ 10:21:48 PM
Error Date : Thursday, April 24th 2014 @ 10:21:50 PM
Script : **************
Referrer :
IP Address : 69.30.238.18
Username : Guest
Classname : vB_Database_MySQLi
MySQL Version :


~~~~~~~~~~~~~~~~~~~~~

[Lynne]

It looks like your cache table is having issues. First I would go to Maintenance > Clear System Cache. Then I would go to Maintenance > Repair and Optimize and click to Repair/Optimize the cache table. You may need to do that a couple of times before it says it is OK.

[jt86]

Thanks.
I do clear the cache on a regular basis, and also have to repair the cache table frequently due to the large number of database error messages. I've clicked to optimize the table too and it said ok first time. Here's hoping that works!

[Wayne Luke]

Optimize is a fairly worthless operation. What Optimize does is actually delete content after you process a large delete command. Otherwise it will not do anything. It certainly doesn't optimize. With modern database engines like INNODB, it doesn't do anything. This won't fix your issue.

The error you're receiving is caused by files that are corrupted. Probably due to running out of space on the disk partition. You should inform your host of these errors and have them run repair tools from the command line to fix them. Otherwise they will continue to occur and get worse over time. At the worst, you would have to turn off your site, delete the cache tables and manually recreate them after the files were deleted from the server. This will create new files and resolve the problem as well. Nothing in the cache has long-term importance.

[jt86]

Thanks Wayne, that finally sounds like it might be the answer! Pretty annoyed that this wasn't suggested when i had a support ticket open a couple of months back with another vB support staff, then maybe I'd still have some members left posting!
I've told the host what you have said, hopefully they'll be able to do that. If they run the necessary repair tools to fix things, will I also need to delete and recreate the cache tables?

[jt86]

The host has said that log messages indicate that the server has been running out of RAM, which has caused MySQL to be stopped in an abnormal fashion.
They also said I have plenty of free space so that isn't an issue.
They've reduced MySQL's memory requirements by lowering the maximum number of client connections it must handle from 150 to 75.
They said that is MySQL continues to run out of memory to increase RAM on my server, but I've already done that twice, so can't keep on doing it and paying more each time.

Would you recommend deleting the cache tables and manually recreating now? If so, is it easy to recreate them, as it's something I've never done before and I'm not that knowledgable about mySQL?
Thanks

[jt86]

Would it be possible to get a reply to this please?

[jt86]

BTW, I've had a handful of database error emails overnight, so there is obviously still a problem.
Would you advise deleting the cache table now? If so, can you please offer any advice on what I would do to recreate the tables in phpmyadmin, as I can't find any info on Google on how to do so, and don't have a clue what to do.

[marky]

jt86, I was facing problems with deadlocks in my cache tables. Zachery from vBulletin said "Truncate the cache table, and cacheevent. Issue should go away, for a bit anyway."

I tried that on April 23rd and cache issues stopped for me. However, it started again just now (May 1).

Try truncate the cache and cache event table and see if it helps.

[Zachery]

I can't answer why your cache table is crashing constantly, but it shouldn't be.

[jt86]

I'm seriously shocked at the lack of support and conisistency here!
My forums were absolutely fine before I upgraded to vB5. Now I have mySQL error messages and cache tables crashing on a regular basis. Trying to get a definitive answer as to why this is happening and how to fix it seems nigh on impossible.

Could Wayne Luke please answer my questions above?

And Marky, thanks for your advice, I'll look into that now and see if I can work out how to do it.

[Wayne Luke]

Without logs or some sort of definitive information, this is a hosting issue. You haven't even provided links to the problem site. From the information provided, your errors are not caused by problems in the code but rather how the server is managed. All we have to go on is the error messages you posted.

Best thing would to go through the Server Optimization steps in the Server Configuration Forum on this site. Maybe a better configuration would help your memory situation better than the arbitrary limit on the number of connections.