Percona Offers InnoDB Replacement

Open source the way it ought to be. Today, Percona announced a replacement for InnoDB that improves performance and fixes bugs. The new engine is called XtraDB.

According to Vadim at Percona:

It's 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

The release is pure GPL (v2) and commercial support is available from Percona. If percona keeps this up, they just might become the new MySQL.

The source is available from Launchpad and from Percona. Binaries are also available and OurDelta will start using XtraDB in future builds. Percona expects a 6 month development cycle. I don't see on here how they plan to incorporate contributions but that may just need a little time to figure out. They do say that they have already incorporated most patches that are available and that make sense for their customer base..

This announcement excites me much more than Drizzle ever did.

LewisC

Technorati : database, mysql, open source, osdb

SQL Newbie Book

I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount. I have several more books like this (DDL, Intro to Relational Databases and Cloud Computing) under construction. I also plan to do some intermediate and advanced books in the future.

LewisC

Technorati : database, learning, mysql, sql

Last Week For Database Survey

This is the last week to participate in a database usage survey. If you haven't already done so, please take a few minutes to answer 25 questions.

LewisC

Technorati : database, mysql, survey

Over 200 Responses in Less than 2 Weeks

Less than two weeks ago, I posted my Database Survey. As of just a few minutes ago, I have had 215 responses. That's pretty awesome. I'd like to get at least twice that though.

I haven't looked deeply at it yet to see if there are any trends. I think it will be best to wait until the survey is closed. I did look at some of the responses, kind of as a quality check. Looks like MySQL is fairly well represented. I didn't see any DB2 responses (for primary database). I did see plenty of Oracle and a few Postgres.

I will leave it up for another 2 1/2 weeks (for a total of 4 weeks). If you haven't taken it yet, please do so if you get a few minutes. It only takes 5-10 minutes as there is only 25 questions.

Also, if you have a blog, post on forums (without spamming), or have any other ways to spread the word, I would appreciate it.

Thanks,

LewisC

Technorati : commercial, database, open source, survey

Infoworld Picks MySQL as Best Database

Infoworld published the 2008 Bossies, Best Of Open Source Software. There are 8 categories and none of them are database:

• Collaboration
• Developer tools
• Enterprise applications
• Networking
• Platforms and middleware
• Productivity applications
• Security
• Storage

I had to look through several of them before I found the database category under Platforms and middleware. Slide 4 is the magic slide:

It says:

Database

While SQLite3 is extremely convenient for development and testing databases, and PostgreSQL has powerful Generalized Search Tree indexes and is very close to being enterprise-ready, MySQL is the choice for many Web sites thanks to its excellent read performance, transparent support for large text and binary objects, and incredibly easy administration. Stored procedures, functions, triggers, and updateable views were added to MySQL in version 5, overcoming the largest technical objections to its deployment at many sites. MySQL also has a large, helpful user base, and some poster-child deployments including eBay, Yahoo, and Craigslist.

I'm not sure why SQLLite would even be on the list. There are plenty of other OSDBs that I would put my bets on before SQLLite. Not that SQLLite is bad, it's just not a "best of" kind of thing. I don't imagine the Postgres folks are too happy at the "also ran" placement. "Close to being enterprise-ready", ouch.

I have to agree that MySQL has a large, helpful user base. I actually think that is one of the best things about MySQL.

LewisC

Technorati : database, mysql, open source

High Performance MySQL: Review

High Performance MySQL, Second Edition
Optimization, Backups, Replication, and More

By Baron Schwartz , Peter Zaitsev , Vadim Tkachenko , Jeremy Zawodny , Arjen Lentz , Derek J. Balling
Second Edition June 2008
Pages: 708
ISBN 10: 0-596-10171-6 | ISBN 13: 9780596101718

When I first read about this book, I figured many sections would be over my head. I was pleasantly surprised when I started reading it. In the Preface, the authors say (and I partially paraphrase for brevity):

"We wanted a book that wasn't just a SQL primer. We wanted a book with a title that didn't start or end in some arbitrary time frame and didn't talk down to the reader. Most of all, we wanted a book that would help you take your skills to the next level and build fast, reliable systems with MySQL."

"We decided to write a book that focused not just on the needs of the MySQL application developer but also on the rigorous demands of the MySQL administrator, who needs to keep the system up and running no matter what the programmers or users may throw at the server."

They are trying to write the "mythical, perfect book". That is a tall order. In many ways though, the authors accomplish what they set out to do. They may have accomplished even more than they intended to. While there is plenty of high performance here, the book goes a bit further than that. I'm not complaining but the title may put off some users who could really benefit from this book.

