Hacker News Reader

Comment by spudlyo | Hacker News Reader

The complaint that MySQL is by default loosey-goosey with your data is valid, but it's an easy default to change. Here is what happens when you run some of the commands shown in that 'Why Not MySQL?' video on a sanely configured MySQL system by setting SQL_MODE to TRADITIONAL. This mode also allows you to not have dates with zeroes, etc.

    mysql> alter table test change column my_money my_money decimal(2,0);
    Query OK, 2 rows affected (0.03 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> insert into test values (4,'bar', 100);
    ERROR 1264 (22003): Out of range value adjusted for column 'my_money' at row 1

View on HN

Semantic changes in configuration files are problematic in MySQL for the same reasons they are problematic in PHP (the only other language that seems to do that):

* Your application then no longer works properly on a default install. Possibly in very subtle ways that you won't notice.

* Upstream will never make your settings the default. It would break too many applications, so you are on your own with those settings pretty much forever.

* Few users use non-default settings, so you are more likely to run into strange behaviors that are either bugs or can't be readily explained in a forum.

* Just imagine if you are trying to support multiple applications that each have their own settings they prefer. Especially if you want to integrate your data together at all, which is one of the main purposes of a database system.

1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data?

2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true:

* Executing an insert statement in the form of INSERT foo (bar) VALUES (1), (2), (3), (4);

* Only one connection/session active at a time (for example during a data migration to MySQL)

* Frequency goes up when more rows are inserted per statement

* Which inserts trigger the deadlocks are not reproducible

I believe this is an issue with race conditions and threads, perhaps a lock contention that isn't being handled properly between index and table writes or the like. I can reproduce it by inserting a couple million rows into a table, a few thousand at a time, but the statements where this occurs varies from one run to the next.

I have never seen braindead locking behavior on PostgreSQL.

There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...

That certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.

It happened to us in the real world. And was fixed in 9.2

ouch. Reminds me of the (one) data corruption bug I found in PostgreSQL 7.3, allowing me to create write-only tables....

Actually it is not fixed yet, the patch was pushed to 9.3 and Alvaro is still working on it.

It has happened to us too, though it was very easily fixed in our case due to the excellent deadlock reporting. And I can also confirm that the case for Joel (the original reporter) is also a real world case.

Link to talk about it http://www.pgcon.org/2012/schedule/events/483.en.html

Edit: Not sure if I would call it braindead though. Highly annoying, yes. Alvaro has spent a lot of time into solving this, it is much harder than it initially seems.

What version of MySQL? Sounds like the old AUTOINC lock mode behavior. http://insatiabledemand.ideeli.com/post/18850015294/diagnosi...

I don't think so. First ID's were being assigned manually, so I don't think a table lock would be pulled out, and secondly only one statement was executing at a time. Only one connection issuing sequential statements, and nobody else on the server.

I think that this is thread/mutex based because of the fact that I could try to run the same series over and over and get different sets failing. So it seems to me the issue might be something environmental affecting timing.

Again the really braindead thing here was that it was only one statement, only one concurrent transaction at any point in time.

1. Yes, applications can set sql_mode themselves at the session level. An admin cannot prevent this. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

So, what good is setting a sensible default as a DBA if the dev guys can override it?

I mean it's fine if you only allow one app to write to your db, but once you have more than one, the fact that the option to ignore invalid data even exists ought to make one very nervous if the data stored is worth anything.

A dev can only override it for their session, not at the global level.

Yep - easy to alter the defaults. My point was that these are the defaults which I consider silly.

Yes, it's a silly default, but I don't think it's the fatal flaw that you make it out to be. That's ok though, this is clearly an advocacy piece, so I don't expect nuance.

If none of that freaks you out, then I have one word for you: Oracle.

This to me smacks of FUD.

Oracle, evil and terrible as they are, would have a very hard time killing or otherwise harming MySQL due to the code being GPL'd and the vibrant MySQL community (Percona, Facebook, Twitter, Drizzle, MariaDB, SkySQL, etc) that fixes bugs, adds features, and directly competes with the work Oracle does.

I've been the victim of that flaw - I biffed a Rails migration and sent (what should have been) $200 transactions into the DB as $99. My local tests used SQLite, so I didn't catch it until we went live - true deal there. Very dumb on my part.

At the same time - changing data values not stupid? We may differ on this point but I believe it's insanity. I'd love to see NULLs honored as well as constraints.

That said - 1/0 = NULL is on par with Javascript silliness.

Finally, it's easy to characterize my comment as FUD but Oracle has a habit of using Open Source systems to its advantage. Why else would they buy MySQL? And the licensing is not as strong as you may think.

Either way - it's very argumentative and I'm happy to agree it's unsure at best.

I agree, it's insanity. I have no idea why it's still the default, there are probably a bunch of crappy apps out there that depend on this behavior. Anyway, sorry for hijacking your thread. I thought your video production and screencast techniques were top notch. As someone who dabbles in creating geeky youtube videos, I have to give you credit for a job well done.

> Why else would they buy MySQL? And the licensing is not as strong as you may think.

To compete with Microsoft SQL Server and to further solidify their customer base (most Oracle customers already use MySQL).

How is MySQL competing with MS-SQL? I'm quite sure they're in different league. Price point wise MS-SQL isn't that much cheaper than Oracle.

Here's one word: upsell _HARD_

That was my experience trying to buy support/license for GlassFish. Some sales guy tries very hard to upsell us to use WebLogic (of course, otherwise he's not going to fill his quota or get a bonus).

> How is MySQL competing with MS-SQL?

http://www.pcworld.com/businesscenter/article/222168/oracle_...

I would characterise your comment as FUD. And now you are questioning the licensing.

There is NO evidence that MySQL has licensing problems otherwise we wouldn't have the Facebook, Twitter and Percona forks. It's as simple as that really.

Reply on news.ycombinator.com