Mysql ORDER BY RAND() not random.....

[velcroelvis]

Hi,

Weird issue. I recently upgraded server hardware in the server running an online game I am working on.
It is an Athlon Thunderbird 1200 Mhz, 256 MB DDR Ram, and ULTRA SCSI-3 WIDE LVD harddrive.
The server has been rebuilt with latest releas Gentoo linux, and packages for MySQL, OpenSSL, and PHP 4.2.2.

Phpinfo here

The problem is this.:rolleyes:

I do a random select for monster encounters like:

SELECT * FROM monster WHERE areacode=9 ORDER BY RAND() LIMIT 1

I run the query through PHP mysql_connect and return the selected record.
The problem is PHP returns only some of the records in the table - certain records are never selected.

Running the SQL query from the Mysql CLI, gives an even spray of matches with all records.

Same thing if I do a loop or return all records in PHP (no LIMIT in sql). Though even if I return all records - certain records never gets selected first.

The thing is I don't need more than one record, so I want to keep the LIMIT 1 in the SQL - to limit memory usage and get cleaner looking code.

My PHP code worked fine until before the upgrade, I think. It definitely works fine in my development environment (Which is IIS PHP and MySQL on Windows 2000).

So basically I don't think there is anything wrong with the code itself.

I made a small test script to illustrate the problem here.

This is what the script looks like:

<?php
include("includes/db_sql.inc");

$i = 400; 
$tmp = Array(); 
while($i>0) 
	{ 
	$i--; 

	$cn = new DB_SQL; 

	$sql = "SELECT * FROM monster WHERE areacode=9 ORDER BY RAND() LIMIT 1"; 
	$cn->query($sql); 

	while($cn->next_record()) 
    		{ 
    		if (!isset($tmp[$cn->record["name"]])) 
			{ 
			$tmp[$cn->record["name"]]["no"]=1; 
			$tmp[$cn->record["name"]]["name"]=$cn->record["name"]; 
	      		} 
		$tmp[$cn->record["name"]]["no"]++; 
    		} 
	} 
foreach ($tmp as $foo) 
	{    
	echo $foo["name"].":".$foo["no"]."<br>"; 
	} 

?>

If you run it repeatedly you will see that Wurm for example never gets selected first. Though the random spread from the loop looks okay.

Any ideas what might be causing this? I'm thinking some kind of timing issue with PHP opening the connection and mysql seeding its random generator???

Please guys, any help would be super!!!

ps. I know I could do this by selecting the whole table and picking a random row from PHP, but that would bloat my code, and memory consumption - and this is one hungry app, so I am trying to minimise load. ds.

Regards
Hans Petersson

[velcroelvis]

Come on people - there's got to be someone out there who has an idea? Please?

e

[tomhath]

Obviously it shouldn't make any difference, but you might try seeding the RAND() function with the current timestamp to see if that gives a more random order...

[[deleted]]

I remember there is a trick with the random functions; different glibc versions have different intepretations of how the random functions should work.

Perhaps you have struck a case where running a query from the CLI uses a different (older or newer) version of the lib than the PHP module does?

[velcroelvis]

vincente: that is actually the closest to a possible explanation I've got - THANKS. I'll check that out, we actually moved to the latest glibc with the latest build because of a CPU/MBD upgrade.

For anyone who might have the same problem, I have found a workaround that at least solves my problem.

Seed the MYSQL RAND() function with a random value from PHP's mt_rand like this.

mt_srand ((double) microtime() * 1000000);
$seed = mt_rand(1,2000000000);

$sql = "SELECT * FROM monster WHERE areacode=9 ORDER BY RAND($seed) LIMIT 1";

And of course, then run the query and do whatever stuff it is you have to do.

This is not a great solution, because someone with knowledge of generation of random numbers on computers will probably tell you that seeding a random generator with a random number from another random generator is a bad idea.

Well in my case it solved the problem. And I'm happy, though I will be poking around in my glibc config files and go crazy sometime soon. 😃

Thanks again guys.

e

[[deleted]]

"This is not a great solution,"

In fact, it's completely weird!

I would have expected that the random number generator used by PHP is completely seperated from the one used by MySQL, so seeding in PHP should not affect the generator in MySQL.

After all, in the query you ask MySQL to order by rand(), PHP should just forward the query to mysql and fetch results.

Methinks this qualifies very much as a serious bug in PHP/MySQL/glibc