Author: Joe 'Zonker' Brockmeier
Kings-Lynne, a PostgreSQL developer who also works on the phpPgAdmin project, is working on a MySQL compatibility project for PostgreSQL that may allow people to utilize PostgreSQL with software that normally requires a MySQL database. According to Kings-Lynne, the MySQL compat project is comprised of about 100 MySQL functions, two MySQL aggregates, and “maybe a cast in PostgreSQL.”
Kings-Lynne said that he’s taking on this project out of self-interest, an opportunity to “polish my SQL stored procedure writing skills,” and a desire to “see PostgreSQL become the open source database of choice.”
Open source applications like MediaWiki, Drupal, SugarCRM, Bugzilla, and Joomla! are what Kings-Lynne is targeting with the MySQL Compatibility Functions — “The big MySQL open source products that I’d rather like to be able to use on PostgreSQL. The products … are in various stages of being ported, I know. It’ll also help the in-house Web development shops who decide to move to PostgreSQL. Imagine how much easier it would be to pitch a migration from MySQL to PostgreSQL to your boss if you could point to this library?”
The current version of the project is designed for compatibility with MySQL 5.x, and Kings-Lynne said that it will “probably” require PostgreSQL 8.x and above to work. The project is on its second beta release as of this writing, and a final 1.0 version should be released soon.
“That means I need to encourage people to support PostgreSQL in their applications. Using this library can make life a lot easier for them, and they don’t need to learn PostgreSQL’s stored procedure language. Since PostgreSQL has the built-in power to implement most of MySQL’s functions at no cost, then why not use it?”
According to Kings-Lynne, there are no compatibility issues with running the MySQL compatibility functions in conjunction with other PostgreSQL databases on the same PostgreSQL server. “This project does not make any changes or patches to the PostgreSQL source code in any way. All it does is implement what’s possible in the userland.”
Instead, the functions are loaded into a database, and exist only in that database. Kings-Lynne said that should not affect PostgreSQL performance at all.
There are some limitations, however, and some things won’t be implemented in the first release, if at all. Kings-Lynne noted that some MySQL functions would be difficult to provide support for, because it would require “altering the PostgreSQL grammar.” The project’s README file lists a number of incompatibilities between MySQL and PostgreSQL, including MySQL’s lack of a boolean type, the way MySQL does NULL handling, and the lack of case-sensitivity in MySQL when handling strings. He did suggest that there were workarounds, and that he planned to eventually tackle those issues in later versions.
Compatibility vs. porting
Why take all this time to create compatibility functions, instead of just adding support for PostgreSQL? PostgreSQL core member Berkus said that it would be better if projects supported PostgreSQL natively, but that’s not likely to happen. “Let’s face it, there’s probably 1,000 Web projects on SourceForge that don’t even support MySQL4 features yet. Asking these projects — many of which have a single part-time developer — to support multiple database systems will get us laughed at, at best.”
Kings-Lynne noted that it can be difficult to port applications to PostgreSQL when a project has only targeted MySQL. “It is a bit of a sorry state of affairs in the Web applications world at the moment in that 99% of open source projects begin targeting ONLY MySQL. It’s not until a few years down the track they decide to make it work on other databases, and then it’s painful.”
The PostgreSQL community has been adding features to make it easier to port MySQL applications to PostgreSQL for those who wish to use it. For example, Berkus cited new functions in the 8.1 release of PostgreSQL that would make it easier for supporting multiple relational database engines. “Dennis Bjorklund added the Lastval() function as an analog of MySQL’s last_insert_id, and Pavel Stehule added the Greatest() and Least() functions. These seem like minor things, but they’re the kind of thing that trip up migrators.”
In addition, Berkus said that MySQL was also helping to make it easier by moving towards greater standards compliance. “Applications built for MySQL 5.0’s ‘strict mode’ will be very easy to port.”
Berkus said that the biggest missing piece is documentation. “What’s really missing from our community is a good, comprehensive, up-to-date HOWTO on porting from MySQL. Something that covers all the gotchas and architectural differences. I think that would help users more than any number of scripts and compatibility functions. Unfortunately, writing and maintaining documentation is much less fun than writing code, so nobody’s done that yet.”
While the MySQL Compatibility Functions may not be the perfect solution, it might be the answer for PostgreSQL users who want to utilize a wider array of open source applications using their preferred database.
