You know what open source software is — like Apache, Perl and the granddaddy of OSS, Linux, it’s as free as junk mail and open to any amount of tinkering and modifying. Simply find a download site, hit the button and boom! you’re in business.
People often lump MySQL, an extremely fast SQL database server, in with the aforementioned as free open source software. Yes … but.
The starting point for all things MySQL, www.mysql.com describes it as “basically free.” Whereas none of the other worthies listed above need any qualification, MySQL does. Part of the reason is that the others were developed via the invisible controlling hand of the Internet, where anyone who had an interest could be part of the development team. No one got paid, but anyone could join the fun.
In contrast, MySQL was developed in 1996 in-house at TcX [http://www.tcx.com], a Swedish high-tech firm “because we at TcX needed a SQL server that could handle very large databases an order of magnitude faster than what any database vendor could offer to us,” according to their home page. It’s currently used “in an environment with more than 40 databases” and about “100 gigabytes of mission-critical data.”
It’s like OSS in that you can download the binaries and source code from Web sites, but unlike OSS in that it comes with developer support. Eric Hammond, writing for Netscape’s Enterprise Developer in the “Web Perspective” column earlier this year, reports downloading the Red Hat version, which comes “as a series of RPM files that installed easily and quickly.” Again, unlike conventional OSS, it includes a GUI-based tutorial that walks you through the basics of using MySQL. “Though I had some trouble getting the tutorial to work properly,” Hammond says, “I was able to run its examples from the command line with no difficulty.”
Now, you don’t have to pay for MySQL if you’re just using it internally: “You do not have to pay us if you do not want to,” TcX writes. Bet they wouldn’t mind the customary freeware case of good beer, though. You do have to pay — in money — if you’re selling MySQL directly, such as with a product that includes the MySQL server, or installing and maintaining a MySQL server at some client site. And you’re not allowed to include it in a distribution if you charge for a part of the distribution.
You don’t need the license if you want to include client code in commercial programs, since the client access part of MySQL is in the public domain. Payment is not only appreciated but a good idea, since if TcX gets enough revenue they’ll release added functionality, such as the ability to create fast compressed read-only databases. In other words, it’s like donating 50 bucks to that public radio station you listen to every day: You feel like a creep if you don’t, and it’s a deal for the price. According to Hammond, the license fees start at about $200, and support — critical in a corporate environment — is available starting at $200 per year. However, the MySQL mailing list provides answers to common questions.
What MySQL Is
It’s pronounced “my ess queue ell,” not “my sequel.” The most recent version available at the time of this writing, hence the one we’ll be discussing, is MySQL 3.21.x.
Tech talk: MySQL is a true multi-user, multi-threaded structure query language database server. Whereas SQL is the most popular database language in the world, MySQL is a client/server implementation that consists of a server daemon (mysqld) and many different client programs and libraries. It’s fully multi-threaded using kernel threads, which means it can use multiple CPUs easily. It speaks your language — C, C++, Java, Perl, Python and TCL API, among others.
The raison d’être for MySQL is to have an SQL server of improved speed, robustness and ease of use. TcX’s necessity was the mother of MySQL’s invention, and to build it they used a base of a set of routines “used in a highly demanding production environment for many years.” Like OSS, MySQL is in perpetual development, yet “already offers a rich and highly useful function set,” according to TcX.
A concise history on www.mysql.com relates that TcX wasn’t really looking to develop a new database server: “We started off with the intention to use mSQL to connect to our own fast low-level (ISAM) tables,” it says. However, they soon found that mSQL simply wasn’t fast or flexible enough. So, “this resulted in a new SQL interface to our database, but with almost the same API interface as mSQL. This API was chosen to ease porting of third-party code.” Voila.
The etymology of the name itself is a bit of a mystery, even to its developers. “Our base directory and a large number of our libraries and tools have had the prefix ‘my’ for well over ten years,” it offers. Yet it also discloses that one of the primary developers, Monty, has a daughter named My.
How Stable Is It?
Can you actually depend on MySQL for your project? A testimonial from TcX states that “MySQL has worked without any problems in our projects since mid-1996.” However, they do admit that “when MySQL was released to a wider public, we noticed that there were some pieces of ‘untested code’ that were quickly found by the new users who made queries in a different manner. Each new release has had fewer portability problems than the previous one, even though each has had many new features, and we hope that it will be possible to label one of the next releases ‘stable.'”
A pertinent question is raised on the always-interesting Slashdot (“News For Nerds”) site by a certain “Commander Taco”: “Recently I’ve been familiarizing myself with MySQL for a ‘secret’ project with a roommate, as well as for the Slashdot Rewrite. I’m really amazed at how cool and fast this app is. Can’t wait to see how it holds up to some serious pressure.” A few (lightly edited) answers culled from the responses at http://slashdot.org/articles/00000787.shtml to his comment give a better idea of MySQL in the trenches than almost anything else could:
- “I’ve been using this combo for about six months for a data mart solution. I must say that this thing is fast. I’ll go as far as to say that the Pentium 166 that it runs on is sometimes faster than DB2 on our AS400 610e!”
- “If only it had such things as rollback and commit, my company (a payroll company) would consider using it. Unfortunately, it does not and the authors have expressed no interest in adding that functionality. Until then, we can’t use it for such important things as paying the cast and crew of Titanic (which we did the payroll for).”
- “I’ve been using MySQL as a database to receive radius accounting records — that is over 2,500 logins a day. Most logins involve a minimum of four queries … so that is over 10,000 queries a day. It has not crashed at all, and it’s running on a 486dx4-100 VLB! I am very satisfied with MySQL. The only ‘downside’ is no transaction support — but this is why it is so fast.”
- “I used MySQL on a project during the second half of last year and found, although fast, it was totally lacking in basic functionality (referential integrity anyone?) not to mention its ‘curious’ implementation of SQL. Much as I hate to bag a (semi) free application, I’d think long and hard before ever using it again.”
- “I know of a few ISPs running MySQL on various unices for Radius accounting. It is growing, but it works good right now. The perl API is a little quirky but all-in-all a fast, stable product.”
- “PHP + MySQL are a nice team to build Web sites and MySQL gets the job done most of the time. Slows down when it has to do multiple joins on large tables, but very nice BLOB support.”
- “MySQL is a lean, mean, stripped-down SQL machine. It’s blazing fast, but missing some functionality. [It’s like] a Chevy Vega with a 454 under the hood and no back seat.”
There you have it, with all the gonzo only the Internet can provide. TcX explains that “each release of MySQL has been usable, and there have been problems only when users start to use code from ‘the gray zones.'” In Tom Wolfean language, then, you get problems when you try to push the outside of the envelope.
All data for the server is stored using an ISAM table handler, and “in all MySQL releases there hasn’t been a single (reported) bug in this code,” TcX says. As a matter of fact, “the only known way to get a corrupted table is to kill the server in the middle of an update.” But even that is unlikely to destroy any data beyond rescue, since all data are flushed to disk between each query.
“All known and reported bugs are fixed in the latest version,” TcX assures us, “with the exception of the bugs listed in the BUGS file which are design related.” To get around Y2K problems MySQL uses Unix time functions, and has no problems with dates until 2069: “All 2-digit years are regarded to be in the range 1970 to 2069, which means that if you store 01 in a year column, MySQL treats it as 2001.” Hey, the grandchildren of today’s Y2K programmers will need to eat, too.
