GROUP BY fixed

Friend and former colleague Roland Bouwman has written an excellent update on the GROUP BY implementation in MySQL. MySQL’s implementation of GROUP BY has historically been quirky. Sometimes that quirkiness has been useful, but often it causes grief as SQL authors can make mistakes that are executed but don’t produce the results they want (or […]

Hard Drive Reliability

Cloud service provider Backblaze has updated its earlier study of hard drive failure rates (Nov 2013) in its own infrastructure – from 27,000 to more than 34,000 drives, and the new report (Sep 2014) is quite informative. Hitachi comes out pretty high, Western Digital has produced some good drives, but Seagate tends to come out […]

Tracing down a problem, finding sloppy code

Daniel was tracking down what appeared to be a networking problem…. server reported 113 (No route to host) However, an strace did not reveal the networking stack ever returning that. On the other side, IP packets were actually received. When confronted with mysteries like this, I get suspicious – mainly of (fellow) programmers. I suggested a grep […]

Password rules

The below comes from an Australian government site (formatting is mine, for readability): “Your password must be a minimum length of nine characters, consisting of three of the following – lowercase (a-z) and uppercase (A-Z) alphabetic characters, numeric characters (0-9) or special characters (! $ # %). It cannot contain any 2 consecutive characters that appear […]

innodb_flush_logs_on_trx_commit and Galera Cluster

We deploy Galera Cluster (in MariaDB) for some clients, and innodb_flush_logs_on_trx_commit is one of the settings we’ve been playing with. The options according to the manual: =0 don’t write or flush at commit, write and flush once per second =1 write and flush at trx commit =2 write log, but only flush once per second The flush […]

Mixing databases usually not optimal

Dan McKinley (Etsy) wrote an [IMHO] insightful article Why MongoDB Never Worked at Etsy. First off, it’s important to realise that it’s not a snipe at MongoDB – it’s a fine tool. The lessons are related to mixing multiple databases in a deployment (administration and monitoring overhead) and the acknowledgement that issues of schema design, […]

Luxbet, MariaDB and Melbourne Cup

Yesterday was Melbourne Cup day in Australia – the biggest annual horse race event in the country, and in the state of Victoria it’s even a public holiday. Open Query does work for Luxbet (part of Tabcorp), and Melbourne Cup day is by far their biggest day of the year in terms of traffic. It’s […]

Hint of the day: noatime and relatime in fstab

It’s been written about everywhere, but since we keep spotting installations in the wild where people don’t know about it, it probably deserves another mention. By default, Linux uses the atime option on a disk mount, which means it writes a timestamp (e.g. a write to the drive) every time it reads anything. So in […]

Temporary Tables and Replication

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based. Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following: Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT); Wait for slave to replicate this statement, then […]

Non-Deterministic Query in Replication Stream

You might find a warning like the below in your error log: 130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what […]