Re: Stored procedure/function appears non-deterministic (and I'm going crazy)

[Martin Aspeli]

Hi Jay!

> 1) Your return type and the retn variable are different data types

Yes, strange... we wrote it with BOOLEAN, but when mysqldump dumped it, it made it into TINYINT(1). The manual says BOOLEAN is an alias for TINYINT(1), though, so that's not very surprising.

> 2) Have you tried changing the retn value to 1 and 0 instead of True and False to see if there is any effect?

We did, yes. We also tried other things, like returning strins "OK", "Not OK" (and changin the return type to VARHCAR, of course), and returning other numbers (5 = True, 15 = False, or some arbitrary INT, again changing the return type). Same result. The IF() was still the problem (or at least the thing that manifested the problem)

> 3) Small thing, but in the procedure, the SELECT CURDATE() INTO testDate; line is redundant, as the next line overwrites the value of testDate anyway.

Yeah, I noticed afterwards and removed it. :)

We appreciate the suggestions for breaking the procedure up. It's one of those cases where it started simple and grew as more things were taken into consideration. In fact, it grew even a bit more since posting this. Sigh...

However, I'm still not clear - were you able to reproduce the problem with the test cases and the dump? We've managed to reproduce on our dev "server" (really just an old laptop) and on my own laptop, with both InnoDB and MyISAM tables and with different combinations of settings during the Setup Wizard. Getting rid of the IF() also consistently fixes the problem.

We'd be very grateful if you or someone with more knowledge of MySQL could try this and (a) tell us we're not crazy and (b) if indeed there is a bug or a gotcha (you shouldn't do this, because ...) , flag it up in the appropriate way so that others don't waste the same 2.5 days we just did :-)

Once again, thanks a lot for your input!

Martin