A while back I was at a seminar on migrating database applications to MySQL. A lot of the attendees were Oracle users. Based on their questions, comments and conversations during lunch, I made the following list of things Oracle users need to know about migrating to MySQL. Most of these are “gotchas” that would be contraindications or require some thought about a workaround.

Note: this is not meant to be MySQL-bashing. Some of these limitations are going to be fixed in future versions of MySQL, but they generally apply to current GA version 5.1. Some things are possible to achieve by choosing one particular way to use the server, at the exclusion of other things (e.g. Cluster rules out foreign keys, spatial data types rules out transactions). I am posting this list at the request of some Oracle DBAs who asked me to produce it for them.

1. Subqueries are poorly optimized.
2. Complex queries are a weak …

If you’ve been holding out for that golden opportunity, now’s a great time to get involved in Maatkit.

Until now I haven’t really made a conscious effort to open-source the decision process and get people involved; Maatkit has been largely driven by so-called “real-world needs,” as perceived through my little lens on the world (and emails from the whole Percona team telling me when something’s wrong). I guess I am likely to remain some kind of benevolent dictator, because I created Maatkit and historically I’m the main hacker. But it doesn’t have to stay that way, and the project and users will be better off if it doesn’t.

So I’ve been trying to break out of the rut of just having some little email exchange with people using the tools, and bring things onto the …

I think it’s occasionally interesting to look back at what people wrote about things while they were happening, especially after big changes (founder and CEO leaving, for example). Here’s a blog post from May of 2008, by Henrik Ingo and Ignacio Torres, about MySQL’s business model. No commentary — just the link.

After I wrote about things you need to know about MySQL backups, a customer contacted me and asked me what I know about SecoBackup for MySQL. I see it has a very low cost and Percona has Amazon accounts for testing purposes, so I quickly downloaded s3sql_2.2.0.1-2.01_i386.deb, installed it, configured it, and gave it a whirl.

Since I just want to see what it does to take a backup, I started up a sandbox running from /tmp/12345 and configured it to backup msandbox:msandbox@127.0.0.1 (I shut down my main mysqld on my laptop to make sure it can’t connect to the default instance).

Then I configured a backup set and tried to take a backup. Right away I saw it isn’t full featured enough. It doesn’t let you specify a port to connect to. This …

Last time I demonstrated a case where stored procedures are slow when they have to do computationally expensive work.  The more interesting, to me at least, question is how slow is SQL?  The answer to that is far more complex. 

For example, this simple SQL takes 2.2 seconds to run on MySQL.  This is a painfully faster than the 696 seconds it took a stored procedure to produce similar results. 

select customerName, count(*)
  from Salet s
  join customer c on s.customerId = c.customerId
group by customerName

As demonstrated in the previous article, the equivalent C# code took 1.4 seconds to produce the same results.  Some may find it surprising that it take less time to ship 1 million rows out of the database and then summarize it in code than it does for MySQL to summarize the same data in the database. 

In this simple case the performance difference …

I keep reading that Ma.gnolia’s data is permanently lost because “a specialist had been unable to recover any data from the corrupted hard drive.” This is not in itself a reason to consider data completely lost.

It is not clear to me whether the hard drive itself is unusable, e.g. the spindle won’t spin and the head won’t read the ones and zeroes, or whether the filesystem is corrupted. It sounds to me, from reading Larry Hallf’s comments, like it’s a simple matter of filesystem corruption. And even if the disk is dead, there is apparently a backup made from the corrupted filesystem, so there should be more than one way to …

I just returned from a MySQL Federal Migration Boot Camp, jointly presented by Carahsoft and Sun/MySQL. It was a half-day seminar on the topic of migrating applications to MySQL, targeted to the US Government sector. Specifically, most of the audience seemed to be running Oracle, though there were some users of other systems (Microsoft SQL Server, Sybase, Informix, etc).

Ronald Bradford presented the whole thing. I thought he did a great job giving a fair and balanced look at topics such as what types of applications are good candidates for migration, what gotchas you might encounter, etc. I’m sure someone looking at migrating in earnest would benefit from a whole day’s discussion (or a more focused engagement with a consultant), but I thought it was a great way to help people figure out whether …

I have a Google Alert on “percona”. (And many other things — great way to keep tabs on competitors, what people are saying about you, etc.)

I’ve been seeing increasing amounts of this type of thing:

MySQL server version: 5.0.67-percona-3 CATEGORY QUERY: SELECT wp_term_taxonomy.count as ‘count’, wp_terms.term_id, wp_terms.name, wp_terms.slug, wp_term_taxonomy.parent, wp_term_taxonomy.description FROM wp_terms, wp_term_taxonomy WHERE …

Go to the page in question (sorry, I won’t link it) and you don’t see “percona” anywhere on it. View the source and you do. It’s WordPress debugging output.

I’m glad to see the anecdotal evidence of more and more active use of the Percona server builds, but at the same time, it’s kind of like finding out that your best friend made it onto the Jerry Springer Show. Sometimes I think …

As you may know, MySQL has a dual-licensing model. You can get the source under the GPL version 2, or you can buy a commercial license.

I’ve recently been hearing a lot of confusion about when you have to buy a commercial license. People I’ve spoken to wrongly believe that they’re required to purchase a license if they’re going to use MySQL in anything but a not-for-profit business, for example. I don’t know how these notions get started, but they do.

So when are you required to buy a commercial license? It’s very simple: when you want to do something with MySQL that the GPL doesn’t permit.

I am not a lawyer, and you should do your own legal research, but misinterpretation of the GPL is rampant and I think I should try to counteract the misinformation about it if I can. Note that in this article I will …

I see my collague Anders wrote some thoughts on date handling in MySQL. I recently had to explain this really down to fine details for a customer, so Anders, everyone else, here's my conclusions...

All the fine details of allowed ways to enter dates are explained here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

With dates, just as in many other cases, MySQL is very flexible in how you can input data. But one thing is that, between the multitudes of formats that are allowed, MySQL does *not* by default allow you to enter a non-existant date, e.g. "31st of February 2009". I know some other DB products allow that - they then store it as a correct date automatically (3rd of March 2009). This is mentioned at the end of …