a slightly silly bug

Fri May 30 16:52:00 UTC 2003

ok, the short version is that there's a bug in the rand() function of
certain versions of mysql. i'm going to search b.m.o to see if this has
been filed before.

the longer (more fun) version of this story is attached.

-jon

-- 
Jonathan Schatz
Engineering System Administrator
VMware, Inc
"Te occidere possunt sed te edere non possunt nefas est."
-------------- next part --------------

Full Text Bug Listing

Bug 27294 - the train was only scary the first few times
Bug#: 27294 Category: Bugzilla Product: Any Platform: IA32
Host OS/Version: Unknown Guest OS/Version: Unknown Status: closed Severity: non-critical Priority: cleanup
Resolution: fixed Assigned To: jon Reported By: mginzton QA Contact: doug
Component: bugzilla Milestone: unknown
URL: 
Summary: the train was only scary the first few times
Opened: 2003-05-27 15:36 PST
Description:

Only the first quip in the quip list (which is, "The light at the end of the 
tunnel is an oncoming train") ever seems to show up, even though there are many 
others in the list (http://bugzilla/quips.cgi?action=show).

------- Additional Comment #1 From Benjamin Chambers 2003-05-27 15:43 PST -------

I've seen others, but I agree the train seems to be bugzilla's favorite. No
coincidence that it's first on the list, probably. Someone's random number
generator is heavily biased toward zero, perhaps?

------- Additional Comment #2 From Jonathan Schatz 2003-05-28 14:54 PST -------

erm, it seems to have resolved itself. i haven't seen the train since yesterday.

------- Additional Comment #3 From Matthew Delco 2003-05-28 14:56 PST -------

I've seen it several times today.

------- Additional Comment #4 From Matt Ginzton 2003-05-28 14:57 PST -------

I still see it most of the time.  After hitting reload about 30 times, I'd say 
about 90%.

------- Additional Comment #5 From Jonathan Schatz 2003-05-28 14:58 PST -------

heh, after i resolved this bug the quote showed back up on my buglist. go figure.

------- Additional Comment #6 From Nolan Leake 2003-05-28 15:11 PST -------

SendSQL("SELECT quip FROM quips WHERE approved = 1 ORDER BY RAND() LIMIT 1"); in
buglist.cgi is the code that that should sort the quips table randomly, and then
return the first quip.  Well, hopefully the query optimizer picks a better plan
than that, but I digress.

Sounds like whatever database we use has a shitty RAND() function.  I'd say that
nothing but MySQL can suck this much, but that's just a guess based on personal
prejudices.

------- Additional Comment #7 From Jonathan Schatz 2003-05-28 15:44 PST -------

the random distribution isn't so bad. i wrote a quick test program to run the
query 1000 times (well, almost the same query, but for queryid instead of the
actual query). here's the distribution:

quipid 1 : 0.022%
quipid 2 : 0.025%
quipid 3 : 0.028%
quipid 4 : 0.021%
quipid 5 : 0.016%
quipid 6 : 0.020%
quipid 7 : 0.017%
quipid 8 : 0.023%
quipid 9 : 0.026%
quipid 10 : 0.018%
quipid 11 : 0.023%
quipid 12 : 0.024%
quipid 13 : 0.017%
quipid 14 : 0.022%
quipid 15 : 0.021%
quipid 16 : 0.022%
quipid 17 : 0.016%
quipid 18 : 0.017%
quipid 19 : 0.020%
quipid 20 : 0.031%
quipid 21 : 0.026%
quipid 22 : 0.028%
quipid 23 : 0.017%
quipid 24 : 0.020%
quipid 25 : 0.024%
quipid 26 : 0.030%
quipid 27 : 0.022%
quipid 28 : 0.026%
quipid 29 : 0.019%
quipid 30 : 0.022%
quipid 31 : 0.026%
quipid 32 : 0.019%
quipid 33 : 0.035%
quipid 34 : 0.027%
quipid 35 : 0.023%
quipid 36 : 0.020%
quipid 37 : 0.011%
quipid 38 : 0.020%
quipid 39 : 0.022%
quipid 40 : 0.024%
quipid 41 : 0.016%
quipid 42 : 0.022%
quipid 43 : 0.022%
quipid 44 : 0.028%
quipid 45 : 0.022%

but i have bigger bugs to look at right now.

------- Additional Comment #8 From Jonathan Schatz 2003-05-28 15:45 PST -------

ok, my division was off by a factor of 100. but you get the point.

------- Additional Comment #9 From Nolan Leake 2003-05-28 16:26 PST -------

The repeated "shift-reload" test on buglist.cgi disagrees violently with your
results: in 50 trials, I got 9 quips that were not the train one.  The 9 were
evenly distributed.

One possible explanation is that the "train" quip is duplicated many times in
the quips table.  This would explain the disparity between your quipid based
results and my quip based results.

Of course, pretty much every other bug is higher priority than this one, but it
is certainly interesting.

------- Additional Comment #10 From Jonathan Schatz 2003-05-28 17:06 PST -------

ok, i re-ran the trial with the exact query, and the results are the same. maybe
a proxy issue? who knows. i'm buying a beer for the person who figures out the
problem.

------- Additional Comment #11 From Nolan Leake 2003-05-28 17:22 PST -------

Using wget, I got "train" 40/50 times going through the proxy, and 43/50 times
going directly to bugzilla.

That would seem to eliminate both the proxy's cache and my browser's cache.

------- Additional Comment #12 From Nolan Leake 2003-05-28 18:09 PST -------

Beer me.

MySQL appears to pick a very close to constant seed (I'm not sick enough to go
spelunking into MySQL's code to find out how they fucked this one up) at the
start of each connection to the database.  Your test app didn't hit this
problem, because unlike bugzilla, it doesn't reconnect to the database every time.

So the easy workaround is (both seed RAND and work around its output clustering
problems):
SendSQL("SELECT quip FROM quips ORDER BY MD5(RAND(NOW())) LIMIT 1");

Alternately, we can upgrade to MySQL 3.23.56, which I believe fixes it.  4.0
does for sure.

Finally, this points to a latent scalability problem in bugzilla.  It should be
using a connection pool of some sort.  Ideally, using a threaded MPM in Apache
2.0, so the connection pool can be shared.

------- Additional Comment #13 From Jonathan Schatz 2003-05-28 18:24 PST -------

what's your drink?

as for the scalability issue, i tried using apache 2.0. queries slowed by ~50%.
this is still possible to do on the db side, but buzilla's not mod_perl safe, so
i can't use the Apache::DBI module to do this. it's a known issue on
bugzilla.mozilla.org (http://bugzilla.mozilla.org/show_bug.cgi?id=87406) . 

------- Additional Comment #14 From Jonathan Schatz 2003-05-28 18:30 PST -------

Change 87063 by jon at jonschatz-lx on 2003/05/28 18:29:43

	quips should be random now

	QA Notes:
	Bug Number:27294
	Reviewed by:nolan
	Testing done:
	Mailto:

Affected files ...

... //depot/bugzilla/bugzilla-release/buglist.cgi#2 edit
... //depot/bugzilla/main/buglist.cgi#7 edit

------- Additional Comment #15 From Jonathan Schatz 2003-05-28 18:32 PST -------

fixed, checked in, and live.

------- Additional Comment #16 From Matt Ginzton 2003-05-28 18:51 PST -------

Awesome.  This is so much cooler now.