[MySQL] Is this caused by a version incompatibility?

Adnams Drinker · 11 Dec 2006 at 16:26

I've just done a site for a client that pulls a news story at random out of the database and displays it on the home page. I've used the following query:

Code:

SELECT ehfeaturednews.newsid, ehfeaturednews.picture, ehfeaturednews.imagealt, ehfeaturednews.textlong AS excerpt, ehfeaturednews.link
FROM ehfeaturednews
ORDER BY RAND() LIMIT 1

Admittedly, there are currently only three stories in the database, but the random effect works fine on my development setup using MySQL 4.1.20 but fails when the page is uploaded to the client's server - it only ever shows the first story. Their host provides MySQL 3.32.54, so my first guess is that this older version didn't support the RAND() command.

Does anybody know if this is the case and if so, how it could be worked-around?

Cuchulain · 11 Dec 2006 at 16:31

Adnams Drinker said:
I've just done a site for a client that pulls a news story at random out of the database and displays it on the home page. I've used the following query:

Code:

SELECT ehfeaturednews.newsid, ehfeaturednews.picture, ehfeaturednews.imagealt, ehfeaturednews.textlong AS excerpt, ehfeaturednews.link FROM ehfeaturednews ORDER BY RAND() LIMIT 1

Admittedly, there are currently only three stories in the database, but the random effect works fine on my development setup using MySQL 4.1.20 but fails when the page is uploaded to the client's server - it only ever shows the first story. Their host provides MySQL 3.32.54, so my first guess is that this older version didn't support the RAND() command.

Does anybody know if this is the case and if so, how it could be worked-around?

There's a bug specific to RAND on older windows install of MySQL, don't know if that helps, I saw somewhere that running the query twice is one way of getting it to work.

Adnams Drinker · 11 Dec 2006 at 16:46

Cuchulain said:
There's a bug specific to RAND on older windows install of MySQL, don't know if that helps, I saw somewhere that running the query twice is one way of getting it to work.

Their hosting is on Unix - I've seen documentation provided by their hosting company, Nildram. I wonder if the same bug applies to Unix installations? Seems the most likely explanation ...

Adnams Drinker · 12 Dec 2006 at 15:09

A quick update just in case it might help someone else at a later date:

I've just Googled for RAND() bugs in MySQL and amongst others found the workaround Cuchulain referred to. The bug is obviously not specific to Windows installs and running the query that uses the RAND() command twice does indeed fix the problem.

Not very kind to the server for tables with hundreds of rows perhaps, but fine for the one I'm using which currently only has three