This is not the book if you are trying to learn about databases in general. The book assumes that you have at least some hands on experience in your background and some familiarity with MySQL. As the authors say, again in the Preface, "We assume you are already relatively experienced with MySQL and, ideally, have read an introductory book on it".

With that in mind, I'll begin the review.

Chapter 1: MySQL Architecture

Chapter 1 is an overview of the MySQL architecture. The chapter doesn't get very deep into MySQL internals (that's not the books focus) but this chapter provides an excellent fast track understanding of how MySQL works at a fairly detailed level. This chapter covers locking, transactions and the storage engine concept as well as details about each individual storage engine.

There is some explanatory content (i.e. what is a lock, what is ACID, what is a deadlock, etc) but most of the content concentrates on MySQL specifically. In a couple of places, the explanatory content and MySQL specifics were not in the same part of the text. For example, on page 8, database isolation levels are defined but it's not until page 11 in a section on autocommit that I finally read, "MySQL recognizes all four ANSI standard isolation levels, and InnoDB supports all of them....." There are a couple of other places where the specifics are oddly separate from the MySQL details. It's a minor nit though.

A particular eye opener for me was the discussion on MVCC in MySQL. If you ask most Oracle people (who are not MySQL also), they will almost all say that MySQL does not do MVCC. The book provides a nicely detailed example of how MVCC works in InnoDB.

The chapter ends with a discussion on storage engines. The book gives a paragraph or two about each available engine (including Maria and Falcon) and a table summarizing the differences between engines. I wonder if anyone is working on a columnar store for MySQL?

Chapter 2: Benchmarking and Profiling

The beginning of chapter 2 can apply to any database (and really most any application). It helps define what to benchmark, how to benchmark, how not to benchmark, etc. The benchmarking section ends with a list of useful tools for benchmarking an application and with a set of MySQL benchmarking utilities. There are examples using http_load, MySQL's Benchmark(), dbt2 and the MySQL benchmark suite of perl scripts. The examples are a mini how-to and results explanation all in one.

The second half of the chapter details profiling. There is some generic profiling discussion but most of the text covers MySQL specifics. I don't want to show my lack of knowledge, but I had never even heard of the "slow log" until I read this chapter. The authors recommend enabling it but to a DBA it has a very scary name. ;-)

This chapter explains what you should be looking for when profiling. This isn't really any different than profiling an Oracle or Postgres database. You want to start with the low hanging fruit and work your way up the tree. The chapter ends with some examples of profiling and a little bit of discussion about profiling when you can't change your database.

Chapter 3: Schema Optimization and Indexing

Having a correctly designed schema is important for any database and MySQL is no exception. This chapter concentrates on what that means for MySQL specifically. An example I wasn't aware of is how NULLable columns in MySQL can impact the database. I wouldn't have guessed that a nullable column would use more space than a NOT NULL column.

A large portion of the chapter is dedicated to the various MySQL data types, and considerations for each, followed by the various types of indexes allowed by the storage engines. It even includes a way to build your own hash index if your particular storage engine doesn't support them.

I didn't realize that MySQL supports "covering indexes". A covering index is called a "fast, full index scan" in Oracle. Basically, all of the data to satisfy a query exists in an index so a table read is never required. This can save a tremendous amount of IO and increase performance. This is a fairly sophisticated optimization that is not supported by many "advanced" databases.

This is a large chapter and includes a huge amount of useful information. Pros and cons of normalization, an indexing case study, summary tables and more. This should be mandatory reading for anyone who is designing real world database schemas in MySQL.

Chapter 4: Query Performance Optimization

How do I tune a query? The age old question asked by developers around the world. There are some general answers to this question but each database has its own quirks and considerations. This chapter addresses those issues for MySQL.

You get the usual: don't fetch more rows than needed, reduce IO and don't use "SELECT *". You get a lot more than that, though. in "Ways to restructure queries" you read about "chopping up a query". In that section, the authors recommend, in certain scenarios, using procedural code to chunk out operations. And in "Join Decomposition", the authors recommend, again in certain scenarios, to reduce a query with joins to its component parts and merge the data in the application.

They give the reasons why to do this with details on how it impacts the internals (like caching). If you read Oracle optimization books, you will get exactly the opposite advice. This is the reason it is important for designers and developers to not assume that every database works the same and follows the same rules. To take advantage of a database, you need to understand the database.

This is another chapter that is required reading for anyone designing MySQL databases. The coverage of the limitations in the MySQL optimizer is worth the cost of the book. This chapter also covers optimizer hints and user defined variables. The user defined variables might not be something you would consider when tuning but maybe they should be.

Chapter 5: Advanced MySQL Features

Chapter 5 is a mix of "other" stuff. A bit of this, a bit of that. It covers the query cache, stored code, prepared statements, updateable views (and limitations of), character sets and conversions, recent full text advances and distributed transactions. There is a really good section on merge tables (which I haven't used) and partitions (which I have used).

A new type of stored code n MySQL 5.1 is an event. An event is kind of like a DBMS_JOB in Oracle. You can schedule an event to run at a certain time or on a certain frequency. Like a DBMS_JOB, you can't send in variables or return results (well you can fudge those pragmatically, of course). Also like a DBMS_JOB, errors show up in the log file.

Chapter 6: Optimizing Server Settings

Chapter 6 gives you coverage of many (all? - most?) of the server settings. It goes beyond that though. It also gives you an understanding of what the setting does as well as when and how to use them.

Chapter 7: OS and Hardware Optimization

Hardware, the bane of most database developers. I know that I prefer to spend my time within the database not in the OS. This chapter explains what, and why, hardware to buy. How to select a CPU(s), memory and disk. It even covers the various flavors of RAID. Closing out the hardware section is a discussion of SAN, NAS and network configuration.

The OS portion of this chapter deals more with configuring the OS rather than choosing the OS. It starts with a little bit of info about the various OSes that run MySQL and which file systems you might want to choose. The rest after that is configuration.

Chapter 8: Replication

Replication is a favorite topic of mine. Most of my replication experience has been with Oracle and a little bit with Postgres. I've not had to replicate MySQL but this chapter gives me a good starting place should I need to. This chapter gives a quick overview of replication and then dives into MySQL specifics.

The nice thing about MySQL replication is that it is integrated with the server and has been for a long time. That means it's pretty stable and mature. This chapter gives a step by step guide to setting replication up and running with it. Because the process is so mature, it's really not that hard.

This chapter also digs into some of the inner details of how replication in MySQL works and some of the various configurations (master-slave, master-multi-slave, master-master, etc). I like that it also covers common problems with replication and the problem solutions. That's very handy to have on hand.

Chapter 9: Scaling and High Availability

Chapter 9 is another chapter that should be mandatory but this time for anyone working on high volume MySQL implementations. It starts with terminology to ensure that everyone is on the same page. After that we get into the goodies.

There is a discussion of data sharding. This is splitting data across different nodes in a cluster. This is very different than scaling in Oracle. If you work with Oracle and MySQL, some of these rules are exact opposites of each other. The book spends quite a bit of time on this topic and that's good because it is counter intuitive to me.

The rest of the chapter covers clustering, load balancing and high availability concepts. This is a good chapter that is pretty deep. I will have to refer back to it in the future.

Chapter 10: Application-Level Optimization

Chapter 10 is a fairly short chapter that discusses some common application issues and possible fixes. It includes a discussion of caching.

Chapter 11: Backup and Recovery

Backup and recovery is arguably the most important task for a DBA. It's also just about the most boring thing to read about. Chapter 11 covers why it's important, when to do it and how to do it. An added wrinkle in the MySQL backup and recovery scenario are the various storage engines and their impact on any particular backup methodology.

Chapter 12: Security

Chapter 12 outlines basic security in MySQL: accounts, privileges, and grant tables. It moves on to how to grant privileges and how MySQL checks them at runtime. It also covers common problems and solutions. It gets into OS, network and application level security and encryption. I'm not sure how important this topic is in a book called High Performance MySQL but it is handy in a MySQL Complete Reference.

Chapter 13: MySQL Server Status

MySQL includes a server command, SHOW STATUS, that can give plenty of information about the status of the server. This chapter walks you through various sections of the command results and what they mean. The authors give you clues about what to look for to interpret the results.

Chapter 14: Tools for High Performance

This chapter should probably be called "Tools Everyone Needs." These aren't so much performance tools as they are tools for everyday usage. Included are the MySQL Visual Tools, SQLyog, phpMyAdmin, Maatkit, innotop and more.

Appendices

There are three appendices: Transferring Large Files, Using Explain Plan and Using Sphinx (Full-Text Search) with MySQL.

My Summary

This is a good book that is well worth the cost. While it is not a newbie book, there is plenty here for novice and expert alike. I can pretty much guarantee that if you work with, or want to work with, MySQL, you will get some value from it.

I found some sections much more valuable than others. That's not unexpected. I also found the information to be at just the right level of detail. I have been working with databases for a long time though, and off and on with MySQL for a while. I think for someone newer it would still be the right amount. In the sections where there might be confusion, there is usually a discussion of terminology. For a MySQL guru, it might be a bit too explanatory and not detailed enough. I just have to say that this book is targeted more toward a novice to intermediate level rather than complete newbies or experts.

I do have a nit to pick about the title though. While the majority of the book does focus on performance, I think the title is misleading. I don't mean that in a bad way as you get more than you might expect from a book with this title. If it was named more like "MySQL Performance and Usage" or "The MySQL Reference including Performance" it might get a larger audience.

If you buy this book along with MySQL in Nutshell and MySQL Cookbook, I don't think you would need another MySQL book in your library.

I enjoyed reading this book, it is well written and, for the most part, flows logically from one topic to another. I didn't concentrate on any typos or oopsies as there is an updated version on the way with most of those already fixed. I didn't find many anyway. I can pretty much guarantee that I will refer back to this book in the future.

LewisC

Technorati : book, mysql, performance, review

LinkedIn Buys Into MySQL

Hot on the heels of news that SquareSpace is using Oracle, comes news that LinkedIn is going whole hog with MySQL.

Actually, you could say that LinkedIn is buying into Sun. They are buying the MySQL Enterprise subscription and they'll be running MySQL on Sparc servers and Solaris 10. They've signed up for Sun Professional Services, MySQL Professional Services, and Solaris Everywhere. I guess you could say that signed up for the full monty. ;-) Pun intended.

