5 ms·

Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. All prior version

Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. All prior versions have hit EOL support, so there is literally no supported version of MySQL which has this behavior out-of-the-box. The topic of MySQL utf8 vs utf8mb4 was discussed in depth just six days ago on HN: https://news.ycombinator.com/item?id=29793916 https://news.ycombinator.com/item?id=29793916

capitainenemo 5y ago
That's good to know, however I was encountering this MySQL truncation just last year in COTS software, so clearly there's a long tail. As a related example, log4j 1.x has been deprecated for years, but 100% of atlassian uses it with their own maintained version. In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x. Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?
- evanelias 5y ago
  For sure, there's a huge long tail. However, if a company is using 6+ year old EOL software that has flaws, that's outside of the vendor's control. > What happens with an existing upgrade sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings. > typical distro package upgrade Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
  - KronisLV 5y ago
    > Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen! MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL. That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.
    - brazzledazzle 5y ago
      Same. If I'm building and managing any special instances/VMs these days I avoid installing any packages but those required to run containers. There's certainly tradeoffs but it makes software management so much easier. Upgrading is a matter of stopping and starting a container using an upgraded image and if it's supported rolling back is just a matter of stopping the newer container and restarting the old. A container hosting service/platform and it's abstractions are better but if you have to run some one offs outside of that it's so much nicer to use containers. I call these pet cattle. I still bind mount directories though. Docker volumes seem really convenient but a few years back I dealt with helping out a coworker with some data loss (pre-volumes) in a container's filesystem so I have some trust issues with how they implement storage.
- waynesonfire 5y ago
  I'm going to use this as an opportunity to vent. Wouldn't it be a nice world where you can just declare something EOL and it disappears and somehow magically it's not a problem anymore for anyone end-to-end? Thing's just don't work that way. Like, suddenly, a product that was working perfectly fine for who knows how long suddenly needs to modified because some ass-hats thinks that EOL isn't suitable for production. There are a number of reasons to upgrade a dependency or a product; an EOL label shouldn't be a part of the calculus. EOL may imply certain conditions that may influence the decision to upgrade but for the sake of EOL you can eat a bag of dicks. Think harder why you're proposing an upgrade is needed and how it'll add value to the business. EOL software is perfectly fine especially because it's been battle tested. We also use log4j 1.x all over the place and I feel zero need to change that.
  - dimitrios1 5y ago
    It is perfectly reasonable to continue to use EOL software, but you have to assume maintenance costs in that case. It would be irresponsible to continue to use EOL software as is. EOL is a perfectly natural concept. Nothing in this world lasts forever, including our machines, electronics, cars, batteries, lives, and yes, software.
    - rkagerer 5y ago
      If software lasted a lifetime, life would be so much easier.
      - kortex 5y ago
        For the end user, not the maintainers. Who's gonna sign up to keep that ENIAC, PDP-11, ALGOL 58, python2.3, win3.1, etc, code running, stable, and not a source of exploits? Or do you mean in the abstract, fairy-tale ending sense?
        dimitrios1 5y ago
        I think they meant in the tongue-in-cheek sense.
