[plug] MySQL for PostgreSQL users

[Daniel Pittman]

Tim Bowden <tim.bowden at westnet.com.au> writes:
> On Wed, 2008-09-17 at 13:44 +0800, Peter Wright wrote:

[...]

>> For example, I ran into a really irritating problem recently where
>> doing something like:
>> 
>>   $ mysql -u username -p -h localhost
>> 
>> (ie. connect to localhost as user "username", ask for password) would
>> throw a bizarre error about not being able to find the appropriate
>> mysql.sock file (used for Unix socket connections, which are
>> completely different to TCP connections - and this particular MySQL
>> was misconfigured so there was no .sock file available).
>> 
>> It turned out that that version of MySQL would try to be "clever"
>> and, if you supplied "localhost" as the host, would silently decide
>> that you must have really wanted to do a Unix socket connection. So
>> it would try to do that and of course fail, leaving me utterly
>> confused.
>> 
>> Only if you supplied '-h 127.0.0.1' instead of '-h localhost' would
>> it do a proper TCP connection. Grrrrrrr.
>
> Oh, that is evil.  Thanks for the warning.  It's the sort of snafu
> that can waste hours.

Oh, that sort of SNAFU is par for the course with MySQL; keep in mind
that it will silently corrupt ^W modify data, such as truncating string
input or transforming invalid string content into '0', in many cases.

You can set an option, per connection, to ask MySQL to actually be
standards complaint, which I advise.  This makes many, but (IIRC) not
all, of these evil, hateful things move from warnings[1] to errors.

>> Pete, now having flashbacks to all the other reasons he hates MySQL.
>
> And I'm learning my own.  The pagination of output in the mysql client
> is (more or less) nonexistent, unlike in psql where it (more or less,
> take your pick) works beautifully.

The worst part is that the supported subset of SQL varies between the
different storage engines, or possibly that there isn't a MySQL using
application out there that actually /works/ with strict mode enabled...

Regards,
        Daniel

Footnotes: 
[1]  ...which nothing in the universe ever checks, and heck, what are
     you going to do?  Alert the user that your database didn't store
     what they entered, but rather something semi-random?