4 ms·

There is a mistake in the article, due to the OP not knowing an arguably basic notion about MySQL. > when defining a field as int(11) you can just happily inse

There is a mistake in the article, due to the OP not knowing an arguably basic notion about MySQL. > when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it. this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields: mysql> create table example ( `number` int(11) not null ); mysql> insert into example (number) values ('wat'); Query OK, 1 row affected, 1 warning (0,00 sec) mysql> SET sql_mode = 'STRICT_ALL_TABLES'; mysql> insert into example (number) values ('wat'); ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1 There are certainly advantages in choosing PostgreSQL over MySQL... this is just not one of them :-)

vogan42 12y ago
Perhaps the author was aware, but found it unacceptable that clients could choose their own sanity ?
solutionyogi 12y ago
Personally, I would not use a database which is 'lax' by default instead of 'strict'. What other choices have they made which I need to learn OR it will bite me big in Production?
- zrail 12y ago
  Example: Inserting strings into a varchar() that are longer than the limit will silently truncate them. Example: The TIMESTAMP type defaults to the current time instead of null. There are more, but those are the two that have bitten me in the past (and no I do not use MySQL at all if I can help it)
  - morgo 12y ago
    See my other comment in this thread RE: string truncation (strict sql mode will default on in MySQL 5.6/5.7). It is possible to change timestamps to default to Null. For backwards compatibility, the previous behavior defaults: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp http://dev.mysql.com/doc/refman/5.7/en/server-system-variabl...
- pnathan 12y ago
  In a previous life, the lax by default approach of MySQL bit us hard on a regular basis; a number of discrepancies vs. reasonable expectation were had. At the time I left, they were working on migrating toward Postgres, and reports suggest they are extremely happy with pg.
- pizza234 12y ago
  It's not so easy. MySQL has a large legacy of being used as a very-immediate-although-somewhat-toy database at its roots. For example, in absolute terms, I would find much more troubling the usage of non-transactional tables, justified by meaningless microbenchmarks, which has been somewhat common for some time. Nowadays MySQL is definitely reliable, and it has a much more expert surrounding culture than the past, so if a person/company is willing to put time and knowledge, it's a reasonable choice. I don't find automatic conversion so damaging that people should stay away just because it's default. When you reach some level, you definitely need to have a relatively intimate knowledge of your tools, and at such point, one is far from the "defaults".
  - sre_ops 12y ago
    MySQL may have a legacy of crappy code, however it is works quite well. MongoDB has no legacy but a pile of barely working crappy code. https://jira.mongodb.org/browse/PYTHON-532 https://jira.mongodb.org/browse/PYTHON-532
- beering 12y ago
  You can subtract two DATETIME columns in MySQL and it will not generate any warnings. It will give you an answer that probably is within the ballpark of the actual time difference but isn't the actual time difference.
- spitfire 12y ago
  If you feed MYSQL an invalid date for a date/timestamp field it will quietly insert 0000-00-00 and produce a warning, not an error.
- imron 12y ago
  utf8 is not really utf8 and doesn't support 4 byte characters that exist outside the BMP (you'll need the separate utf8mb4 type for that, assuming you're not running on a legacy version that doesn't support that either).
morgo 12y ago
This is indeed the case. To clarify a little: - STRICT_TRANS_TABLES is on by default for "new installations" starting from MySQL 5.6 (2013). What this means is that the bundled config files all turn it on. - Starting from MySQL 5.7, it is a compiled default, along with several other more "strict" options. Effectively making it default to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER. I have some sample configuration files to make 5.6 behave like 5.7's strictness. For example: https://github.com/morgo/mysql-compatibility-config/blob/master/mysql-56/mysql-57-proposed.cnf https://github.com/morgo/mysql-compatibility-config/blob/mas... It does make upgrades harder, so some applications may also need to use a whitelist/blacklist approach to transition: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...
YorickPeterse 12y ago
When buying a car, would you prefer for it to be safe by default or only after you've enabled half a dozen features such as "air bags" and "seatbelts"?

vogan42 12y ago

Perhaps the author was aware, but found it unacceptable that clients could choose their own sanity ?

solutionyogi 12y ago

Personally, I would not use a database which is 'lax' by default instead of 'strict'. What other choices have they made which I need to learn OR it will bite me big in Production?

zrail 12y ago
Example: Inserting strings into a varchar() that are longer than the limit will silently truncate them. Example: The TIMESTAMP type defaults to the current time instead of null. There are more, but those are the two that have bitten me in the past (and no I do not use MySQL at all if I can help it)
- morgo 12y ago
  See my other comment in this thread RE: string truncation (strict sql mode will default on in MySQL 5.6/5.7). It is possible to change timestamps to default to Null. For backwards compatibility, the previous behavior defaults: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp http://dev.mysql.com/doc/refman/5.7/en/server-system-variabl...
pnathan 12y ago
In a previous life, the lax by default approach of MySQL bit us hard on a regular basis; a number of discrepancies vs. reasonable expectation were had. At the time I left, they were working on migrating toward Postgres, and reports suggest they are extremely happy with pg.
pizza234 12y ago
It's not so easy. MySQL has a large legacy of being used as a very-immediate-although-somewhat-toy database at its roots. For example, in absolute terms, I would find much more troubling the usage of non-transactional tables, justified by meaningless microbenchmarks, which has been somewhat common for some time. Nowadays MySQL is definitely reliable, and it has a much more expert surrounding culture than the past, so if a person/company is willing to put time and knowledge, it's a reasonable choice. I don't find automatic conversion so damaging that people should stay away just because it's default. When you reach some level, you definitely need to have a relatively intimate knowledge of your tools, and at such point, one is far from the "defaults".
- sre_ops 12y ago
  MySQL may have a legacy of crappy code, however it is works quite well. MongoDB has no legacy but a pile of barely working crappy code. https://jira.mongodb.org/browse/PYTHON-532 https://jira.mongodb.org/browse/PYTHON-532
beering 12y ago
You can subtract two DATETIME columns in MySQL and it will not generate any warnings. It will give you an answer that probably is within the ballpark of the actual time difference but isn't the actual time difference.
spitfire 12y ago
If you feed MYSQL an invalid date for a date/timestamp field it will quietly insert 0000-00-00 and produce a warning, not an error.
imron 12y ago
utf8 is not really utf8 and doesn't support 4 byte characters that exist outside the BMP (you'll need the separate utf8mb4 type for that, assuming you're not running on a legacy version that doesn't support that either).

morgo 12y ago

This is indeed the case. To clarify a little: - STRICT_TRANS_TABLES is on by default for "new installations" starting from MySQL 5.6 (2013). What this means is that the bundled config files all turn it on. - Starting from MySQL 5.7, it is a compiled default, along with several other more "strict" options. Effectively making it default to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER. I have some sample configuration files to make 5.6 behave like 5.7's strictness. For example: https://github.com/morgo/mysql-compatibility-config/blob/master/mysql-56/mysql-57-proposed.cnf https://github.com/morgo/mysql-compatibility-config/blob/mas... It does make upgrades harder, so some applications may also need to use a whitelist/blacklist approach to transition: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...

YorickPeterse 12y ago

When buying a car, would you prefer for it to be safe by default or only after you've enabled half a dozen features such as "air bags" and "seatbelts"?