While building the new website for http://www.sqlconverter.com, I recently ran into a slight compatibility issue between the SQLite3 database used for local initial development and MySQL when the site was prepared for production. What was the compatibility issue? It was the random function!
The issue popped up in the function used by the main page to return three random testimonials (in random order) for presentation on the homepage. The RoR code for the function is:
1 2 3 4
The highlighted ActiveRecord call more or less translates into this SQL:
The SQL server is expected to return three (3) entries in random order. It works great in SQLite and PostgreSQL. It also works great in MySQL, except MySQL doesn’t have RANDOM() but rather has a RAND() – which does the exact same thing.
However, I did not want to have to change the RoR code, which was already passing its unit tests. Besides doing so would cause the code to not work in the other databases, which define the RANDOM function. The quickest solution turns out to be to define a custom RANDOM function for MySQL!
MySQL statement to define RANDOM() as an alias for its native RAND()
And everything worked. I hope this will help you too.