At the 1st Latin American MySQL, NoSQL and Cloud Conference, my podcast co-host Gerry and I gave a talk on Alerting with MySQL and Nagios – download the PDF slides!

We just ran into a nasty InnoDB bug that only seems to exist in MySQL 5.5.25:

An InnoDB update that modifies a rows primary key can trigger some recursive behavior that creates new rows until all disk space is exceeded. This does not affect all primary key updates in general but only gets triggered when a few other conditions are also met, so you're not too likely to run into it, but if you do your mysqld server will waste a lot of IO bandwidth and storage space on this and will usually eventually run out of disk space.

read more

If you use MySQL with InnoDB (most everyone) then you will likely see this error at some point. There is some confusion sometimes about what this means. Let me try and explain it.

Let's say we have a connection called A to the database. Connection A tries to update a row. But, it receives a lock wait timeout error. That does not mean that connection A did anything wrong. It means that another connection, call it B, is also updating a row that connection A wants to update. But, connection B has an open transaction that has not been committed yet. So, MySQL won't let you update that row from connection A. Make sense?

The first mistake people may make is looking at the code that throws the error to find a solution. It is hardly ever the code that throws the error that is the problem. In our case, it was code that was doing a simple insert into a table. I had a look at our processing logs around the time that the errors were …

I’ve been working on a data archival project over the last couple weeks and thought it would be interesting to discuss something a bit counter-intuitive. Absolutes are never true, but when getting rid of data, it’s usually more efficient to insert the data being kept into a new table rather than deleting the old data from the existing table.

Here is our example table from the IMDB database.

mysql> show create table title\G
*************************** 1. row ***************************
       Table: title
Create Table: CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) …

Read the original article at The Art of Resistance

Sometimes, you have to be the bad guy. Be resistant to change. Here’s a story about how stubbornness pays off. As we’ve written about before A 4 letter word divides Dev & Ops.

I had one experience working as the primary MySQL DBA for an internet startup. Turns out they had Oracle for some applications too. And another DBA just to handle the Oracle stuff.

So it came time for Oracle guy to go on vacation. Suddenly these Oracle systems landed on my shoulders. We reviewed everything in advance, then he bid his goodbyes.

Almost as soon as he was out the door I …

I don’t like stupid benchmarks, as they waste my time. I don’t like stupid marketing, as it wastes my time too. Sometimes I succumb to those things, and now in return I want to waste your time a bit.

So, this MemSQL thing, written by some smart guys has been making rounds in press and technical community. Centerpiece of all the communication was:

“MemSQL, the database they have developed over the past year, is thirty times faster than conventional disk-based databases”

Though I usually understand that those claims don’t make any sense, I was wondering what did they do wrong. Apparently they got MySQL with default settings running and MemSQL with default settings running, then compared the two. They say it is a good benchmark, as it compares what users get just by installing standard packages.

That is already cheating, because systems are forced to work in completely different profiles. …

Two extra tips when you try to (manually) bootstrap MySQL on Windows from the command line:

1. When bootstrapping, use --no-defaults or --defaults-file.
2. Use --standalone when starting the MySQL server.

The first point really bugged me yesterday. I’m used to installing MySQL manually and having no option files available where MySQL would read them by default.

On Windows, however, I used the MySQL installer. So, when I launch unit testing for Connector/Python it bugged me saying log files of InnoDB didn’t match the configuration. This is indeed a newbie error..

features supported are:

• on-update/delete restrict
• on-update/delete noaction
• on-update/delete setnull
• on-update/delete cascade
• online add/drop foreign key

The foreign keys are implemented inside data-nodes, and will hence work regardless of which type of client you use. SQL, memcache, Cluster/J, ndbapi, {insert your favourite connector}.

somewhat interesting differences between ndb and innodb are:

1. i haven't implemented the Innodb "extension" has that allows parent reference to be non-unique. It seems ok i think...wl#148 describes it as a feature not to have this extension
2. For innodb "noaction" is identical as "restrict".
   For ndb, noaction means deferred check. I.e the constraint is checked before commit.
   (i think …

The other day Twitter was down and I had no place to comment on Twitter being down. This got us to talking about scaling at work. I was reminded of the recent slides posted from Instagram about their scaling journey. They are great slides. There is only one problem I have with them. They are just the same slides that you would find from 2000 about scaling.

I have to say, I like Instagram. My daughter has something like 1,000 followers on Instagram. And good for them for being bought by Facebook for a bajillion dollars. This is not a dig on them really. This is a dig on our industry. Why did Instagram have to learn the hard way how to scale their app? I want to point out some of their issues and discuss why its silly they had to learn this the hard way.

…

I normally don't do this. When I see someone write a blog post I don't agree with, I often just dismiss it and go on. But, this particular one caught my attention. It was titled PHP vs Node.js: Yet Another Versus. The summary was:

Node.js = PHP + Apache + Memcached + Gearman - overhead

What the f**k? Are you kidding me? Clearly this person has NEVER used memcached or Gearman in a production environment that had any actual load.

Back in the day, when URLs and filesystems had a 1:1 mapping, it made perfect sense to have a web server separate from the language it is running. But, nowadays, any PHP app with attractive URLs running behind the Apache web server is going to need a .htaccess file, which tells the server a regular expression to check before serving up a file. Sound complex and awkward …