[plug] MySQL for PostgreSQL users

[Peter Wright]

On 17/09 11:19:38, Tim Bowden wrote:
> Hi all,
> I've got to use MySQL instead of my preferred PostgreSQL (wordpress
> dependency.

Funny - I was about to say that Wordpress runs on PostgreSQL (I
genuinely thought that was the case), but it turns out I'm completely
and utterly wrong:

http://wordpress.org/support/topic/549/page/2

I can understand why the Wordpress developers would put it into the
too-hard-and-we-don't-care-enough basket, but yeah, it's annoying.

> Why oh why can't open source projects be database agnostic.  We have
> an SQL standard for a reason),

*An* SQL standard? :-)

And every major database vendor implemented their own extensions/
violations of the SQL standard for a reason, and then once they'd done
that they couldn't easily break backwards compatibility with previous
versions of their own software merely to provide greater compatibility
with a standard that not terribly many people care about. :-/

Yes, it sucks.

> but I'm not quite understanding the MySQL user/security model yet.

Join the club :-). It's never made a lot of sense to me either.

I think Daniel's response is pretty accurate as far as I can recall,
but I try to do as little as possible with MySQL and just stay the
hell away once I've got the damn thing working.


The four commands I use most at the MySQL prompt are:

  > show databases;              -- equivalent to 'psql -l'
  > connect database_name;       -- equivalent to 'psql database_name'
  > show tables;                 -- equivalent to psql's \dt (once
                                    connected to a DB)
  > desc table_name;             -- equivalent to psql's \d table_name

One thing worth keeping in mind with MySQL is that (at least for
*some* versions, not sure if it still does this in the latest version)
it tries to be clever and ends up violating the Principle of Least
Astonishment.

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.

> Regards,
> Tim

Pete, now having flashbacks to all the other reasons he hates MySQL.
-- 
"A retrospective of the 'good 'ole days' of the Net when men were real
men, all computers ran Unix, and the Web seemed like a good idea."  
	-- third in a list of ideas for articles about the history of
	the Net by Paul Stephanouk