MySQL cluster – a wonderful idea.\u00a0 It’s really nice: you have a front end, and multiple redundant back ends, and all the data is loaded into memory, so things go very fast, even queries that inefficiently scan vast squabs of data, all executing in parallel with failsafe redundancy.\u00a0 Not only this, but you get to use words like “cluster” and “database” in one sentence, and it’s all very cool until you hit a limit:<\/p>\n
So, in the shorter term, you migrate archive data away from the <\/em>ndbcluster<\/em> tables and into MyISAM <\/em>tables.\u00a0 (Why not innodb?\u00a0 Because you need to make copies of these things, and tying up your front end to suck out innodb tables is not much fun.)<\/p>\n So today, for the longer term, we’re migrating back to plain InnoDB on some fair to amazing hardware.\u00a0 This means setting up a slave of a live system, and bringing it into sync – 150Gb of data.\u00a0 This has proved interesting:<\/p>\n Anyhow, this brought us to reducing the size of the tables so that we could actually work with them.\u00a0 That has been fun, and this is where we ran into problems with a mass DELETE\u00a0<\/em>statement with some odd error that means “I’m busy with the stuff you said, so I can’t do more right now, sorry.”<\/p>\n So there was this idea:<\/p>\n Why don’t we generate the SQL statements using SQL!<\/p><\/blockquote>\n Now this is truly the daftest idea that was had all day.\u00a0 And fortunately, MySQL seemed to agree:<\/p>\n What a relief! Sanity prevailed! A language which had the foresight not to implement that one thing that makes script languages eval.<\/p>\n Or so we thought, until this gem, which is eval for mysql:<\/p>\n So that’s eval for mysql. It was ugly. Fortunately, nobody did this. Equally fortunate is that it takes two statements to do this, which seriously limits the opportunities for SQL injection from applications.<\/p>\n","protected":false},"excerpt":{"rendered":" MySQL cluster – a wonderful idea.\u00a0 It’s really nice: you have a front end, and multiple redundant back ends, and all the data is loaded into memory, so things go very fast, even queries that inefficiently scan vast squabs of … Continue reading \n
mysql> select eval('select 1');<\/b>\r\nERROR 1305 (42000): FUNCTION eval does not exist<\/pre>\nmysql> set @string='select 1'; prepare eval from @string; execute eval;<\/b>\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.01 sec)\r\nStatement prepared\r\n\r\n+---+\r\n| 1 |\r\n+---+\r\n| 1 |\r\n+---+\r\n1 row in set (0.00 sec)<\/pre>\n