6 ms·

Call me when MySQL has a working insert statement. One that doesn't randomly truncate your data, or allow insertion of nulls into not null columns.

by fleitz 14y ago

ceejayoz 14y ago
You mean like: SET GLOBAL sql_mode='TRADITIONAL'; ?
- twerquie 14y ago
  That's like selling aftermarket airbags for your car.
  - ceejayoz 14y ago
    It's no more aftermarket than InnoDB support is.
- deleted 14y ago
  [deleted]
- einhverfr 14y ago
  But individual apps can change that on the session level, right? So still no guarantees and you more or less have to audit every app connecting to your db to make sure it isn't tampering with the sql_mode if you value data integrity.
  - ceejayoz 14y ago
    Individual apps can do far more destructive stupid things than changing sql_mode.
    - einhverfr 14y ago
      The point though is that you lose a set of guarantees with MySQL that you don't lose with PostgreSQL. Yeah, computers make it possible to make more mistakes faster than any invention since handguns and tequila but when you lose the ability to guarantee that your declarative constraints are actually followed, you lose the ability to prove that certain types of errors are not being made. That's a big deal.
Roboprog 14y ago
I think ceejayoz took you a little too literally. MySQL continues to scare the hell out of me. It was bad enough before Oracle go a hold of it: the designers seemed completely unconcerned with data integrity. It has many patches to deal with individual issues, but when the creators just fundamentally don't care??? The GLP-not-LGPL license sounds like a booby trap gladly left in place by Oracle from before they got it. I can't see Oracle wanting to do anything other than make it light and fast (at expense of correctness), either, since if you want "data integrity", they have a solution for you.
- swah 14y ago
  But OTOH it powers gigantic websites a la Facebook. Can it really be that bad fundamentally?
  - scott_w 14y ago
    Facebook also uses PHP... In all seriousness, Facebook's requirements are for high availability, with data integrity only mattering to the point at which it affects the usability of Facebook. In other words, Facebook can tolerate a high degree of shit in the system before it becomes a problem. That isn't bad in and of itself, but you need to investigate your own needs before using something because Facebook/Google use it.
    - taligent 14y ago
      So does Twitter, Flickr, Yahoo, Wikipedia, Craigslist, LinkedIn etc. And I would imagine that most of those wouldn't tolerate any issues with data integrity.
      - einhverfr 14y ago
        What is it powering though? The way I look at it is this: MySQL is at its roots an SQL-like database specializing in content management. The sorts of data integrity problems that can occur in MySQL are entirely tolerable in a content management environment for the most part. It was designed to be a fast backend for web sites with an SQL interface. A lot of the issues it has are entirely due to that legacy, but those issues don't matter at all when you are using MySQL for content management. Every single one of those examples are probably using MySQL for some sort of content management. So it isn't just whether they can tolerate some crap in their systems. It is what sort of crap they can tolerate. If the MySQL gotchas don't result in intolerable crap, then it doesn't matter. Now, personally I wouldn't run an accounting system on MySQL particularly if I was expecting many apps to access the same db. This is because the sorts of issues that MySQL has are real show-stoppers in these environments. But content management? Why not? Heck many of these data integrity problems may be features in these environments. For example, suppose MySQL@Twitter truncates your tweets to the maximum length silently without issuing an error. Bug or feature? Suppose it truncates numbers in your accounting software? Those are two completely different cases and while they may in theory be comparable, in practice they are not.
einhverfr 14y ago
Yeah, I have also had multi-row inserts deadlock against themselves in MySQL. However, you need to reword that slightly. MySQL does have such a working insert statement if you set strict mode. The problem is that apps can unset strict mode. Until that changes..... So really you should word it as: "One that can be guaranteed not to randomly truncate your data, or allow insertion of nulls into not null columns." MySQL inserts in strict mode don't do these things. MySQL inserts cannot be guaranteed not to do these things however. Therefore this relegates MySQL, in my view, to a one-app-per-db environment because you cannot prove that your db constraints will be properly enforced and therefore have to independently verify this aspect in every app that connects.