"Helping LinkedIn to scale their Web systems demonstrates the strength of combining the Sun and MySQL teams," said Zack Urlocker, vice-president of products, database group, Sun Microsystems. "Our focus is on delivering customers innovative solutions in a straight-forward, cost-effective way -- based on open source software and other high-performance, reliable platforms."

This looks like Sun's sweet spot. Hardware, Solaris, MySQL and professional services. I'd love to know what the price tag on this deal. This is really the kind of deal we need to hear more of if Sun (and MySQL) want to stay significant in the future.

I use LinkedIn as my primary professional social network. I never really considered what it was running under the covers but from the press release, it looks like they are long time MySQL users. Having them buy the enterprise subscription is a big win for Sun.

On the downside, I still think the enterprise subscription is too cheap. It's almost like giving the software away. ;-)

LewisC

Technorati : linkedin, mysql, scalability, social network

OSCON 2008 Popularity Contest

I didn't get a chance to go to OSCON 2008. Bummer. But I can live vicariously through google. So, along with all of the announcements you've heard from OSCON, I know present the OSCON 2008 - Google popularity contest. This is a completely unscientific survey of google hits. I was searching blogs and news. I started with just news but the blogs hits really upped the numbers.

To run these searches, I use "oscon 2008" and the search term, for example:

"oscon 2008" mysql

In the case of open source, I also quoted "open source".

I'm using google's about number. I didn't sit and count each hit. ;-)

Category	Term	Hits
General	open source	28600
	cloud	4220
Database	database	9680
	mysql	10300
	postgres	2560
	drizzle	521
	hadoop	277
	firebird	809
	derby	1680
	ingres	3190
	luciddb	37
	couchdb	298
Vendor	sun	9390
	oracle	6480
	microsoft	15100
	apple	10500
	intel	45800
Language	ruby	5730
	php	12400
	java	43200
	perl	8280
	python	5230
	mono	842
Linux	ubuntu	10700
	fedora	5120
	debian	5040
	bsd	4870
	centos	2520
	gentoo	16900
	red hat	5860
	suse	3220

Interesting results. MySQL took the database by a good margin. I thought Ubuntu would take the Linux flavor but Gentoo got it. I also didn't expect Java to be #1 much less by such a huge margin.

LewisC

Technorati : comparison, google, open source, socon

Is Drizzle good for MySQL?

Have you heard of Drizzle? It was announced at OSCON yesterday and is all over the blogosphere. From the Drizzle FAQ:

* So what are the differences between is and MySQL?

No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types. Less engines, less code. Assume the primary engine is transactional.

Also from the FAQ is that, right now at least, there is no intention to make this run natively on windows and they make the point:

* "This is not a SQL compliant relational..."

Very true, and we do not aim to be that.

