3 ms·

> I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it

> I'd rather have my database complain than do stuff that could lose data. One great example is when you try to stuff more characters into a text field than it can accommodate. By default, MySQL just truncates the string silently; Postgres just doesn't allow it. That's your opinion, but the truth is not necessarily black and white. Postgres will probably lose more data in your case when the application developer doesn't handle the exception for a text field too long, and loses the whole row. (If you're an application developer, you should audit your code now.) A well-written application using Postgres would have to truncate the data before insert, achieving the same result as having MySQL auto-truncation, but with more initial and on-going maintenance effort in the case of Postgres. And the existence proof that MySQL's behavior is ok is that MySQL is the most widely-used relational databases, and powers most Internet sites. Source: DBA.

pmiller2 6y ago
Here's the deal: I am not presenting my opinion as objective fact, nor do I actually care that you're a DBA. If anything, the fact that you are a DBA makes your opinion less relevant to me as an application developer. Simply put, you do not share my pain, and the people who have upvoted me, while your comment is currently gray as I write this, have. The example was intended to show a scenario in which MySQL silently loses data. There are certainly situations in which this sort of data loss is acceptable. I've been involved in scenarios where it was not acceptable, I was surprised by the behavior, and I ended up with a bunch of missing data. The problem here is that I was surprised by my database. That should literally never happen. The correct behavior in this specific case is for the database driver to raise an exception or warning (exactly which should probably be a configurable option, defaulting to an exception). That way, if an exception gets raised in my application, at the very minimum, I have a traceback in my logs to tell me something went wrong, which allows me to fix the issue sooner rather than later. As for your existence proof that "MySQL's behavior is ok," the fact that MySQL has a bigger installed base literally proves nothing. I guarantee you there are folks out there who are experiencing such behavior and being surprised by it, just like I was. There are people out there for whom this specific behavior is not a problem. And, there are people out there who are length checking every bit of text they try to stuff into a VARCHAR(n) or CHAR(n) field using MySQL, because they started with MySQL and they're basically stuck with it, unless they want to do major surgery on their app.
- redis_mlc 6y ago
  > The example was intended to show a scenario in which MySQL silently loses data. So you're dogmatic and can't accept alternate opinions. And you'll continue whining while the world runs on MySQL, including trillion-dollar companies like Google and Facebook, not Postgres. Got it - what a tool.
  - pmiller2 6y ago
    Guess you're out of actual arguments now that you've resorted to name calling. Edit: I notice a lot of gray on your profile page under comments. Do you need help? This is a genuine offer. My email is in my profile.
ryanbrunner 6y ago
> Postgres will probably lose more data in your case when the application developer doesn't handle the exception for a text field too long, and loses the whole row. (If you're an application developer, you should audit your code now.) The thing is, you'll noisily lose your data, which is far more likely to be caught and corrected. Even the most roughly put together web app is probably going to have some mechanism to alert about server-side production errors. > A well-written application using Postgres would have to truncate the data before insert, achieving the same result as having MySQL auto-truncation, but with more initial and on-going maintenance effort in the case of Postgres. Not necessarily - you could alert the user that they need to provide less data, or mitigate it in some other way (maybe storing a summary and the full version in a blob somewhere).

pmiller2 6y ago

Here's the deal: I am not presenting my opinion as objective fact, nor do I actually care that you're a DBA. If anything, the fact that you are a DBA makes your opinion less relevant to me as an application developer. Simply put, you do not share my pain, and the people who have upvoted me, while your comment is currently gray as I write this, have. The example was intended to show a scenario in which MySQL silently loses data. There are certainly situations in which this sort of data loss is acceptable. I've been involved in scenarios where it was not acceptable, I was surprised by the behavior, and I ended up with a bunch of missing data. The problem here is that I was surprised by my database. That should literally never happen. The correct behavior in this specific case is for the database driver to raise an exception or warning (exactly which should probably be a configurable option, defaulting to an exception). That way, if an exception gets raised in my application, at the very minimum, I have a traceback in my logs to tell me something went wrong, which allows me to fix the issue sooner rather than later. As for your existence proof that "MySQL's behavior is ok," the fact that MySQL has a bigger installed base literally proves nothing. I guarantee you there are folks out there who are experiencing such behavior and being surprised by it, just like I was. There are people out there for whom this specific behavior is not a problem. And, there are people out there who are length checking every bit of text they try to stuff into a VARCHAR(n) or CHAR(n) field using MySQL, because they started with MySQL and they're basically stuck with it, unless they want to do major surgery on their app.

redis_mlc 6y ago
> The example was intended to show a scenario in which MySQL silently loses data. So you're dogmatic and can't accept alternate opinions. And you'll continue whining while the world runs on MySQL, including trillion-dollar companies like Google and Facebook, not Postgres. Got it - what a tool.
- pmiller2 6y ago
  Guess you're out of actual arguments now that you've resorted to name calling. Edit: I notice a lot of gray on your profile page under comments. Do you need help? This is a genuine offer. My email is in my profile.

ryanbrunner 6y ago

> Postgres will probably lose more data in your case when the application developer doesn't handle the exception for a text field too long, and loses the whole row. (If you're an application developer, you should audit your code now.) The thing is, you'll noisily lose your data, which is far more likely to be caught and corrected. Even the most roughly put together web app is probably going to have some mechanism to alert about server-side production errors. > A well-written application using Postgres would have to truncate the data before insert, achieving the same result as having MySQL auto-truncation, but with more initial and on-going maintenance effort in the case of Postgres. Not necessarily - you could alert the user that they need to provide less data, or mitigate it in some other way (maybe storing a summary and the full version in a blob somewhere).