Call me when MySQL has a working insert statement. One that doesn't randomly truncate your data, or allow insertion of nulls into not null columns.

by fleitz 14y ago

Call me when MySQL has a working insert statement. One that doesn't randomly truncate your data, or allow insertion of nulls into not null columns.

ceejayoz 14y ago

You mean like: SET GLOBAL sql_mode='TRADITIONAL'; ?

twerquie 14y ago
That's like selling aftermarket airbags for your car.
- ceejayoz 14y ago
  It's no more aftermarket than InnoDB support is.
deleted 14y ago
[deleted]
einhverfr 14y ago
But individual apps can change that on the session level, right? So still no guarantees and you more or less have to audit every app connecting to your db to make sure it isn't tampering with the sql_mode if you value data integrity.
- ceejayoz 14y ago
  Individual apps can do far more destructive stupid things than changing sql_mode.
  - einhverfr 14y ago
    The point though is that you lose a set of guarantees with MySQL that you don't lose with PostgreSQL. Yeah, computers make it possible to make more mistakes faster than any invention since handguns and tequila but when you lose the ability to guarantee that your declarative constraints are actually followed, you lose the ability to prove that certain types of errors are not being made. That's a big deal.

Roboprog 14y ago

I think ceejayoz took you a little too literally. MySQL continues to scare the hell out of me. It was bad enough before Oracle go a hold of it: the designers seemed completely unconcerned with data integrity. It has many patches to deal with individual issues, but when the creators just fundamentally don't care??? The GLP-not-LGPL license sounds like a booby trap gladly left in place by Oracle from before they got it. I can't see Oracle wanting to do anything other than make it light and fast (at expense of correctness), either, since if you want "data integrity", they have a solution for you.

swah 14y ago
But OTOH it powers gigantic websites a la Facebook. Can it really be that bad fundamentally?
- scott_w 14y ago
  Facebook also uses PHP... In all seriousness, Facebook's requirements are for high availability, with data integrity only mattering to the point at which it affects the usability of Facebook. In other words, Facebook can tolerate a high degree of shit in the system before it becomes a problem. That isn't bad in and of itself, but you need to investigate your own needs before using something because Facebook/Google use it.
  - taligent 14y ago
    So does Twitter, Flickr, Yahoo, Wikipedia, Craigslist, LinkedIn etc. And I would imagine that most of those wouldn't tolerate any issues with data integrity.
    - einhverfr 14y ago
      What is it powering though? The way I look at it is this: MySQL is at its roots an SQL-like database specializing in content management. The sorts of data integrity problems that can occur in MySQL are entirely tolerable in a content management environment for the most part. It was designed to be a fast backend for web sites with an SQL interface. A lot of the issues it has are entirely due to that legacy, but those issues don't matter at all when you are using MySQL for content management. Every single one of those examples are probably using MySQL for some sort of content management. So it isn't just whether they can tolerate some crap in their systems. It is what sort of crap they can tolerate. If the MySQL gotchas don't result in intolerable crap, then it doesn't matter. Now, personally I wouldn't run an accounting system on MySQL particularly if I was expecting many apps to access the same db. This is because the sorts of issues that MySQL has are real show-stoppers in these environments. But content management? Why not? Heck many of these data integrity problems may be features in these environments. For example, suppose MySQL@Twitter truncates your tweets to the maximum length silently without issuing an error. Bug or feature? Suppose it truncates numbers in your accounting software? Those are two completely different cases and while they may in theory be comparable, in practice they are not.

einhverfr 14y ago

Yeah, I have also had multi-row inserts deadlock against themselves in MySQL. However, you need to reword that slightly. MySQL does have such a working insert statement if you set strict mode. The problem is that apps can unset strict mode. Until that changes..... So really you should word it as: "One that can be guaranteed not to randomly truncate your data, or allow insertion of nulls into not null columns." MySQL inserts in strict mode don't do these things. MySQL inserts cannot be guaranteed not to do these things however. Therefore this relegates MySQL, in my view, to a one-app-per-db environment because you cannot prove that your db constraints will be properly enforced and therefore have to independently verify this aspect in every app that connects.