It is a fork of MySQL that takes it backward to pre-5.0 in features but hopefully greatly reduces the bugs and instabilities. I plan to look at it but I don't see much enterprise adoption. It was the enterprise users who wanted stored procedures, views and most of the other stuff that is being removed. I think it will be adopted mainly by read-only (or mostly) web sites that want to serve many pages. That's ok. There's a huge market for that. If I found a fit for a client, I would consider it.

They are very honest with the goals:

* What is the target?

Deliver a microkernel that we can use to build a database that meets the needs of a web/cloud infrastructure. To this end we are exploring http interfaces, sharding enhancements, etc... do not expect an Oracle, MySQL, Postgres, or DB2.

I was reading my usual stable of blogs this morning and ran across two blog entries that got me thinking. First is Ronald Bradford's blog, The new kid on the block - Drizzle. Ronald gives a great overview of what Drizzle is trying to achieve, the current state of MySQL and reasons why Drizzle is a good idea. This is well worth a read. I'm just this far from being convinced. ;-)

The second was On MySQL Forks and MySQL's non-open source documentation on Jeremy Cole's blog. To answer his question, I did not know that the MySQL documentation was not open source. That is a very interesting point and one that never even occurred to me. I wonder if Sun would consider opening it?

Anyway, both of those got me thinking about forking MySQL. If you look at the Postgres community (not comparing the databases, just the goals of the community) over the years, they have taken great pains to not fork the database. The contrib modules are there so that the database can add functionality without forking. The companies who have tried to make a go at monetizing Postgres have also taken great pains to not fork the database.

In fairness to Drizzle, it may have a sort of contrib module like functionality (bolding is mine):

* What is the goal?

A micro-kernel that we then extend to add what we need (all additions come through interfaces that can be compiled/loaded in as needed). The target for the project is web infrastructure backend and cloud components.

With Postgres, even companies like Yahoo and Skype who needed specific functionality have contributed that back to the community so that the code will eventually be reworked into the mainline (or as a contrib). There is a very anti-forking bias in the Postgres community and I think that has helped advance the database.

Ignoring any commercial interests, will the MySQL community become fragmented by forking? Because honestly, the community is the important part of any open source project. If Sun is putting its resources behind MySQL 5.1 and 6, and if they don't open the documentation, where will Drizzle go?

I'm just not convinced that Drizzle is a good thing or that it's needed. Then again, if they concentrate on making this a natively scalable database for the cloud, maybe it is time for a fork. Although, I'm not sure that goal is achievable starting with the MySQL codebase. What do you think?

LewisC

Technorati : competition, fork, fragmentation, mysql

Results of EnterpriseDB Open Source Database Survey

EnterpriseDB announced the results of the survey they did a few months ago at OSCON. Now, take the results with a grain of salt as it was done by EnterpriseDB. EnterpriseDB is based on Postgres so there is a vested interest in making Postgres sound good. Results can be skewed depending on how the survey is worded, what options are available as answers and who the respondents are.

The results summary is available for free.

Some key facts:

500 respondents. The download page says "500 corporate IT leaders". Or maybe, 500 open source developers. ;-)

Only 9% of respondents indicated that they preferred commercial solutions over open source solutions. I would guess that a majority of those responding were open source database people anyway. This is also one place where I think the wording of survey questions makes a difference. I'd like to see the survey again and compare the results to the survey itself.

The survey shows that respondents are using open source to migrate away from Oracle and SQL Server. It says that less than 1% is using open source to migrate away from DB2. Since DB2 is a major investor in EnterpriseDB, that doesn't surprise me. Again, the target users of the survey make a difference as well as the questions themselves.

Of course, Postgres was chosen more than any other open source database for transactional applications and high reliability. Again, not surprising based on who wrote the survey and what they sell.

Before I put very much value on this survey, I would want to see more than just a hand-crafted summary of the results. A spreadsheet of all the questions and the answers chosen would be, at least somewhat, valuable. Without that though, it's just marketing. I can't find anything on the site indicating the full results will be made available.

Del.icio.us : database, enterprisedb, mysql, open source, oscon, postgres, survey

MySQL vs Postgres, Again - Is Postgres Better?

I was browsing the web on this lazy Sunday afternoon and ran across a good article on the Rarest Words blog. The author was trying to get Django installed and running with Postgres. From the author's own admissions, he is not a Postgres fanatic.

Well, this and last year I hear everywhere that PostgreSQL is the way to go and that usage of mySQL in 2008 makes people puke… But without any real arguments (besides "Postgres is the way to go").

After some not so compatible errors with these not so compatible databases, the author did get it working and ran some benchmarks. Postgres did not turn out faster than MySQL. If you ask anyone in the Postgres community which database is faster, they will say Postgres. Ask anyone in the MySQL community and there's no telling what answer you'll get. ;-)

