MySQL Error 1053 / How to change a RAND() function db query

[USAMustangs.com]

EDIT: See post#7 for solution.

Need help on a database query that is causing my host to kill off the process for taking too much CPU time.

In debugging mode, I have a vbgarage template "vbgarage_latestbits" that is causing me all kinds of problems. It takes 4 - 6 second on average to process because it runs a db query that is calling for SELECT * FROM vbgarage_images ORDER BY RAND() LIMIT 5. There are 533 rows, so this is slowing my site down severely and causing an occasional database error when server loads are high. As it's trying to pull of a random set of pictures from 138 different garages. Well, since I can't find "ORDER BY RAND() LIMIT 5" in any of my garage templates or vbgarage.php file. It doesn't come up in my CMPS index.php and the page generates in about 1 second.

How is the best way to rectify this problem. And is there a workaround where I can run execute a SQL query to change that RAND() or something I can do to the table itself in phpmyadmin?

This is the error I get whenever server loads are high.

Invalid SQL:
SELECT * FROM vbgarage_images ORDER BY RAND() LIMIT 5;

MySQL Error : Server shutdown in progress
Error Number : 1053
Date : Tuesday, May 18th 2010 @ 12:03:03 PM
Script : http://www.usamustangs.com/forum/index.php
Referrer : http://www.usamustangs.com/forum/private.php?folderid=0
IP Address : 71.xxx.xxx.xx
Username : Me
Classname : vB_Database

This is becoming an annoying problem for me. Any help would greatly appreciated. I already attempted to contact the coder to no avail. However, this thread made me wonder if there is a work around. Because I have dedicated link to it on my navbar. I have no idea why this is running the RAND() query when I don't have the images displaying on my FORUMHOME or navbar templates.

Thanks.
__________________

[Marco van Herwaarden]

Check both the vbgarage.php and any include files belonging to this modification.

[Marco van Herwaarden]

I see that you are not even sure what Garage modification you are using as you have posted the same question in threads of 2 Garage modifications.

There is no RAND() used in any of the vBGarage files as far as i can tell. I suspect you are also using an Addon modification to show random garage images on your forum home or CMS page. I would first check for that.

PS It still could be a totally different query that brings your server down (or not even a query at all). But as this is probably the first query that is loaded after something causes the server to shutdown, this query gets the error message that it can not be executed because the server is in progress of being shutdown. But that is impossible to tell from only this error message.

[USAMustangs.com]

Originally Posted by Marco van Herwaarden

Check both the vbgarage.php and any include files belonging to this modification.

OK. I just checked. According to my Plugin Manager, this is what I have installed. Vbulletin Garage 3.5.0 Garage System, but I later upgraded that mod with the lightbox feature.

I have attached my exact vbgarage.php file below. And I don't see anything in the includes directory related to vbgarage. And in debugging mode, when you hit explain template usage. All queries (17) are being processed in less than a 1/10 of a second according to the before and after time stamp. However, the vbgarage_latestbits SELECT * FROM vbgarage_images ORDER BY RAND() LIMIT 5; shows 5 - 7 seconds by itself according to the before and after timestamp. And this occurs after the global.php call. Does this info help you at all? As I'm still lost. And when server loads are high, I get the exact MySQL error code I posted above. And this doesn't occur in the CMPS, just the main forum index.

Thanks.

[Marco van Herwaarden]

See my previous post.

[USAMustangs.com]

OK. I see what you're saying. Sorry, just trying to find the root problem here. As this is becoming somewhat of a hassle.

I'll keep digging in my product plugins and template modifications.

And I'm also going to send you a PM. Maybe it's something that might help you help me.

Appreciate your help.

[USAMustangs.com]

To benefit any others that may be experiencing this same problem.

Below is a screen shot of the root cause of the problem.

The solution was to go into the AdminCP Plugin Manager and disable the Latest Uploads Forum Home plugin. It did not affect the functionality of vbgarage in anyway. And it was ultimately useless because it was calling to bring up 5 random pictures under the forumhome_start hook. I never had any pictures pulling up on my forum index. Only when you click on the vbgarage link off the navbar, then it pulls up the last 5 uploads. Now my page generation went from 5 - 15 seconds to less than 1. And no more database errors under high server loads.

Thanks again to Marco van Herwaarden for his assistance with this problem.

[noppid]

I got your pm, but couldn't reply. That rand query is gonna put a load on a server as you see.

That add on eventually became latest uploads on forum home IIRC due to the weakness of rand.

Try something like...

PHP Code:

SELECT * FROM ". TABLE_PREFIX ."vbgarage_images WHERE 1 ORDER BY dateline DESC LIMIT 5 


to get the latest upload to display. Make sure dateline is the name of the date field in the images table for the upload date.

[USAMustangs.com]

Thanks for the info.