Category Archives: Technology

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. :(

Helping fellow man… Kiva

A few months ago, I found out about a non-profit called Kiva that allows basically random strangers via the Internet to lend money to businesses and individuals in third-world countries. Kiva relies on donations to cover their administrative costs, so every dollar you give them goes to the business you are funding. They do not pay interest, so there are no capital gains to be had. When I found Kiva, they had just gotten quite a bit of press, so they had no more businesses in need of funding available on their site. I signed up for their newsletter and updates and have been checking back with them fairly often since then.

In the past month, they have signed up with a few new partners in new countries in order to increase their reach. This morning, they sent out an email announcing that they had listed a number of new businesses on their site… and luckily I got to it pretty quickly. They raised an incredible amount of money in only a few hours, and now they have no more businesses in need once again.

I managed to get in before all the businesses were funded, so I’ve now funded $150 of a $500 loan to Shawqat Al Kahwaji, a Palestinian electrician in Gaza. I’m excited to read about his progress over the coming months. Good luck, Shawqat!

Kiva rocks. You can make a difference in the world, you can help a fellow man get his business and his life off the ground and feed his family and help his community.

If you’re interested in being a part of this awesome new idea, go read about the existing businesses, sign up, and wait for new businesses to be listed (and act quickly once they are!).

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…

Toyota Camry Hybrid: Political statement or a commercial?

I didn’t watch the Super Bowl XL, this year… in fact I never watch it, as I couldn’t care less. I usually catch the commercials on the internet afterwards, since it’s not worth suffering through hours of football just for some funny commercials.

This year there was a commercial for the Toyota Camry Hybrid, which featured a hispanic man and small boy. Their conversation went like this:

Boy: Papa, why do we have a hybrid?
Man: For your future.
Boy: Why?
Man: It’s better for the air, and we spend less because it runs on gas and electrical power.
Man: Mire—mire aquí. Man points to console display, which shows the engine, battery, and electric motor status.
Man: It uses both.
Boy: Like you with English and Spanish!
Man: Si!
Boy: So why did you learn English?
Man: For your future!

What the hell? The subtext of the whole commercial is: immigrants need to learn English. I won’t argue against that point—that battle has been played out many times by many people. Instead I’ll just hit the point I care about: Why did they include this subtext in a car commercial. It would’ve been a fine public service announcement if not sponsored by Toyota. Instead, it just seems in poor taste, at least to me.

Now with AdSense…

You might have noticed that there are Google AdSense ads scattered accross the jcole.us as of last night. This is an experiment to see how having the ads affects traffic, and if I actually make any money on it. :)

I tried to place the ads in unobtrusive places, for now. Maybe next month I’ll try more obtrusive placement and see if that makes a difference. Let me know what you think. :)