I have now worked with quite a few different databases. Over the last decade most of my time has been spent with Oracle but I have also spent some time with MySQL and Postgres. So, I have to tell you, Oracle is faster. ;-) Just kidding.

What I have found is that any claim that one database is the best database is just kind of silly. Every database has a different feature set, a different set of strengths and a different set of weaknesses. Comparisons are good so that people know where a database is best used but it's pointless for claims of "winners and losers".

It's sort of like benchmarks. If you benchmark two databases, the loser fan base will always claim that you didn't tune correctly. Or the benchmark was invalid. Or the wrong engine was used. Or whatever. They may be true but even so, with all things being equal, that does not invalidate the benchmark. Under those conditions, one or the other is faster. Is that significant? Probably not.

So, which is better for Django? I haven't a clue. I don't know much about Django. I'd say the best database is the one you are most comfortable using and have the most experience with.

When switching from one database to another, I think most people have pretty much the same experience as the blog author had. It can be really painful at first. I like it. Not the pain so much but digging in to it. I like understanding the differences between one database and another. But then, I'm sort of weird that way. ;-)

For business reasons, if a database is working fine for your needs, don't switch. Most people don't need the absolute performance that a $250/hour brain surgeon DBA might be able to give you. And if you do, you're probably already running Oracle. ;-)

LewisC

Technorati : compare, database, django, mysql, oracle, postgres

Free Database Design Tools

LewisC's An Expert's Guide To Oracle Technology

Sun just announced MySQL Workbench, a new database design tool for MySQL developers and DBAs. I'm a data modeling tool junkie. I like to play with any I can get my hands on. I've used almost every modeling tool that's been built. My all time favorite is probably Erwin.

I decided to download MySQL Workbench and give it a try. Since I was playing with it, I figured I should write about it and while I am writing about it, I might as well write about a couple of other tools, that I have personally used, that you might like.

TOAD Data Modeler

The TOAD Data Modeler from Quest used to have a free version. I can no longer find a link to a free version but you can download a free copy of the latest Beta version. It's unfortunate that Quest has decided not to continue the free version, though. Like most Quest tools, the price tag for Data Modeler is high, $479.00 per seat. That's a bit more than I want to pay.

It is a good looking tool though. If I am mistaken and you can find the free version, it worth checking out.

It has everything you would expect in a data model tool. It also has excellent support for most databases. It supports the commercial databases (SQL Server, Oracle, Sybase and DB2). It also supports Postgres 8.1 and 8.2 (no 8.3 so no XML type) and MySQL 5.

It can be a bit kludgey to use at times and at $479.00 I don't have any reason to recommend it over my next design tool

fabForce DBDesigner 4

DBDesigner is a MySQL database design tool that just happens to provide some support for other databases. It supports SQL Server, Oracle, SQL Lite and ODBC.

There are a few features I really like in DBDesigner4.

• It's free. I like free.
• If you are using a database not directly supported, you can create your own data type. Right click in the data type window and select Create New Datatype.
• Reverse Engineering. That means you can connect to a database and it will import your model. That's rare in a free tool.

I have used this tool for a while now and have designed a couple of different application schemas using it.

My only complaint would be that the interface sometimes seems non-intuitive/non-standard. I find myself looking around trying to find the button or menu option that I know is there, just not where I expect it to be. That's a nit though. I definitely recommend this tool to anyone looking for design tool. I would choose Erwin of DBDesigner but only if someone else was paying for it.

MySQL Workbench

And now on to MySQL Workbench. I have to say that I have only spent a short amount of time playing with it but I like it. Sun is offering a community edition and a standard edition. The community edition is feature limited and free, standard has additional features and costs $99.00. You can read about the differences.

As I used it, I kept thinking to myself how much like DBDesigner it is. I don't know if they licensed DBDesigner code or if it's just coincidence. The interface does have a different look and feel but there is something about it that just makes me think DBDesigner.

Compare the DBDesigner screen shot to the MySQL Workbench screen shot. They're eerily similar.

Still, like most MySQL tools, it's easy to install and use. Even if it is based on DBDesigner, it is restricted to being just a MySQL tool. I couldn't find anyway to connect to any database though. That might be a limitation between standard and community.

NOTE: I just found a Workbench FAQ entry about DBDEsigner 4:

Q.2: Is the MySQL Workbench based on the code of DBDesigner4?

