Lest you think I’ve drunk deeply of the Open Source Kool-Aid, let me please politely but forcefully disabuse you of that notion… Let’s talk about MySQL.
So… MySql? This darling of the Open Source scene, recently purchased by Sun because they were losing out on that whole Web 2.0 thang and needed to acquire some piece of that hot, sweet, sweaty LAMP stack action by hook or crook? Yeah, that MySQL.
It sucks. And I don’t mean a little, I mean lots. And by lots I mean more suck than James Dyson will need in hundreds of lifetimes. I mean suck on the order of Harley-starting, golf-balls-through-garden-hoses, black-hole-consuming-galaxies suck.
Did I mention MySQL sucks?
As part of the project referenced in my last post, I need to match data in a MySQL database to data in an Excel spreadsheet to data in a Microsoft SQL Server database, then ported back to a MySQL database. The client wants the code that will do all this to be SQL, not a compiled language, which means that most of this will have to be written in MS SQL Server since that’s the only platform that can really read all three data formats. So what’s the problem with MySQL?
Where to begin…
Never mind MySQL’s atypical SQL dialect – that’s pretty much par for the course when it comes to SQL databases, and there are regular expression libraries and code bases out there that will convert 90% of your Microsoft-specific SQL code to MySQL-specific code. Forget MySQL’s use of the back-tick as a way of identifying table and field names – sure, quotes or square brackets would be far more standard, but maybe (maybe) the back-tick is in a reasonable position on a Swedish keyboard instead of being in the carpal-tunnel-inducing location it is on standard English keyboards. Because, you know, you wouldn’t want something to identify something as esoteric as table and field names to be in an inconvenient location, right? And we definitely won’t talk about how MySQL barely deserves to be classified as an RDBMS (Relational DataBase Management System) since it, well, doesn’t entirely support the R part of that acronym. Hey, referential integrity is overrated, right?
No, these idiosyncrasies pale beside the horror of actually trying to use MySQL’s tools. Oh, the command line tools work well enough, as does MySQL QueryBrowser and MySQL Administrator (as long as you don’t expect to work with anything other than MySQL data sources). No, let’s talk about their offerings intended to make MySQL a viable alternative for commercial solutions.
So, my struggles with Visio and MS SQL Server? Laughably minor when compared to how crappily MySQL’s Workbench works. Want to reverse-engineer a MySQL database with their visual designer? You can’t – at least, not with the ‘community’ version, only the commercial version. One problem with this – they haven’t released the commercial version yet, and the community version’s been available for a while now.
Want to migrate a MS SQL Server database to MySQL using MySQL’s Migration Toolkit? Good luck connecting to your MS SQL database. From what I can tell on MySQL’s forums, I am far from alone in facing this problem, and… there’s no clear fix for it. There are no checklists detailing what the configuration needs to be for either the source or destination server – you’re on your own, there. Oh, and don’t bother with the video ‘tutorial’ for migrating MS SQL to MySQL – besides being utterly simplistic, it shows the user doing something you can’t apparently do: save a connection to a MS SQL database for MySQL to reuse. Sure, you can edit the XML file that stores saved user connections – good luck finding documentation on what the values need to be for the connection elements to actually work properly.
So maybe you can port MS SQL structures and data by running MS SQL queries against a linked MySQL database. After all, as far as I can tell, every other database driver that lets you link MS SQL to other databases will let you do this – and you can do it in reverse, copying MySQL to MS SQL. Ha. Nope. Apparently, I don’t have privileges to do anything other than view data from the linked server – even though I’m connecting to it using the MySQL root login. “CREATE TABLE foo (bar INT NOT NULL, baz varchar(255) NULL);”? Forget it. Not gonna happen. Not using any of the tools provided by MySQL anyway.
Computers. Programmers. Software. Hmph.