Recently we faced an issue when Nagios reported significant amount of “Locked” queries.
To investigate and debug the issue we needed to get more insight about the state of MySQL and the OS at the time the locks occurred.
This is how we got the much needed information:


wget http://bit.ly/1ltoZtk -O pt-stalk
chmod +x pt-stalk
mkdir -p /tmp/pt-stalk
sudo pt-stalk --daemonize --notify-by-email <EMAIL> --log /tmp/pt-stalk/pt-stalk.log --user root --dest /tmp/pt-stalk --function processlist --variable State --match Locked --threshold 20 --cycles=10 --sleep=15 --run-time=15


In this case we are running pt-stalk utility in background and trying to catch the case when there are 20+ Locked queries in processlist. When a match occurs, pt-stalk will collect a lot of OS and MySQL info and notify us by email.

Importing a text file containing a list of SQL commands into MySQL is a straightforward task. All you need to do is simply feed the file contents through pipe into MySQL command line client. For example: mysql app_production < dump.sql.

The reasons for doing such imports can be very different - restoring MySQL backups created with mysqldump, manually replaying binary log events or performing database migrations during software roll-outs.

While the task is simple, the import may not end successfully and when this happens, how to tell what the problem was?

MySQL errors

Whenever database hits an error, MySQL produces an error message that describes the problem and the import process stops immediately. If the message is not clear enough, you can always refer to the reported line number, which is the line number inside the source SQL file. This way you can locate the precise command or query that …

MySQL lets database administrators define access rights on many levels – from the ability to run global commands down to access to individual columns. Some rights can be applied to many different objects, such as for example SELECT or UPDATE, which can be granted globally or restricted only to certain databases or tables, while others are only meant for one specific purpose. An example of the latter could be FILE privilege, which permits user to interact with the file system from inside a database instance. It only makes sense as the global right and not anywhere else.

As any other activity that produces changes, GRANT statements are replicated to MySQL slaves. Regardless of the binary log format setting, such events are always logged in STATEMENT format. It is likely because the command needs to handle more than just updating the contents of a few system tables, so such design allows each slave to fully execute the changes within their …

In a world driven by computers, most companies rely on systems that are entirely built around databases. Losing data, or even as little as losing the full control over it, could bring any business down. Frequently databases hold sensitive information such as personal details, transaction statements, credit card data – among many other things. This is also why running certain types of databases is regulated in many countries by local as well as international laws – especially in Europe.

What is at stake?

• Availability. When a database or data disappear, business stops. Assuming you have working backups you can restore from, this is the least of all concerns.
• Confidentiality. Your secrets, private information of your customers and anything else that you decided not to make publicly available may be leaked.
• Privacy. Personal …

If for any reason you couldn’t attend my talk at FOSDEM earlier today on improving MySQL security, I have already uploaded the slides. You can download them from here.

Without going into unnecessary details about CVE-2012-5612 bug; an authenticated database user could use this flaw to crash MySQL instance or even try executing some code. Is it a serious problem? Do you need to worry about it?
I recently saw some comments that “My database is safe, only application can access it. Is it really a serious bug?” which scared me a bit. Such opinion might be fine but only for closed systems with limited access to MySQL, but what if you are hosting provider that share single MySQL instance between several accounts? Here, security matters a lot! Especially if you allow users to create test/demo accounts.

Based on comments from security lists, only 5.5 family of MySQL is affected since the vulnerable MDL subsystem was first implemented in mysql-5.5. That’s why I decided to do some testing against most popular 5.5 distributions on market.

My tests covered:

• vanilla …

For those who don’t know, several security vulnerabilities in MySQL were discovered recently and published to the security mailing lists. Yet another time, remote attacker can badly hit your production systems causing long downtime.

List of security issues:

• • CVE-2012-5611 MySQL (Linux) Stack based buffer overrun PoC Zeroday
    • http://seclists.org/fulldisclosure/2012/Dec/4
    • https://bugzilla.redhat.com/show_bug.cgi?id=882599
  • CVE-2012-5612 MySQL (Linux) Heap Based Overrun PoC Zeroday
  • …

For any person actively working with MySQL databases on the command line level, logging in, logging out for a few moments, and then logging in again, all repeated many times, not only eventually becomes annoying, especially with passwords that aren’t easy to type, but it also can take a lot of time over the course of a day. For a long time I’ve been relaying on something that allows me to avoid most of this effort while working. The solution is of course not to log out unless you actually want to.

Linux, BSD and Solaris all allow suspending a running task and resuming its execution at a later time. This can be used to temporarily exit MySQL client without having it to leave the database.

When inside MySQL, you can press Ctrl+Z to suspend the client program and return to the system shell. You will be able to bring the database client back at any time by running fg command …

The other day at PSCE I worked on a customer case of what turned out to be a problem with poor data locality or a data fragmentation problem if you will. I tought that it would make a good article as it was a great demonstration of how badly it can affect MySQL performance. And while the post is mostly around MyISAM tables, the problem is not really specific to any particular storage engine, it can affect a database that runs on InnoDB in a very similar way.

The problem

MyISAM lacks support for clustering keys or even anything remotely similar. Its data file format allows new information to be written anywhere inside a table. Anywhere can be either at the end of a file where it can be simply appended or an empty space somewhere in the middle left after previously deleted row(s). This implies no particular order in which rows are stored unless there are absolutely no …

Do you rely on pt-diskstats from Percona Toolkit instead of the standard iostat a lot? There appears to be a nasty bug in pt-diskstats 2.1, which makes it produce bad results.

I noticed some of the numbers I was getting weren’t right, so I tried running iostat and two different releases of pt-diskstat side by side. Here’s what I got:

I can understand the slight differences between the lines in iostat and pt-diskstats 2.0 as they probably weren’t reading /proc/diskstats contents in the same moments, so the values they were seeing could be a bit different. However both lines practically show the same thing.

On the other hand, the line based on the pt-diskstats 2.1 output looks completely different. It seems like maybe the script returns some kind of averages rather than per second deltas. I filed a …