We ran across the following error on a MySQL slave server recent: mysql> SHOW SLAVE STATUS \G <snip> Last_Error: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: '<database_name>'. Query: '<query>' <snip> In this […]

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many people have a hard time with NULL, which is why these bugs are so hard to …

MySQL Workbench is a great tool for developers to map out and visualise databases. What many people know is that it’s also a great way to make upgrading and writing upgrade scripts for your components really painless too.

Let’s say you created a small component that became popular over night. Tens of thousands of downloads from on the Joomla Extensions Directory. So you get inspired and put some more work into improving and bugfixing this component even more, and while doing that you change and add on to the database schema.

Now you need to create and distribute an update script with the next version of your component, and MySQL Workbench makes creating this update script really easy. Here is what you need to do:

Install MySQL Workbench

I’ll not help you with this. Just go to the MySQL Workbench homepage and follow the instructions from there. …

The closest thing I know of to a “Northern Virginia MySQL Meetup” is the Sterling Database Data Solutions Group. I got in touch with the organizer and we scheduled a meeting next Wednesday July 28th. I’ll be presenting, and so will someone from Fusion-IO, a solid-state storage vendor. This is on short notice, so tell your friends about it! It would be great to grow a strong monthly meetup presence in this area.

Here’s the abstract I sent: “This talk covers best practices to help you get the most out of MySQL performance. It assumes you know a database well, though it need not be MySQL. We’ll cover several angles of the topic. Configuration is usually the first thing people ask about. Although it’s possible to misconfigure MySQL and get …

OmniTI’s Surge conference is looking really good — and I’m going to be speaking there. The CfP just closed, so the list of speakers is still growing, but it already includes impressive names such as Neil J. Gunther. So far, this speaker list has zero fluff, and reminds me of the Percona Performance Conference. I’ll be talking about how not to shard your systems. Sharding is no fun and it’s costly. If you don’t have to do it — and many applications don’t need to, with orders-of-magnitude performance improvements in MySQL — you should not.

Related posts:

1. …

I hate losing useful SQL queries, so I tend to save them. Ever so often, I stumble across a file of queries from some work I was doing months ago. The following two queries are just such a case. I was analyzing some call data for a call center that uses Asterisk. I set up a temporary database for this, so I can't even run them again. Still, it's nice to save them away for a rainy day.

Call Center Call Volume by Day of Week and Hour

SET @tz = 'EST5EDT';
select 
  DAYNAME(CONVERT_TZ(start, 'GMT', @tz)) AS "Weekday",
  HOUR(CONVERT_TZ(start, 'GMT', @tz)) AS "Hour",
  COUNT(id), 
  SUM(duration), 
  SUM(billsec), 
  AVG(duration), AVG(billsec) 
  FROM cdr 
    WHERE dcontext IN ('Queue1', 'Queue2', 'Queue3', 'Queue4')
      AND duration > 60
    GROUP BY Weekday, Hour
    ORDER BY WEEKDAY(CONVERT_TZ(start, 'GMT', @tz)), Hour 
;

I was trying to figure out …

For those of you who miss what Maatkit’s mk-audit tool (now retired) gave you, there’s a pair of tools in Aspersa that more than replaces it. I wrote previously about the summary tool. I don’t think I have mentioned the mysql-summary tool. It has been under development for a while, and at this point it has quite a lot of functionality. You can see a sample of the output on its wiki page.

Related posts:

1. Apsersa’s summary tool supports Adaptec and MegaRAID …

Guerrilla Capacity Planning

Guerrilla Capacity Planning. By Neil J. Gunther, Springer 2007. Page count: about 200 pages, plus appendixes. (Here’s a link to the publisher’s site.)

Of all the books I’ve reviewed, this one has taken me the longest to study first. That’s because there is a lot of math involved, and Neil Gunther knows a lot more about it than I do. Here’s the short version: I’m learning how to use this in the real world, but that’s going to take many months, probably years. I’ve already spent about 10 months studying this book, and …

Cloud Application Architectures

Cloud Application Architectures. By George Reese, O’Reilly 2009. (Here’s a link to the publisher’s site).

This is a great book on how to build apps in the cloud! I was happy to see how much depth it went into. It’s short — 150 pages plus some appendixes — so I was expecting it to be a superficial overview. But it isn’t. It is thorough. And it is also obviously built on his own experience building very specific applications that he uses to run his business — he isn’t preaching about stuff he doesn’t know first-hand. Finally, George Reese is a good writer! It’s impressive. This is how he covers so much ground with so much depth in so few pages, …

Web Operations

Web Operations. By John Allspaw and Jesse Robbins, O’Reilly 2010, with a chapter by myself. (Here’s a link to the publisher’s site).

I wrote a chapter for this book, and it’s now on shelves in bookstores near you. I got my dead-tree copy today and read everyone else’s contributions to it. It’s a good book. A group effort such as this one is necessarily going to have some differences in style and even overlapping content, but overall it works very well. It includes chapters from some really smart people, some of whom I was not previously familiar with. John and Jesse obviously have good connections. A …