The latest from the MySQL community

I’m using innotop again every day, for the first time in a few years. I found that I didn’t like the tool that the younger and less experienced version of me created. It is very flexible and has the ability to surface a lot of information about MySQL, but not all on one screen. I wanted a “single pane of glass” health dashboard for the servers I’m monitoring, instead of having to look on various screens for important bits of information.

The good news is, innotop is very extensible and I know the code because I wrote most of it, so in a short while I had a dashboard that suited me. I committed these changes to trunk, so if you wish you can easily get the code:

$ wget innotop.googlecode.com/svn/trunk/innotop
$ chmod +x innotop
$ ./innotop

You can select the new ‘A’ mode by pressing the capital ‘A’ key. Here is a screenshot (click for full size version):

…

Killing long-running idle transactions is a good way to increase the uptime of a MySQL server. This may sound strange, but open transactions will eventually bring the server down, and it is better to hurt a single application than the many that will be hurt when that happens.

Long-running idle transactions are usually caused by a programmer mistake or an unexpected condition that causes an application not to be able to do its work. The potential number of sources for such problems is unlimited, so it’s virtually impossible to prevent long-running transactions. You can find and solve them when they happen, but you can’t ensure that you’ll never get one from an unexpected source (because, by definition, the source is unexpected).

That is why it’s a good idea to run an idle-transaction killer. There are also other types of things you can profitably kill and help your uptime even more, but those are sometimes more complex to …

MySQL is a database of compromise. Compromise between running a production-ready relational database and being popular with all sorts of hackers - mostly the ones that don't really like SQL. And because they don't really like SQL, they choose MySQL, as MySQL is very forgiving. It is just as forgiving as their favourite language PHP, … Continue reading MySQL Bad Idea #384 →

Just for the pure insane fun of it, I accepted the challenge of “what can you do with the text format of the schedule?” for BarCampMel. I’m a database guy, so I wanted to load it into a database (which would be Drizzle), and I wanted it to be easy to keep it up to date (this is an unconference after all).

So… the text file itself isn’t in any standard format, so I’d have to parse it. I’m lazy and didn’t want to leave the comfort of the database. Luckily, inside Drizzle, we have a js plugin that lets you execute arbitrary JavaScript. Parsing solved. I needed to get the program and luckily we have the http_functions plugin that uses libcurl to allow us to perform HTTP GET requests. I also wanted it in a table so I could query it when not online, so I needed to load the data. Luckily, in Drizzle we have the built in EXECUTE functionality, so I could just use the JavaScript to parse the response from the HTTP GET request and …

When you're spoiled with Oracle's fabulous query transformation capabilities and its really well-done cost-based optimiser, then you might forget how difficult SQL query tuning used to be in the "old days" or with those less sophisticated databases. Here's a really nice explanation of the various means of implementing an ANTI-JOIN in MySQL: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

I have seen a few posts on DBA.SE (where I answer a lot of questions) recommending the use of semi-synchronous replication in MySQL 5.5 over a WAN as a way to improve the reliability of replication. My gut reaction was that this is a very bad idea with even a tiny write load, but I wanted to test it out to confirm. Please note that I do not mean to disparage the author of those posts, a user whom I have great respect for.

What is semi-synchronous replication?

The short version is that one slave has to acknowledge receipt of the binary log event before the query returns. The slave doesn’t have to execute it before returning control so it’s still an asynchronous commit. …

The MySQL developer tools team announces the availability of version 5.2.41 of the MySQL Workbench GUI tool. This version has significant improvements in several parts of the tool and also includes the new Migration Wizard plugin.

The new Migration Wizard presents an easy to use GUI for migrating databases from third party products to MySQL. In this initial release, support for Microsoft SQL Server is included, in addition to other products that support ODBC, such as PostgreSQL.

Other improvements include:

• More than 100 bug fixes
• Code completion in the SQL Editor (beta)
• Better handling of schema synchronization in modeling

For a full list of issues fixed in this release, see  http://dev.mysql.com/doc/workbench/en/changes-5.2.x.html

Please get your copy from our Downloads …

If you haven’t checked into pt-online-schema-change yet, now’s a great time to sign up for my free webinar Thursday, July 19, 3-4 PM EDT. I’ll explain and demonstrate the tool, and walk you through everything you need to decide whether it’s right for you.

If you haven’t even heard about pt-online-schema-change yet, the short version is it lets you alter tables in MySQL with practically no downtime at all. This even works for really big tables that might take hours or days to alter — normally a blocking operation. In fact, 37Signals just wrote a blog post saying it has helped them achieve 99.99% uptime for their Basecamp …

One of the more notable success stories of the open source world is in the field of databases. A company with a strong commitment to open source has seen tremendous growth and success in the enterprise while contributing to a hugely respected open source code base. Who is that? Maybe your first thought was MySQL, now owned by Oracle. But unlike MySQL, this company is actually taking business away from Oracle so effectively that it's seen an 80 percent revenue growth in the last year.

read more

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 …