Slashdot Mirror
Sun's Mickos Is OK With Monty's MySQL 5.1 Rant
narramissic writes "Back on November 29, MySQL developer Michael Widenius trashed Sun's decision to give MySQL 5.1 a 'generally available' designation in a now-infamous blog post. Widenius warned users to be 'very cautious about MySQL 5.1' because 'there are still many known and unknown fatal bugs in the new features that are still not addressed.' And now we get Sun's response. In an interview Monday, Marten Mickos, senior VP of Sun's database group, said, 'I learned over many years about the benefits and the painfulness of absolute transparency in open source. A little bit of debate never hurts. This is part of being an open-source company. ... People are free to blog about what they want.' Doubtless, this will do nothing to end the debate over whether Widenius will follow fellow MySQL co-founder David Axmark's lead and leave Sun."
While I think the AC may be overstating this a bit, I do think the term 'infamous' is being a bit overused here. Ask any random person on the street about this issue and you're probably going to get a response along the lines of "What's MySQL?"
This is my sig. There are many like it but this one is mine.
prepare to hear about known unknowns
So tell us, what exactly IS yourSQL?
What's MySQL?
What is the debate? MySQL releases with known crashing bugs. Noone is disputing that.
Is the debate over whether or not it is okay to ship a database with known crashing bugs?
It really surprises me to hear someone from Sun saying that one can debate the merits of a crashing database. If this is the expected level of performance from MySQL, no wonder people shun it. At the very least they could have called it a beta or rc release, that would set the expectation level at something approaching reality.
We use MySQL in a number of critical aspects of our company. I'd rather have a company be honest and let me know I might have some issues with this new release than pretend there are no issues. That lets me stay with my current version and upgrade later.
Rather be on the stable blunt edge in critical infrastructure, not the bleeding edge.
iSQL
50% more pretentious, runs all the coolest sites in the world.
MySQL has never been a stable database program. I've never had any other database system that just blows a database table at random. Nothing is more exciting then having a website go down because one of the tables got marked "corrupt" and you have to go "REPAIR TABLE". The damn thing might not even have a load on it and it will blow up!
First of all, what is MySQL doing that corrupts tables during normal operation and second of all? Seriously, a database shouldn't crash like that, ever.
Second of all, it might as well try to auto-repair the damn table. I mean, I've never had it loose data, only somehow decide the table was "corrupt" and then taken offline. And who cares if you do it automatically and it looses data, this is MySQL we are talking about here! They make no claim about data integrity and the user base doesn't even know what that means (must be a car or some "enterprise" feature used only by NASA and Fortune 50 companies)! I mean, 0000-00-00 is a valid date according to them!
But alas, this is MySQL we are talking about here. I mean, it isn't like you are putting any sensitive data on it right? I mean, surely only a fool would use it for anything besides storing data like "number of shoes in my closet" or "number of purses owned by the wife", right?
Good 'ol MySQL. I mean, what fun is a database server that is consistent or predictable?
If there really was an iSQL it would cost money, decide which SQL statements you wanted to run and how to run them, have no export to other databases but come in a really really flashy box.
Uh... I didn't know yourSQL was actually a thing, I was trying to make a joke.
I'm not really touching this potato, maybe you're running into some quirks / unfortunate query. Just some quick questions:
- Why don't you have a PK / any index in the address table?
- Did you try a different syntax (e.g. WHERE vs. JOIN ON)?
- Did you try setting different indexes? Tried forcing a specific index?
Why yes, there is an iSQL, though it's a command-line client tool for MS SQL. But it does meet all of your criteria.
If your database is crashed and is no longer capable of accepting data, how is that different from losing data? Go ahead and explain that with a straight face. Do they have another data store where you can keep your data until the database is fixed?
Sun should be ashamed of themselves for even calling this abomination a database in the first place. The word 'database' carries a whole host of expectations that the product simply does not live up to. A text file makes a better database than MySQL.
I mean this is mysql here, not a real relational database. Kind of like sloppy cowboy VB coders of yore, MySQL has the same kind of attitude. "If it works, who cares if it is right".
I mean, sure people site "Well, Slashdot, FaceBook, and BIGCO use it, so MySQL is okay". But have those people ever realized how easy it is to lock yourself into MySQL? MySQL is so full of non-standard behavior and gotchas that it can be very painful and difficult to migrate to a real database. So what to companies do? Layer on a huge pile of Memcached and crazy "archive databases" to scale when if they had started with a more standard, scalable database system maybe they could have allocated their developer time to something more productive.
Anyway, I rant. I just think MySQL is only used by large companies because either they don't understand how much extra developer hours are spent working around MySQLisms or they know MySQL sucks, but know that it is to costly to migrate to something better.
But that has nothing to do with your post or my original post does it? I'll conclude with the main problem--Like VB, MySQL grew a whole crop of developers who dont know any better. While I dont know if you can blame that on the database or a programming language, I chuckle when I see MySQLisms in code (like never using a "JOIN" because it mysql is "faster if you give it small SELECT statements).
But what happens if you want to do full text search? Besides, your nice ACID InnoDB kinda backfires when half the tables are using MyISAM, doesn't it? And good thing MySQL lets you know when your nice happy transaction will not roll back properly because half the tables are MyISAM, right?
As I said, what fun is a database server that is consistent or predictable?
You're probably not going to like this answer but....
The data is not in an optimal form for MySQL. Consider storing the IP address as a BINARY CHAR field, and not as a number. Order the bytes so that it is in big-endian byte order. Now MySQL can use it's indexes.
The problem is that MySQL treats index keys as a binary string so if you are using a function to join two tables, MySQL does things the hard way.
No sig. Move along - nothing to see here.
Except that mySQL is open Source, how can they kill the copy that I have on my hard drive and can re-distribute?
Okay... so what's your query?
The problem with this scenario and why it will always bother people who are used to non-MySQL RDBMS, is that really, you haven't had to think about things like that in a decade (more if you were giving your first born to Oracle).
Equivalent where vs joins should give similar query plans. If not, since the SQL standard where JOINs are first class citizen state that its what you should use for linking tables (no matter how exotic the JOIN), it should handle that better, and having to force an index is usually a crutch (even Microsoft will often consider it a bug, and the logical scenarios get fixed between versions... in 2000 you had to force em every so often, in 2005 they solved most of them, in 2008 I haven't seen an occurance where the analyzer got it wrong...).
The lack of index in the address table is indeed fairly illogical here, but for such a simple query, most RDBMS will be able to do it fine anyway, -especially- with table statistics. In this case, my pragmatic self would never expect it to be fast, but in most RDBMS, it will still be zippy. The only ones I've personally tried that will choke (even with gigs of data) are MySQL and PervasiveSQL (Pervasive makes MySQL look like the holy grail, thats for sure). I've had douzens of databases with up to 50-100 gigs of data (though it was spread out over at least 75-100 tables, sometimes up to a thousand) with no indexes aside for the primary keys and the systems were fast, on MSSQL, Oracle and Postgres (not saying indexes wouldn't have helped a ton, but it wasn't my decision to take), so its a bit of a culture shock to many when you have to spell out your intent to the database that much.
Sure, innoDB gives you transactions, but at the cost of a lot of MySQL's vaunted speed; half of the reason it took so much of the early open source DB business, lo those many years ago (the other being ease of app development.)
I'm a database rube, but even I've left MySQL for PostgreSQL. Try PostgreSQL, just try it. This isn't your old 7.3 postgres anymore, no siree. ACID all the way, kicks InnoDB's butt and is probably faster than MyISAM most of the time for most of your stuff.
8.4 is nearing completion and it's going to be fabulous. Try it.
The problem is, MySQL hasn't had a stable, crash-free release in MANY years. The version you think is stable is only stable with your data set and queries.
.
Does anyone remember those Windows 2000 source code comments that leaked a few years back?
We should not punish Open Source for being Open Source. We are a community. OK, more like a family at Thanksgiving, bickering and such.
Bzzzt. It exports to Oracle just fine by default, thanks for playing.
Having experience with this problem, I can tell you that the problem is that MySQL's implementation of b-tree indices doesn't work well for ranges (specifically, it can only eliminate rows on one side of the inequality). The solution is to use rtree indices (GIS functions, 'SPATIAL INDEX').
I didn't come up with the technique, but I can't find the webpage where I found it. I did end up using it for a geolocation system though.
I'd suggest looking into the polygon type. This article may be of some use.
The basic idea is that you create a polygon column and create an entry that corresponds to the start/end points for each row in your table, then you can run a query like this:
SELECT * FROM your_table WHERE MBRContains(polyfield, POINTFROMWKB(POINT(INET_ATON('1.2.3.4'), 0)));
As a point of reference, the above query runs in my local DB here in 0.02 seconds for any IP I can throw at it.
HTH.
Thank you for this interesting suggestion. I want it to work but I tried what you suggest and I don't see any difference in MySQL's query plan. I created exactly the same tables, except all columns of type int(10) unsigned converted to binary(4). The query plan is identical to the original.