Category Archives: MySQL

MySQL 5.0: Remarkably Painful

MySQL 5.0 has a ton of new features. In fact, several tons. Many people and companies were waiting endlessly for some of these features. Some of the new features include: Views, Stored Procedures and Stored Functions, Triggers, and some extra optimizations. Hoorah.

However, not all is peaches. In fact, there are few peaches to be found. Consider the rest of this post a bitch/complaint session and a call for a return to sanity. Let’s take a look at some of the major features of MySQL 5.0:

Views — Views are a great idea. Overall, they even work great. Their biggest downfall is that their MERGE algorithm doesn’t work with UNION, which means that the view engine falls back to the TEMPTABLE algorithm instead. That means it completely forms the view: running the full UNION, dumping the results into a temporary table, and then applying the view against that temporary table. What that really means is that views are useless for replacing MyISAM merge tables, which means we still have no way to do the same thing with InnoDB.

Stored Procedures — No way to trigger or return an error, since no form of RAISE is implemented. Yes, I know you can do other hacks to make it error, but that’s useless.

Stored Functions — I think they might have gotten stored functions right. They aren’t terribly complicated, though.

Triggers — Okay, I don’t even know where to start on triggers. They have a number of major problems:

  • Only one trigger per table per action — MySQL missed the point of one of the main applications of triggers: auditing. Since you can only have one trigger per table per action, you cannot use triggers for the typical application purposes at the same time as you use it for auditing. Yes, a single trigger can take more than one action, but that’s not the same thing.
  • Replication — Replication of triggers is still fundamentally broken in 5.0.19. You can, on the master, create a trigger that breaks replication. Yes, it’s fixed in 5.0.20, but that doesn’t help me, now does it?
  • Upgrading — An upgrade from 5.0.16 to 5.0.19 turned ugly because of some trigger compatibility issues.
  • Inconsistent syntax — There is no DROP TRIGGER IF EXISTS for some reason.
  • No atomic replacement — MySQL doesn’t support the OR REPLACE syntax to CREATE TRIGGER meaning that you cannot atomically replace (update the definition of) a trigger on a running installation. Strike two for using triggers for auditing.

Memory Leaks — Okay, this isn’t actually a feature, but a bug. A server running 5.0.19, using all of the above features except for views, is leaking memory like crazy. I’ll write a new post once I track it down.

How are you all dealing with MySQL 5.0? Are you as disappointed as I am?

UPDATE: Looks like stored procedures with replication is broken, too. :(

MySQL 5.1: Partitioning not quite there yet

As I recently found out, MySQL 5.1 claims to support partitioning, but it doesn’t really work, yet.

I tried loading the FlightStats database (feel free to get your copy on dl.flightstats.us) into a MySQL 5.1 instance to try out partitioning. It seemed fairly simple… create an ontime table, partitioned by month, er, wait. Then I hit this bug:

mysql> CREATE TABLE t (
    ->   d DATE NOT NULL
    -> )
    -> PARTITION BY RANGE(d) (
    ->   PARTITION p0 VALUES LESS THAN ('2005-06-02')
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition function near
')
)' at line 5

OK, so I worked around that using YEAR(d)*100+MONTH(d) instead, and calling it an INT. I got a few million rows of data loaded. I tried one of the main features that partitioning is useful for, and one that MySQL claims to support: dropping single partitions. Immediately, I hit this bug:

mysql> alter table ontime drop partition p_2000_01;
ERROR 1037 (HY001): Out of memory; restart server and try again (needed 8
bytes)

Ugh. I guess I have to give up and wait for MySQL 5.1.8 before I can try again. Keep an eye out, I’ll update things once I try again.

Meet me at MySQL UC 2006

Are you coming to the MySQL Users Conference 2006? This year it’s April 24-27, 2006 in Santa Clara, CA. Sadly for me, I don’t get to travel anywhere for the conference: It’s practically in my backyard.

I’m presenting a session and a tutorial, and participating in a Panel and a Hackfest session this year:

  • MySQL GIS: Overview and Tools — I’ll talk about what GIS is useful for, how to get data in and out, and give some demos using PHP.
  • Replication for Scaling and High Availability — An honest look at how replication really works, when it doesn’t work, and what you can expect. An in-depth look at how replication can be used to meet your high availability goals as part of a robust failover system.
  • HackFest A: Creating New UDFs — Build your own UDF, ask questions, see examples, participate in discussion about MySQL’s support for User Defined Functions (UDFs).
  • Panel: Scale Out — Ask questions, compare notes, get expert advice about scaling your high performance MySQL applications.

I’ll see you all there! If you want to meet up with me, or have some ideas for a discussion, BoF, dinner, etc., drop me a line.

Oracle buys Sleepycat, MySQL users yawn

It’s been reported a million times elsewhere, but Oracle has acquired Sleepycat, maker of the BerkeleyDB database. This will probably affect various users of BDB itself, but not MySQL users. If Oracle bought Sleepycat to mess with MySQL, they’re smoking something really good. I don’t think they’re that stupid.

I was quoted by Computer World magazine, in their article Users unworried by Oracle’s purchase of Sleepycat as follows:

Despite its popularity elsewhere, BerkeleyDB isn’t widely used by MySQL users, said Jeremy Cole, a former MySQL employee who now helps oversee about 8,000 MySQL databases used worldwide by Yahoo Inc.

“Basically, the BDB storage engine was added to MySQL in the early days as a prototype for adding transactional support to MySQL,” Cole said. “Once BDB was working with MySQL, InnoDB came along shortly afterwards and quickly surpassed BDB in usefulness, speed and features. No one has looked back since.”

Oracle is making some interesting moves. Interesting times ahead…