Tips: Hacking MySQL Server

Hacking the latest storage engine? Working on a fancy new SHOW command? Hunting for a bug in mysqld? This is for you!

Once you’ve got MySQL compiled, how do you test your mysqld binary? You’ll need to get it started. Stop! Don’t make install it!

  1. Set up your sandbox 
    $ mkdir ~/t
$ vi ~/t/my.cnf

    Add the following to ~/t/my.cnf (modify to taste):

    [mysqld]
basedir=.
datadir=/Users/jcole/t 
port=3307
socket=/Users/jcole/t/mysql.sock
skip-grant
skip-innodb
skip-bdb

[client]
port=3307
socket=/Users/jcole/t/mysql.sock
  2. Build your source
  3. Set up a symlink for share/mysql pointing to the share directory. 
    $ mkdir share && ln -s ../sql/share share/mysql

    This, combined with the basedir=. in my.cnf means that MySQL will be able to find its error message and character set files, so that the release you’re building doesn’t have to match the release installed on the system.

  4. Start mysqld 
    $ sql/mysqld --defaults-file=~/t/my.cnf
  5. Start a mysql client (in another window) 
    $ client/mysql --defaults-file=~/t/my.cnf
  6. Shutdown the mysqld (if it didn’t crash :) ) 
    $ client/mysqladmin --defaults-file=~/t/my.cnf shutdown

That’s it! Much easier, safer, and faster than using make install, and you don’t need to remember all the options to specify to start and test your newly built mysqld! If you manage to break your sandbox, just delete everything except my.cnf and you are back to a clean slate.

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.

It’s a french word, or something…

The scene: My mother and I are at a BBQ place outside of Houston, Texas. A group of four people walk in: a woman about 35 years old, a young boy about 10 years old, and two teenage girls, perhaps 16 years old. They order their food and sit down behind us, waiting for it.

Someone says something about a threesome, I perk up. Some more (joking) babbling about a love triangle, between the boy and the two teenage girls.

Woman: It’s a menage-a-trois.
Girl 1: A what?
Woman: [Scoffing, surprised that the two girls don’t know the phrase.] A menage-a-trois, it means threesome. It’s like a french word, or something…
Girl 1 & 2: Oh…

Strange conversation to have in a BBQ place.

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.