kukx 5y ago
The strict sql_mode is often manually disabled. I bet it is off on most shared hostings.
- evanelias 5y ago
  Sure, but that's not MySQL's fault. MySQL/Oracle has no control over what ill-conceived setting overrides are used by DBaaS providers or shared hosting providers. There are similarly dumb misconfigurations that can theoretically be made in most hosted open source software.
  - capitainenemo 5y ago
    Well, the things it would have been nice for MySQL to have done would have been not implementing it this way in the first place (yes, that's probably not Oracle's fault), and moving utf8 to utf8mb4 a bit more quickly. (good to know that's finally happening). Also, I gotta say, now I have to look into this strict mode and what else it breaks, when in this particular case a "don't silently truncate strings" flag would be preferable and probably lower impact, since "strict" vs "security flaw mitigation" is not quite the same thing (does such a more granular flag exist?). ... unless that's all the strict mode does. Hm, and since you brought this up elsewhere. Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
    - ars 5y ago
      > and moving utf8 to utf8mb4 a bit more quickly. (good to know that's finally happening). That happened in 2010 - I'm not sure 12 years counts as "finally happening", or just how much quicker you would like than 12 years ago? Other than a time machine, I'm not sure what else you would like them to do.
      - butlerm 5y ago
        They should have made "utf8" have UTF-8 semantics by default as soon as the defect was discovered, not twenty plus years later.
Ginden 5y ago
> Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. What happens if you upgrade from older version? Are defaults from previous version used, or is it default only on fresh installs?
- evanelias 5y ago
  See bottom of https://news.ycombinator.com/item?id=29910494 https://news.ycombinator.com/item?id=29910494 and my reply https://news.ycombinator.com/item?id=29910863 https://news.ycombinator.com/item?id=29910863
- johannes1234321 5y ago
  If you don't have an explicit value set in your my.cnf config and the client sends no explicit value upon connecting you get the current built in default of the version you are using. If you set something somewhere it will be respected.
purple_ferret 5y ago
Lots of people turn it off because Warnings are easier to ignore than Errors are

capitainenemo 5y ago

That's good to know, however I was encountering this MySQL truncation just last year in COTS software, so clearly there's a long tail. As a related example, log4j 1.x has been deprecated for years, but 100% of atlassian uses it with their own maintained version. In fact, when the log4j thing came up, we audited all our existing COTS/internal and virtually all of it was still on the "EOL" log4j 1.x. Since you're familiar with this. What happens with an existing upgrade - is strict mode enabled by a typical distro package upgrade or does it only impact new installs?

evanelias 5y ago
For sure, there's a huge long tail. However, if a company is using 6+ year old EOL software that has flaws, that's outside of the vendor's control. > What happens with an existing upgrade sql_mode is a setting, so it depends on whether the default is being overridden: e.g. does the my.cnf config file specify a value explicitly? Does your application's connection logic set a session-level override for each connection? DBaaS vendors add an additional wrinkle since they tend to have custom UIs and APIs for global settings. > typical distro package upgrade Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen!
- KronisLV 5y ago
  > Many Linux distros made it so that attempting to install "mysql" actually installs MariaDB instead, so when going this route, it's unfortunately always very hard to predict what you'll get or what will happen! MariaDB is indeed pretty pleasant to use and for most use cases can be a drop in replacement for MySQL, should you not want to use MySQL, but also wouldn't want/need/be able to use PostgreSQL or something else: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ I might be in the minority here, but i actually rather enjoy MySQL/MariaDB, since MySQL Workbench is still the best DBA software that i've used, especially because of the ER modelling and schema sync functionality, and their approach to schemas/users within a DB feels more reasonable than that of Oracle/PostgreSQL. That said, both PL/SQL and PL/pgSQL feel more powerful than whatever MySQL/MariaDB has. Also, in regards to MariaDB in particular, their versions have diverged from MySQL somewhat, so you might still want to look into the differences, if migrating over: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ As for predictability - nowadays i just run specific versions in containers (e.g. 10.7.1 or maybe 10.7 which corresponds to the patest PATCH release), to sidestep the distro differences altogether. It also lets me easily launch new instances, give resource limits to them, have multiple versions in parallel and mount the data directories wherever i need them, for easier backups etc.
  - brazzledazzle 5y ago
    Same. If I'm building and managing any special instances/VMs these days I avoid installing any packages but those required to run containers. There's certainly tradeoffs but it makes software management so much easier. Upgrading is a matter of stopping and starting a container using an upgraded image and if it's supported rolling back is just a matter of stopping the newer container and restarting the old. A container hosting service/platform and it's abstractions are better but if you have to run some one offs outside of that it's so much nicer to use containers. I call these pet cattle. I still bind mount directories though. Docker volumes seem really convenient but a few years back I dealt with helping out a coworker with some data loss (pre-volumes) in a container's filesystem so I have some trust issues with how they implement storage.
waynesonfire 5y ago
I'm going to use this as an opportunity to vent. Wouldn't it be a nice world where you can just declare something EOL and it disappears and somehow magically it's not a problem anymore for anyone end-to-end? Thing's just don't work that way. Like, suddenly, a product that was working perfectly fine for who knows how long suddenly needs to modified because some ass-hats thinks that EOL isn't suitable for production. There are a number of reasons to upgrade a dependency or a product; an EOL label shouldn't be a part of the calculus. EOL may imply certain conditions that may influence the decision to upgrade but for the sake of EOL you can eat a bag of dicks. Think harder why you're proposing an upgrade is needed and how it'll add value to the business. EOL software is perfectly fine especially because it's been battle tested. We also use log4j 1.x all over the place and I feel zero need to change that.
- dimitrios1 5y ago
  It is perfectly reasonable to continue to use EOL software, but you have to assume maintenance costs in that case. It would be irresponsible to continue to use EOL software as is. EOL is a perfectly natural concept. Nothing in this world lasts forever, including our machines, electronics, cars, batteries, lives, and yes, software.
  - rkagerer 5y ago
    If software lasted a lifetime, life would be so much easier.
    - kortex 5y ago
      For the end user, not the maintainers. Who's gonna sign up to keep that ENIAC, PDP-11, ALGOL 58, python2.3, win3.1, etc, code running, stable, and not a source of exploits? Or do you mean in the abstract, fairy-tale ending sense?
      - dimitrios1 5y ago
        I think they meant in the tongue-in-cheek sense.

kukx 5y ago

The strict sql_mode is often manually disabled. I bet it is off on most shared hostings.

evanelias 5y ago
Sure, but that's not MySQL's fault. MySQL/Oracle has no control over what ill-conceived setting overrides are used by DBaaS providers or shared hosting providers. There are similarly dumb misconfigurations that can theoretically be made in most hosted open source software.
- capitainenemo 5y ago
  Well, the things it would have been nice for MySQL to have done would have been not implementing it this way in the first place (yes, that's probably not Oracle's fault), and moving utf8 to utf8mb4 a bit more quickly. (good to know that's finally happening). Also, I gotta say, now I have to look into this strict mode and what else it breaks, when in this particular case a "don't silently truncate strings" flag would be preferable and probably lower impact, since "strict" vs "security flaw mitigation" is not quite the same thing (does such a more granular flag exist?). ... unless that's all the strict mode does. Hm, and since you brought this up elsewhere. Is this "strict by default" in MariaDB - which is what most (linux) people are (probably) thinking of.
  - ars 5y ago
    > and moving utf8 to utf8mb4 a bit more quickly. (good to know that's finally happening). That happened in 2010 - I'm not sure 12 years counts as "finally happening", or just how much quicker you would like than 12 years ago? Other than a time machine, I'm not sure what else you would like them to do.
    - butlerm 5y ago
      They should have made "utf8" have UTF-8 semantics by default as soon as the defect was discovered, not twenty plus years later.

Ginden 5y ago

> Silent truncation only occurs if strict sql_mode is disabled. Strict sql_mode has been the default since MySQL 5.7, released over 6 years ago. What happens if you upgrade from older version? Are defaults from previous version used, or is it default only on fresh installs?

evanelias 5y ago
See bottom of https://news.ycombinator.com/item?id=29910494 https://news.ycombinator.com/item?id=29910494 and my reply https://news.ycombinator.com/item?id=29910863 https://news.ycombinator.com/item?id=29910863
johannes1234321 5y ago
If you don't have an explicit value set in your my.cnf config and the client sends no explicit value upon connecting you get the current built in default of the version you are using. If you set something somewhere it will be respected.

purple_ferret 5y ago

Lots of people turn it off because Warnings are easier to ignore than Errors are