No, MySQL Workbench is a complete rewrite in C++ / C# / Objective-C. Not a single line of code is shared between the projects. But Workbench does build on the experience and feedback got from the DBDesigner4 project and should be better than its predecessor in every respect once GA quality is reached.

If you're MySQL only, Workbench could be a good choice. DBDesigner does offer reverse engineering and documentation for free though, in addition to supporting multiple databases. I think I will stick with DBDesigner4 for now (when my employer/client doesn't provide their own modeling tool).

LewisC

Technorati : data, data model, database, design, model, mysql, oracle, postgres, tool

My New DB GUI

I've been playing with various GUIs off and on over the last couple of months. I find that I drop to mysql.exe quite often no matter which tool I use. My favorites until recently have been the MySQL GUI Tools and NaviCat.

I was using the Lite edition of Navicat. I actually started using navicat with postgres a few years ago. I like it but the lite version is limited in some annoying ways. It's nice in that it runs (at least the mysql version) in Linux and windows. I don't use a mac so that doesn't really do anything for me.

Of course the MySQL gui tools run just about anywhere.

I just remembered that Quest Software has a mysql tool called TOAD for MySQL. I have been a user of TOAD for Oracle for well over a decade. I started using it before Quest owned it.

TOAD started at the Tool for Oracle Application Developers. So calling it Tool for Oracle Application Developers for MySQL sounds kind of stupid. ;-)

TOAD is still the most advanced gui tool for Oracle. I have started using Oracle's SQL Developer and it's a nice tool but TOAD still has it beat on a feature and performance basis right now.

Anyway, my point with all of this is that TOAD for MySQL is now my preferred tool. It is completely free although it runs only on windows. It has the most advanced feature set of any of the database tools I've been using recently.

In addition to MySQL and Oracle, it also supports MS SQL Server, DB2 and Sybase. If they came out with a Postgres version it would be a complete set.

The query builder and erd tool are highly functional. When I am new to an application, the first thing I look for is an ERD. Most MySQL applications do not have one available (at least in my experience). So, I start off by building one.

The schema browser also blows away the competition. Grid view and edit with out the pain of separate pop up windows. very nice. You can also see all of the databse objects.

My suggestion, when you install, choose the TOAD for Oracle view. If you prefer an explorer view, choose the SQL Navigator view.

Give it a try. You can read my initial review (kind of old now though).

LewisC

MySQL vs Postgres Wiki

There is a new wiki comparing MySQL to PostgreSQL. Because it's a wiki, hopefully it can be kept updated so that it's current AND accurate. The wiki is MySQL vs PostgreSQL. Personally, I'd like to see this grow into a universal comparison site that the community could keep updated. LewisC

Sun Buys MySQL for $1 Billion!

Holy simoleans, Batman. I wake up this morning, write a nice little Oracle tip and then next thing I see is Josh's post about Sun->MySQL. That was totally unexpected to me. I browsed over to PlanetMySQL to get the scoop and what do I see, Sun buys MySQL for $1 billion to take centerstage in the web economy. I don't often agree with Matt but on this topic, I think I have to. This just makes a lot of sense for everyone involved. Sun, MySQL and MySQL users. The first post I saw that shows concern about Sun's Postgres support is this one from the 451 Group, Sun acquiring MySQL for $1bn. That post led me to Jonathan Schwartz's blog. I love his post title, Helping Dolphins Fly. Jonathan always sounds pumped but this blog entry makes him sound ecstatic. I have not been impressed with many Sun decisions over the last couple of years but I have to admit, this is a big one. I think it's a good direction. If you want to read the press release and get some of the details, you can do so at MySQL AB. Lot's of changes this year, I think. LewisC

MySQL Adds XML and XPath Support

I was browsing around the web and ran across this article at xml.com, XML Moves to mySQL. Being a heavy XML user, I had to read the article. It looks like MySQL is expanding the built-in support for XML.

This article isn't very detailed but it links to Using XML in MySQL 5.1 and 6.0 at mySQL.com which is very detailed.

I like the ability that is built in to support loading XML from files. That's a feature I wish Oracle would work on. Even in 11g, that functionality is still limited.

MySQL also adds ExtractValue() and UpdateXML() support. If you're manipulating XML much, you know that these two functions are needed.

From what I know of MySQL and what I read here, MySQL is just getting started with XML support. This article points out some baby steps on the road to XML maturity. You have to start somewhere and I am glad MySQL is adding this.

LewisC

Google Contributes to MySQL

According to this article in ComputerWorld, MySQL to get injection of Google code. Google has signed an agreement to contribute to MySQL (oddly enough called a contributer license agreement) and will contribute source code for a variety of technical items. In a way, this can be considered a very strong endorsement of MySQL by Google. Google even has an engineer dedicated to working with MySQL and the MySQL development team. I didn't realize that Google was such a heavy user of MySQL. Google will contribute some code related to replication and monitoring. Also noted in the article is that MySQL, in the future of course, will support role based security and even Transparent Data Encryption (TDE). TDE is a big selling point for financial companies and other companies heavily regulated by privacy concerns. TDE puts the burden of encryption on the database instead of on the developer. Oracle has had it since 10g. Some good info in the article. Check it out! LewisC

Hiding SQL in a Stored Procedure

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I found.

My create table command and inserts ran unchanged. I did run into an issue with the timestamp though.

mysql> create table test_data (

-> name text,

-> address text,

-> create_date timestamp );

Query OK, 0 rows affected (0.09 sec)



mysql> insert into test_data values (

-> 'lewis',

-> '123 abc st',

-> timestamp '2001-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)



mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2091-01-01 10:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 0000-00-00 00:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Notice the timestamp in the "george" record is all 0s. I figure that's a configurable issue but I don't really care to research it at this moment so I'll just delete it and use a timestamp that's a little closer to NOW.

mysql> delete from test_data where name = 'george';

Query OK, 1 row affected (0.03 sec)

mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2021-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

mysql>

Ok. Now I'm ready to go. I look at the proc that I wrote for Postgres:

CREATE OR REPLACE FUNCTION get_data_by_creation(

          timestamp without time zone, 

          timestamp without time zone)

RETURNS SETOF test_data 

AS

$$ 

SELECT name, address, create_date

  FROM test_data

  WHERE create_date >= $1

    AND create_date <= $2;

$$

LANGUAGE 'sql' VOLATILE;

That's obviously not going to work but like I said above, the changes are fairly minor. I need to add a delimiter call and drop the postgres specific stuff:

delimiter //

CREATE PROCEDURE get_data_by_creation(

    IN param1 timestamp, 

    IN param2 timestamp)

BEGIN

  SELECT name, address, create_date

    FROM test_data

    WHERE create_date >= param1

      AND create_date <= param2;

END;

//

That compiles fine. Now for the test. I can't use select so I will do a call. I write three call statements: one to return both records, one to "george" and one to return "lewis".

call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

When I run these, I get the expected results:

mysql> call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)



mysql> call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

+-------+------------+---------------------+

| name | address | create_date |

+-------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

+-------+------------+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Sweet! This code is actually not all that far from Oracle's PL/SQL. I'll do up an example of that next.

LewisC

Does MySQL GIS Make The Grade?

Bob Zurek of EnterpriseDB posted a blog entry today titled, "We slammed into a brick wall with MySQL". If you read his blog entry, the information he is referencing is in this press release, FortiusOne Migrates GeoCommons Intelligent Mapping Website to EnterpriseDB Advanced Server. If you read that press release, it says:

“We slammed into a brick wall with MySQL,” said Chris Ingrassia, chief technology officer, FortiusOne. “As an example, MySQL’s rather limited and incomplete spatial support dramatically impacted performance. We were looking for an affordable database solution, but we required enterprise-class features and performance that MySQL simply couldn’t deliver. Plus, philosophically we want to support open source-based technologies like EnterpriseDB.”

I'm not at all familiar with the MySQL GIS support and only remotely familiar with PostGIS (PostgreSQL GIS). Is MySQL GIS support lacking or was that particular application of MySQL GIS a bad fit? Anyone familiar with both? I'm curious as to how they compare. LewisC

How do I log into MySQL?

I remember the first time I downloaded MySQL. I think I was using Mandrake Linux. Anyway, the install was fairly painless but once it was installed, I had no clue how to run queries.

I was coming from an Oracle background and was used to SQL*Plus. I was also familiar with PostgreSQL and psql. For the life of me, I could not figure out how to get into MySQL.

So, for you developers and brand new users, you can easily start MySQL and start using it. This is not meant for a production installation, just for playing on your laptop or desktop.

Start MySQL by running mysqld (mysqld.exe on Windows). It will be in your MySQL home/bin directory. That gets the server portion of our program running.

The SQL*Plus equivalent is mysql (or mysql.exe). If you are logging in for the first time, you can use root. Once you are in, you can create other users.

To log in and run commands, type:

mysql -u root

That will load the character based MySQL Monitor. At this point you are in and ready to play.

LewisC