6 ms·

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

by spudlyo 14y ago

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

robconery 14y ago
Yep - easy to alter the defaults. My point was that these are the defaults which I consider silly.
- spudlyo 14y ago
  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.
  - robconery 14y ago
    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.
    - spudlyo 14y ago
      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.
    - darnaut 14y ago
      > 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).
      - edwinnathaniel 14y ago
        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).
        darnaut 14y ago
        > How is MySQL competing with MS-SQL? http://www.pcworld.com/businesscenter/article/222168/oracle_goes_after_microsoft_sql_server_with_mysql.html http://www.pcworld.com/businesscenter/article/222168/oracle_...
    - taligent 14y ago
      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.
einhverfr 14y ago
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.
- epimenov 14y ago
  There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg157869.html http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...
  - einhverfr 14y ago
    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.
    - epimenov 14y ago
      It happened to us in the real world. And was fixed in 9.2
      - einhverfr 14y ago
jeffdavis 14y ago
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.

by spudlyo 14y ago

robconery 14y ago

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

spudlyo 14y ago
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.
- robconery 14y ago
  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.
  - spudlyo 14y ago
    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.
  - darnaut 14y ago
    > 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).
    - edwinnathaniel 14y ago
      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).
      - darnaut 14y ago
        > How is MySQL competing with MS-SQL? http://www.pcworld.com/businesscenter/article/222168/oracle_goes_after_microsoft_sql_server_with_mysql.html http://www.pcworld.com/businesscenter/article/222168/oracle_...
  - taligent 14y ago
    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.

einhverfr 14y ago

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.

epimenov 14y ago
There definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg157869.html http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...
- einhverfr 14y ago
  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.
  - epimenov 14y ago
    It happened to us in the real world. And was fixed in 9.2
    - einhverfr 14y ago

jeffdavis 14y ago

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.