The Debt of Dirty Data

--Hey, why can't our Insurance and Billing system just talk to our Admissions system?  We just get too many errors in patient names and numbers, and fixing all of this is becoming a nightmare.

--Oh, alright let's integrate them then.

Duplicate data, multiple databases, and the need to communicate new records and updated records, has some organizations in a frenzy.  The mantra "just integrate them" makes it seem like there's a technology that will make this happen like magic.

It's not just the technology, it's the data.

Even if you follow database normalization rules (arcane, mathematically-based rules for designing a database for the non-database types), that doesn't mean you have clean, tight data.  Even still, in all of the databases I've seen, over the years, I've still yet to see a perfectly designed database anyway.  Add to …

Experienced MySQL DBAs know that temp tables cause major problems for MySQL replication.  It turns out the converse is also true:  replication can cause major problems for temporary tables.

In a recent customer engagement we enabled Tungsten Replicator with a MySQL application that originally ran on a server that did not use replication.  QA promptly discovered reports that previously ran in 10 seconds were now running in as many minutes.  It turned out that the reports used temp tables to assemble data, and these were being written into the master binlog.  This created bloated binlogs and extremely slow reports.  We fixed the problem by enabling row replication (i.e., binlog-format=row in my.cnf).

A common DBA response to temp table problems is to try to eliminate them completely, as suggested in the excellent …

This is the second and final part of my notes from the MySQL conference. In this part I'll focus on the technical substance of talks I saw, and didn't see.

More than ever before I was a contributor rather than attendee at this conference. Looking back, this resulted in seeing less talks than I would have wanted to, since I was speaking or preparing to speak myself. Sometimes it was worse than speaking, for instance I spent half a day picking up pewter goblets from an egnravings shop... (congratulations to all the winners again :-) Luckily, I can make up for some of that by going back and browse their slides. This is especially important whenever 2 good talks are scheduled in the same slot, or in the same slot when I was to speak. So I have categorized topics here along various axes, but also along the "things I did see" versus "things I missed" axis.

My own talks

…

I have finally recovered from my trip to Santa Clara enough that I can scribble down some notes from this year's MySQL Conference. Writing a travel report is part of the deal where my employer covers the travel expense, so even if many people have written about the conference, I need to do it too. And judging from the many posts for instance from Pythian's direction, Nokia is perhaps not the only company with such a policy?

Baron's keynote

There has usually always been something that can be called a "soft keynote". Pirate Party founder Rick Falckvinge speaking at a database conference is a memorable example (I still keep in touch with him, having met him at the Hyatt Santa Clara). This year there was one less day, and therefore less keynotes. The soft keynote was therefore taken care of by Baron using some time out of Peter's opening keynote. Baron's talk was an ode to the conference itself, underscoring the meaning of the …

Last Friday the Dutch TV program Zembla aired part two of the "verzuimpolitie" series. The first part was mainly about how employers could access medical information about employees. There is a news article about the second part here (with google translate).

…

Two separate posts on gamedev.stackexchange.com drew my attention. It appears that some game programmers are getting it WRONG.

Superficially, it seems that in some games (think of scrolling games with enemies appearing of the edge of the screen), the enemies are all approaching at a constant rate.

But what REALLY happens, is that the viewport scrolls at a fixed rate, and each enemy starts at a fixed world-space position, and activates when the viewport reaches a particular place.

This is not a very important distinction for the player, but its a HUGE distinction for the programmer.

If the developer thinks it's good to have 1 million enemies, all of which are created at the beginning of the game, and then move each one of them, every single tick, to a slightly different position as the camera moves, then they are probably doing it wrong:

…

The memcache protocol has an INCR command, which atomically increments a stored value. MySQL Cluster is able to take simple operations (like incrementing a counter) and "push them down" to run on a data node with a lock held. Acquire the lock, increment the value, read it, and release the lock.

Originally in memcached, the incrementing sequence number was local to one memcache server, and was also volatile. Whenever the memcached server restarted, the sequence would be reset. Today, though, we can put cluster and memcached together, and instead of a volatile local sequence number, we get a durable and shared one.

This is a very simple application. Keep reading to see the whole thing, start to finish. In my example I'll use MySQL Cluster 7.2.4 and the memcached ASCII protocol. There is a minor (but annoying) bug, already fixed in 7.2.6, that prevented this from working with the binary protocol. So, keep that in mind, and …

The memcache protocol has an INCR command, which atomically increments a stored value. MySQL Cluster is able to take simple operations (like incrementing a counter) and “push them down” to run on a data node with a lock held. Acquire the lock, increment the value, read it, and release the lock.

Originally in memcached, the incrementing sequence number was local to one memcache server, and was also volatile. Whenever the memcached server restarted, the sequence would be reset. Today, though, we can put cluster and memcached together, and instead of a volatile local sequence number, we get a durable and shared one.

This is a very simple application, and I’m going to walk through the whole thing. Keep reading to see the whole thing, start to finish. In my example I’ll use MySQL Cluster 7.2.4 and the memcached ASCII protocol. There is a minor (but annoying) bug, already fixed in 7.2.6, that prevented this from working with the …

A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

Usage is simple:
garfield ~ # ./extract.sh -f dump.sql -d redmine > redmine.sql
garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows > redmine-workflows.sql

You can grab the script from Downloads page.

extract.sh

#!/bin/bash

#
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
#

function help()
{
  cat <<EOF
  Usage: [-h] -f <dump file> -d <database …

Recent Tips and News on Java, Java EE 6, GlassFish & more :