Explain.... It is a very simple command that I feel is one of the most overlooked commands by new MySQL users. It is also a very valuable command available for MySQL. I realize I am preaching to the choir for a lot of MySQL users. However, for everyone who uses explain, we are bound to have many who do not. The MySQL documentation on this is great and available here and Optimizing Queries with EXPLAIN
Developer and a dba issues will continue for years,  but we can at least start on a level playing field. When writing a query, regardless of what it is, it is a good practice is to start it with explain first. This can achieve a couple things for you.

• It checks your syntax to help you avoid mistakes.
…

I’ve been following the development of Tungsten Replicator for quiet some time now, and recently was fortunate enough to find the time to take a look at the product in more detail. If you haven’t heard of Tungsten Replicator yet, it’s an open source database replication engine that can be used to complement or completely [...]

MySQL Replication is a powerful tool and it’s hard to find a production system not using it. On the other hand debugging replication issues can be very hard and time consuming. Especially if your replication setup is not straightforward and you are using filtering of some kind.

Recently we got an alert from our monitoring system that replication stopped on production slave with the following error:

Can't find record in 'some_table', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log binlog.000011, end_log_pos 735766642

This means that a ROW-based replication event was going to be applied on slave, but could not find the row it was supposed to be applied to. This is something I like about ROW format — it allows you to catch such data synchronization issues right away. In this particular case MIXED format was used, but if this event was written in STATEMENT format, slave would just apply it …

MySQL Stickers

How to measure a Storm’s Fury One Breakfast at a Time is a non MySQL-Centric story I need to share with my fellow DBAs. The US Federal Emergency Management Agency has a metric to judge the severity of a storm. They monitor the status of Waffle House Restaurants. Waffle House goes to extreme measures to open up storm damaged or impacted restaurants that need to be studied by anyone who had any disaster recovery duties. Their measures are so extensive that FEMA now tracks which Waffle Houses are open, closed or operating in a diminished capacity after a storm. After Katrina, seven restaurants were destroyed and one hundred more shut down. But they were quickly …

MySQL Cluster* is one of the most advanced and scalable databases available today and, despite what its name might suggest, it is also a brilliant NoSQL database**.
Let me discuss this statement!
First, let’s discuss the high level issues that NoSQL databases try to address: -     Scalability. Traditional RDBMS technology was designed four decades ago, and is not appropriate for today’s Big Data requirements. Database systems today need to be able to scale horizontally over multiple machines to handle millions of users. As the CAP theorem states, it is not possible to achieve availability, scalability and consistency in one system. Several NoSQL databases sacrifice consistency for availability and scalability. -     RDBMS has a rigid data model. Once a normalized data model has been defined in an RDBMS, it is …

As announced on the Maatkit and Aspersa mailing lists, Daniel and I have created a new toolkit that represents the union of the two, and will be focusing efforts on this Percona Toolkit moving forward. The goal is to make them simpler and significantly more powerful, and to create more tools. The tools will continue to be open-source, but will be developed primarily to meet our MySQL support and consulting staff’s needs.

If you’re interested in challenging software engineering in Perl and shell, then please apply online. You can work online from anywhere, but I strongly prefer someone in the Americas timezones.

Further Reading:

• Planned change in Maatkit & Aspersa development …

Time-Based Blind SQL Injection

 
Overview
=======
Blind SQL Injection is an attack which the attacker gets an indication for the query execution success. The attacker doesn’t get the query results.
Most of the time, the indication bases on server errors or customized application errors.

Time-Based Blind SQL Injection
======================
Sometimes the attacker might not be able to identify the query execution success, because the server/application doesn’t show any error.
One of the techniques to get an indication for the query execution success called Time-Based Blind SQL Injection.
With this technique, the attacker executes functions that take some time to finish (for example: Benchmark, Delay, etc.). By measuring the time took the application to response, the attacker might be able to identify if the query executed successfully or the query execution failed. …

If you run a replicated MySQL setup and have a master failure, you may see the following error in your error log.

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)

At this point your slave will not be replicating and you will need to recover from the situation. But why did it occur in the first place and what does it mean to your data integrity?

Why it occurs

This error will occur if the master stops unexpectedly. This could be as a result of a hardware failure (like a disk error) or a power loss.  Any time this happens there is a chain of events that result in the problem.

First of all MySQL holds a lot of information in memory.  Even with InnoDB there may be a pending transaction that has not yet written to the log, but even more importantly the server may not be able to write the Stop event message to the …

Percona is glad to announce the release of Percona Server 5.5.15-21.0 on August 31, 2011 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.15, including all the bug fixes in it, Percona Server 5.5.15-21.0 is now the current stable release in the 5.5 series. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.5.15-21.0 milestone at Launchpad.

Improvements Improved MEMORY Storage Engine

As of MySQL 5.5.15, a Fixed Row Format …