Navigate/Search

Sweet Jesus Tap-dancing Christ, do I hate MySQL

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.

10 Responses to “Sweet Jesus Tap-dancing Christ, do I hate MySQL”

  1. Tom Says:

    Not that it fixes any of the issues with MySQL, but one thing that makes life a little easier is HeidiSQL. Or maybe you already know about it– then things are still as bad as they were before.

  2. Tom Says:

    Not that it fixes any of the issues with MySQL, but one thing that makes life a little easier is <a href="http://www.heidisql.com/&quot; rel="nofollow">HeidiSQL</a>. Or maybe you already know about it– then things are still as bad as they were before.

  3. protected static Says:

    No, I wasn’t – I’ll have to check it out.

    Thanks!

  4. protected static Says:

    No, I wasn't – I'll have to check it out.

    Thanks!

  5. Tom Says:

    It’s not SQL Management Studio (or whatever they call it), but it gets you a little closer.

  6. Tom Says:

    It's not SQL Management Studio (or whatever they call it), but it gets you a little closer.

  7. dean Says:

    I believe MySQL will import comma-delimited data. I don’t know what sort of data volume you’re dealing with, but I’d probably attack this by pulling the data into MySQL, Excel and MSSQL data into their own tables, and then operate on it with MySQL scripts. Of course, MySQL prior to.. what, v 5? doesn’t support stored procedures, so you’d be stuck with cutting and pasting scripts, but still…

    All this is off if you’re dealing with data on the order of millions of rows in SQL Server. If THAT is the case, I’d try like hell to get MySQL to connect to the SQL Server db so you could pull the data in.

    Data migration/integration projects are always badly underestimated. Because these projects are so full of pitfalls, holes, blind alleys, and tripwires. With an occasion IED thrown in.

  8. dean Says:

    I believe MySQL will import comma-delimited data. I don't know what sort of data volume you're dealing with, but I'd probably attack this by pulling the data into MySQL, Excel and MSSQL data into their own tables, and then operate on it with MySQL scripts. Of course, MySQL prior to.. what, v 5? doesn't support stored procedures, so you'd be stuck with cutting and pasting scripts, but still…

    All this is off if you're dealing with data on the order of millions of rows in SQL Server. If THAT is the case, I'd try like hell to get MySQL to connect to the SQL Server db so you could pull the data in.

    Data migration/integration projects are always badly underestimated. Because these projects are so full of pitfalls, holes, blind alleys, and tripwires. With an occasion IED thrown in.

  9. protected static Says:

    Yeah, that’s basically what I’m going to do… Mostly this is a pain because we’re trying to normalize real-world data (i.e., messy) that was originally collected and stored without enforced relations… Whee!

    Fortunately, there’s been some scope creep (how often do you hear that?), so I’ve got some more time to figure out all the vagaries of this nastiness…

  10. protected static Says:

    Yeah, that's basically what I'm going to do… Mostly this is a pain because we're trying to normalize real-world data (i.e., messy) that was originally collected and stored without enforced relations… Whee!

    Fortunately, there's been some scope creep (how often do you hear that?), so I've got some more time to figure out all the vagaries of this nastiness…

Leave a Reply