HN Theater @HNTheaterMonth

https://vimeo.com/43536445

I appreciate that it might sound like that to someone who hasn't used MySQL in production for 10+ years.

To start with, this is still true today: https://vimeo.com/43536445 Despite being 6 years old, strict mode is still required.

Anything prior to MySQL 5.7 will accept "0000-00-00 00:00:00" as a valid date, 5.7 will not (which is sane) however this means migrating from 5.6 -> 5.7 just got a little harder.

In fact it wouldn't validate /any/ date so it would assume every year was a leap year and febuary always had 29 days.

Regarding the query planner: https://bugs.mysql.com/bug.php?id=74602 This affected my prod mysql/zabbix installation https://support.zabbix.com/browse/ZBX-10652

Regarding performance: This is what I found from my own experience: I was given the task of testing the limits of MySQL, MySQL was the chosen technology and I was no involved in making that decision so- whatever. We were given 10 servers, with 40 cores (2014-2015~) 128G of DDR3/ECC and 8 SATA SSDs in RAID-0 with 1G of RAID cache for write-back. We managed to get MySQL to bottleneck pretty quickly, our queries involved a lot of binary data so we should have been raw IOPS bound, but we weren't we were memory bound. So we replaced the memory allocator with a faster one (jemalloc) and we get a 30% performance improvement. We suspected that the kernel sockets implementation was slowing us down so we compiled a custom "fastsockets" linux kernel. The improvement was around 4%, but we were bottlenecked on memory. After doing a full trace of what MySQL was doing we saw that InnoDB was spinning on a lock quite a lot. I asked if we could try other SQL solutions (MSSQL/PostgreSQL) Postgresql was first chosen because we could just install it, no license and no OS change... it was twice as fast as the optimised MySQL installation out of the box with a stock CentOS6 kernel.

We never even bothered testing MSSQL because PostgreSQL met our performance targets, we were now IOPS bound.

-- More anecdatum:

Regarding data consistency we (tried) to migrate to postgresql for performance reasons in 2014 (my previous company), and failed because MySQL had been corrupting our data very slowly and silently for many years (corrupting meaning not honouring NOT NULL, not honouring type safety, allowing invalid dates, inserting data on error) So far in that actually reimporting the output of `mysqldump` would not work.

⬐ e12e

> will accept "0000-00-00 00:00:00"

Isn't it? I thought that today()-(2018 years, 4 months and 10 days) would be approximately that date? Maybe you prefer +0000 vs just 0000?

'ISO 8601 prescribes, as a minimum, a four-digit year [YYYY] to avoid the year 2000 problem. It therefore represents years from 0000 to 9999, year 0000 being equal to 1 BC and all others AD. However, years prior to 1583 are not automatically allowed by the standard. Instead "values in the range [0000] through [1582] shall only be used by mutual agreement of the partners in information interchange."

To represent years before 0000 or after 9999, the standard also permits the expansion of the year representation but only by prior agreement between the sender and the receiver.[19] An expanded year representation [±YYYYY] must have an agreed-upon number of extra year digits beyond the four-digit minimum, and it must be prefixed with a + or − sign[20] instead of the more common AD/BC (or CE/BCE) notation; by convention 1 BC is labelled +0000, 2 BC is labeled −0001, and so on.'

https://en.m.wikipedia.org/wiki/ISO_8601

Now, if mysql accept, but can't store such a date, I understand that it's a problem.

⬐ dijit

MSSQL/Oracle/PostgreSQL/firebird all accept 0000-01-01 which is a valid date.

⬐ paulryanrogers

Technically true but still 0 is not yet a month or day of the month, so 0000-00-00 is still bad data

⬐ e12e

Ah, yeah. Was too focused on the year to consider the month/day!

Too long -- didn't watch.

Actually, I saw the original (57mins) a few months ago @ http://vimeo.com/43536445 "Rob Conery - Five Things You Didn't Know About PostGresSQL" recorded at NDC2012 (Norwegian Developers Conference).

Is this clip just the error reporting part? (In fairness, the youtube description does have a link to the original.)

Worth more views--this, or the original. I don't think I've ever heard anything bad about PostgreSQL. There must be some comparisons with the other viewpoint? I would be interested in seeing those too.

I recently watched this video which is mostly about Postgres but also pokes some fun at MySQL's weirdness. I didn't realise how scary MySQL can be

http://vimeo.com/m/43536445