Wednesday, June 27, 2007

A knock on MySQL

In the course of the translation, it made sense to set up a replica of the mySQL db on the same host as the MSSQL db. Ok, all well in good, except that there seems to be an issue in MySQL which isn't at all well documented whereby the slave chokes on large update statements - sighting an Error 5 Out of Memory.

Luckily - there is a quick fix - the replication thread tells you the SQL statement that beat it - just copy this in to a client and execute it - the slave on its next rety attempt will then attempt a much smaller (or cached) update and peacefully carry on to the next statement.

Watch Out - Order of Inner Joins

Just a quick one here - when porting SQL statements from mySQL to MSSQL - you need to mind the order of your joins. Whereas mySQL likely re-orders the joins so that columns used in one join is available to the next, MSSQL quickly barks at you unless your join your tables in the order so that you never reference a table not already previously mentioned in the SQL statement.

Pretty linear limitation for a product which is completely based on set theory relation rather than linearity.