Goodbye MongoDB, Hello PostgreSQL

Re:Goodbye MongoDB, Hello PostgreSQL by pizza234 on Tuesday March 10, 2015 @01:18PM (#9178990)

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 :-)

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by solutionyogi on Tuesday March 10, 2015 @01:23PM (#9179049)

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?

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by pizza234 on Tuesday March 10, 2015 @01:35PM (#9179152)

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".

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by zrail on Tuesday March 10, 2015 @01:28PM (#9179085)

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)

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by morgo on Tuesday March 10, 2015 @01:34PM (#9179143)

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-variabl...

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by beering on Tuesday March 10, 2015 @01:36PM (#9179163)

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.

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by spitfire on Tuesday March 10, 2015 @02:22PM (#9179548)

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.

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by pnathan on Tuesday March 10, 2015 @01:30PM (#9179095)

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.

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by imron on Tuesday March 10, 2015 @07:29PM (#9181489)

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).

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by morgo on Tuesday March 10, 2015 @01:30PM (#9179105)

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/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-transitionin...

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by YorickPeterse on Tuesday March 10, 2015 @02:47PM (#9179743)

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"?

[ Parent | on HN ]

Re:Goodbye MongoDB, Hello PostgreSQL by vogan42 on Tuesday March 10, 2015 @01:19PM (#9179012)

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

[ Parent | on HN ]