woensdag 28 september 2016

Why I prefer PostgreSQL over MySQL: functions either work, or they don't

Today I got one of those WTF moments.

From the MySQL manual:

ALTER USER was added in MySQL 5.6.6. However, in 5.6.6, ALTER USER also sets the Password column to the empty string, so do not use this statement until 5.6.7.
That's MySQL speak for "yeah, we released something that can break your application and potentially open it up to hackers, so uhm... just don't use it, ok?It'll be fine..."

In PostgreSQL, features are either completed before release, or they are made safe by giving an error message when the incomplete part is accessed.

These hidden "features" that can cost hours of debugging and restoring and, potentially, a large data-leak, simply don't exist in PostgreSQL.

donderdag 25 februari 2016

Facebook... yeah...

So. Yeah... facebook eh?

You can use it to login on other websites.


Except... facebook has a bug in its authentication, which means that roughly 2% of times they don't send the required data in their response and sometimes they will send garbage instead.

But hey, it's facebook, good-old Zucky will fix it in no time, right?

Not really. Reports of this bug started back all the way to 2011. Five years now...

So, yeah. Facebook...

zaterdag 20 februari 2016

SQL: Wat te doen met een trage OR query?

Een query die in de WHERE clausule een OR gebruiken in de vorm van WHERE foo=1 OR foo = 5 worden vaak traag omdat de database geen index kan gebruiken om aan de OR te voldoen.

De query is wel snel  als je alleen zoekt naar foo=1 of alleen zoekt naar foo=5. De oplossing is dan ook simpelweg: UNION.

Een UNION voegt de uitkomst van twee queries samen en twee snelle queries samenvoegen gaat sneller dan één trage query uitvoeren.

Dus iets als dit:

SELECT * FROM foo WHERE bar=1 or bar=5;

wordt iets als: