Incorrect arguments to RAND, on a new server

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dotsc
Forum Newbie
Posts: 14
Joined: Sun Jan 29, 2006 2:55 pm

Incorrect arguments to RAND, on a new server

Post by dotsc »

Hi,

This is more of a mysql query question, I've been using the same scripts for over 2 years, never had any problems with them but now I have moved to a new server and I am getting this error when I try running the script:
select count(*) from list WHERE list_locationid='15' AND list_member!='' ORDER BY RAND(list.list_photo),RAND(NOW())

Incorrect arguments to RAND

Here is the query:

Code: Select all

$paging->Query("SELECT (TO_DAYS(CURDATE())-TO_DAYS(list_adddate)) AS daysFromAdd, (TO_DAYS(CURDATE())-TO_DAYS(list_update)) AS daysFromUpdate,list.* FROM list WHERE list_locationid='$location_id' AND $filter_section $city_filter ORDER BY RAND(list.list_photo),RAND(NOW())");
How can I get rid of this error, I can configure and install new scripts on my site but I still have a limited knowledge of php/mysql
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

my guess would be different versions of MySQL.

have you tried running that query from the CLI?
dotsc
Forum Newbie
Posts: 14
Joined: Sun Jan 29, 2006 2:55 pm

Post by dotsc »

old server my sql version: mysql (4.0.27-standard)

new server mysql version: mysql (4.1.19-standard)

All my other scripts are running fine except for this 1 script. Could the mysql version possibly cause this error in just one of the scripts? Any one has any suggestions on how to correct my query?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

there are HUGE differences between those sql versions and a lot of them are changes to syntax. I'd be willing to bet that your query just doesn't work on 4.1.

try running the query from the CLI. If it doesn't work, you'll need to just rework the query to get it to work with 4.1.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Take a look at the MySQL manual's Mathematical Functions. Do a page search for RAND.

To answer your other question, yes, there is a possibility that MySQL 4.1 could be considerably different from version 4.0 and below in many respects. You may also want to have a look at the change log to see what differences there are.

Another thing to note, also, is the server set up. Are there differences in server configurations that could have potentially been quieting errors though you didn't know it? Something to consider...
dotsc
Forum Newbie
Posts: 14
Joined: Sun Jan 29, 2006 2:55 pm

Post by dotsc »

thanks burrito and everah :-)

Did a little reseach and I came up with this code:

Code: Select all

$paging->Query("SELECT (TO_DAYS(CURDATE())-TO_DAYS(list_adddate)) AS daysFromAdd, (TO_DAYS(CURDATE())-TO_DAYS(list_update)) AS daysFromUpdate,list.* FROM list WHERE list_locationid='$location_id' AND $filter_section $city_filter ORDER BY RAND()");
And it works great!! I've removed the list.list_photo between the RAND ( ) and now the script runs nicely. E-Z fix, just needed some guideance.

I hope this helps someone to resolve a similar problem

Thanks!
Post Reply