postgresql vs mysql

On 2/20/07, gustavo halperin <ggh.develop@gmail.com> wrote:

Hello

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

in MySQL if you have tables MyISAM they will ignore all rollback
commands you exexute, so you will have inconsistencies in databases...

worst, if you have tables MyISAM and tables InnoDB the first will
ignore all rollback commands and the laters won't...

of course the legendary speed in mysql can be obtained if you use
tables MyISAM :(

some other issues (some of them had been resolved in 5.x i don't know wich ones)

http://sql-info.de/mysql/gotchas.html

one last thing mysql team doesn't afraid to change behaviours between
minor releases, look at this thread

http://archives.postgresql.org/pgsql-general/2005-12/msg00487.php

http://dev.mysql.com/doc/refman/5.1/en/join.html
(Join Processing Changes in MySQL 5.0.12)

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

one last thing mysql team doesn't afraid to change behaviours between
minor releases, look at this thread

That is so true, all the differences between minor versions made creating
Lightning Admin for MySQL a pain in the rear...

After I did the port I really appreciated how clean PostgreSQL is.

--
Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL
http://www.amsoftwaredesign.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 15:25, gustavo halperin wrote:

Hello

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '0000-00-00'.

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-----END PGP SIGNATURE-----

I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:

"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."

mysql> use test
Database changed
mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.0.27-standard |
+-----------------+
1 row in set (0.00 sec)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 15:25, gustavo halperin wrote:

Hello

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '0000-00-00'.

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Adam Rich" <adam.r@sbcglobal.net> writes:

I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."

Really?

[tgl@rh2 ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql>

Note that this case is *not* testing whether mysql knows that
February has less than 31 days.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/20/07 23:59, Adam Rich wrote:

I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:

"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."

Only if you set sql modes STRICT_TRANS_TABLES and STRICT_ALL_TABLES
(which *still* allow bogus dates like 2007-02-00!!) or TRADITIONAL.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

It's (finally) a step in the right direction, but is really only a
"pretty please", since SQL modes are session-time changeable.

mysql> use test
Database changed
mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.0.27-standard |
+-----------------+
1 row in set (0.00 sec)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

On 02/20/07 15:25, gustavo halperin wrote:

Hello

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '0000-00-00'.

http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html

- ---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

- ---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF2+SQS9HxQb37XmcRAqh4AJwJz41yaTzIkqcAIr1wi7gK7J1QPACgvl07
fVNXVeoJo4vWhbIeGWM5MWs=
=Px12
-----END PGP SIGNATURE-----

On 2/20/07, gustavo halperin <ggh.develop@gmail.com> wrote:

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an error. Totally
annoying, I don't know if it was ever fixed. It seems that MySQL's parser
is generally weak at syntax validation in it's default configuration.

--
Chad
http://www.postgresqlforums.com/

This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY
to the sql_mode setting.

As Ron mentioned though that can be happily overridden on a per-session
basis so it's not as 'strict' as it makes out...

Chad Wagner wrote:

On Wednesday 21 February 2007 1:10:41 am Tom Lane wrote:

"Adam Rich" <adam.r@sbcglobal.net> writes:

I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
"As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively."

Really?

[tgl@rh2 ~]$ mysql test

... snip ...

It gets better: The problem is not just feb 35, it's also that it doesn't warn
you that it didn't like the input format:

[head sep-head 08:49]$ mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.33 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test ( td DATE );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

mysql> insert into test values ('17-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.01 sec)

mysql> insert into test values ('2007-02-19');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 2007-02-19 |
+------------+
3 rows in set (0.00 sec)

mysql> insert into test values ('2007-02-35');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| td |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 2007-02-19 |
| 0000-00-00 |
+------------+
4 rows in set (0.00 sec)

mysql>

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

On Wed, Feb 21, 2007 at 08:54:30AM -0500, Jan de Visser wrote:

It gets better: The problem is not just feb 35, it's also that it doesn't warn
you that it didn't like the input format:

Actually it did, sort of.

mysql> insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.07 sec)

                            ^^^^^^^^^
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'td' at row 1 | 
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

Not as good as "ERROR: hey bonehead, there ain't no such date" but
at least it's something :-)

--
Michael Fuhr

On Tue, 2007-02-20 at 15:25, gustavo halperin wrote:

Hello

I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

My personal peeves:

It's got a query parser that's dumb as a brick. Basically it seems to
work like this: Got an index? Cool, use it. Complex queries quickly
bog down on large data sets in MySQL. Just read the database forums at
phpbuilder.com or anywhere else that people use mysql a lot and you'll
see request after request to fix up a query performance-wise that
PostgreSQL would run with decent speed. Further, the output of Explain
is damned near useless.

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

It swallows column level foreign key contraints and does nothing with
them, no errors nothing, even if you're defining innodb tables. I.e.
this produces not errors:

mysql> create table a (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create table b (a_id int references a(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values (1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into b values (1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into b values (2);
Query OK, 1 row affected (0.03 sec)

That last statement should fail. Or the creation of table b should
throw a warning. Or something.

This is with 5.0.19.

So, innodb tables pay for the sins of the fathers (i.e. myisam tables)
and by extension, so do you.

My main gripe about MySQL is that it teaches you bad habits. It plays
loose and fast with your data, and teaches you to do that too.

If it was a lot faster than PostgreSQL (like it was back in the days of
7.1 or 7.2) it might be worth the effort to overcome its shortcomings,
but it's not.

Scott Marlowe wrote:

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

Forgive a dumb question: What does postgresql do with ALTER TABLE?
What sort of modifications do not require time proportional to the
number of rows in the table?

Jack Orenstein

At 07:31 PM 2/21/2007, Chad Wagner wrote:

On 2/20/07, gustavo halperin
<<mailto:ggh.develop@gmail.com>ggh.develop@gmail.com> wrote:
I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an
error. Totally annoying, I don't know if it was ever fixed. It
seems that MySQL's parser is generally weak at syntax validation in
it's default configuration.

** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and
great performance. BUT, when you start to get to the details, too
often you'd find that some features aren't so compatible with others
or take a bit (lot?) more effort to get working properly.

Want transactions? Use innoDB. Want to restore a multi-gigabyte
database fast from backups, sure use MyISAM (too many people seem to
have probs doing that with innoDB).

Want foreign keys to work? Use innoDB. MyISAM tables allow you to
specify foreign keys but ignores AND forgets them.

You can mix MyISAM tables with innoDB tables in the same database.
That's a minus.

Want to back up a consistent snapshot of the database AND still have
users using the database live? Use only InnoDB tables. Because to
ensure consistency when dumping MyISAM tables you should lock all the
tables involved. You still want a live consistent backup of a
database with some MyISAM tables? Here's a method I suggested: use
multiple MySQL servers with replication - do the backup snapshot off
a slave, while users are using the master (or other slaves). If
anyone has better ideas do let me know :).

Do not use innoDB on a filesystem that does not support files > 2GB
in size. Though MySQL +innoDB supports a configurable like
"autoextend:max:1000M", this only works if you using a single shared
tablespace, doesn't work if you are using one
"innodb_file_per_table". BUT if you are using a single shared
tablespace be aware that you can't easily shrink such tablespaces and
reclaim unused space.

Too many IFs, BUTs, ONLYs, etc.

** D'oh level release gotchas
Example: Before MySQL 5.0.13, GREATEST(x,NULL) and
LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.13,
both functions return NULL if any argument is NULL, the same as
Oracle. This change can cause problems for applications that rely
on the old behavior.

Or release 5.0.19: The InnoDB storage engine no longer ignores
trailing spaces when comparing BINARY or VARBINARY column values.
This means that (for example) the binary values 'a' and 'a ' are now
regarded as unequal any time they are compared, as they are in MyISAM
tables. (Bug#14189)

** Commercial/strategic gotchas
Oracle owns the companies that make the transactional backends for
MySQL (innoDB, sleepycat).

** Conclusion
In my opinion, if you don't have anything that specifically requires
MySQL, but where MySQL is suggested, it's better to use Postgresql.

Not saying Postgresql is perfect - rather that MySQL makes Postgresql
look really good.

Unfortunately, I have to deal with MySQL at work. *sigh*.

Regards,
Link.

On Wed, 2007-02-21 at 10:12, Jack Orenstein wrote:

Scott Marlowe wrote:

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really
big table, you need that much space free on the drive to alter the table
for the rewrite that's going to take place.

Forgive a dumb question: What does postgresql do with ALTER TABLE?
What sort of modifications do not require time proportional to the
number of rows in the table?

It's an interesting subject, and it's not a dumb question. In
PostgreSQL, indexes live in another file than the table. In MySQL they
are part of the main table file with myisam tables. I don't know what
innodb does in this regard.

The only thing I can think of that rewrites a whole postgresql table
would be reindexing it, or an update without a where clause (or a where
clause that includes every row). Normal operations, like create index,
add column, drop column, etc do not need to rewrite the table and happen
almost instantly.

For instance, on a table with about 30 columns and 100,000 rows, I can
add a column this fast:

alter table brs add column a int;
ALTER TABLE
Time: 57.052 ms

alter table brs rename column b to c;
ALTER TABLE
Time: 33.281 ms

alter table brs drop column c;
ALTER TABLE
Time: 31.065 ms

Of course, mvcc (which both postgresql and innodb use) have other
issues, like doubling the table size if you update every row until the
dead tuples can be reclaimed.

At 12:02 AM 2/22/2007, Scott Marlowe wrote:

You can't change a table in any way without rewriting the whole thing,
resulting in a very long wait and a complete table lock on any alter
table action on big tables. Don't forget that if you've got a really

Oh yeah, that reminds me. "rewriting the whole thing" means in most
cases the _entire_ table is temporarily _duplicated_ (with all the
associated increased space requirements)![1]"If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table"

WORSE: This happens if you are creating or deleting indexes, or even
changing a column definition!

So say you have a 40GB table, and have 30GB free space. Life is good
right? Then someone makes a reasonable request - Big Boss wants an
important report sped up, and it turns out you just need to create an
index. Enjoy :).

Running low on space and think you can get more space by deleting
some unused indexes? Probably not a good idea!

And even if disk space is cheap, IO bandwidth usually isn't...

Regards,
Link.

[1]: "If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table"
always creates a temporary table"

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

MySQL: the PHP of databases.

It's got a query parser that's dumb as a brick.

While we're on this topic... I have a question on these series
of queries:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)

-- Query C
select count(*) from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null

I believe they all achieve the same thing. i.e. How many
customers have never placed an order? I ran these 3 on
MySQL & PG with the following results:

Query A: MySQL=4.74s PostgreSQL=4.23s
Query B: MySQL=4.64s PostgreSQL=?????
Query C: MySQL=5.07s PostgreSQL=3.39s

MySQL's time is pretty consistent for all 3. As you said,
the output from explain is pretty useless so there's not
much else to look at.

PostgreSQL runs A&C slightly faster, which I expected.
However, waiting for query B exceeded my patience and
I had to abort it. The explain output is below, is this
result due to some incorrect setting?

benchdb=# explain select count(*) from customers c
benchdb-# where customer_id not in ( select customer_id from orders);
QUERY PLAN
------------------------------------------------------------------------
------------
Aggregate (cost=16406564027.00..16406564027.01 rows=1 width=0)
-> Seq Scan on customers c (cost=41578.00..16406562777.00
rows=500000 width=0)
Filter: (NOT (subplan))
SubPlan
-> Materialize (cost=41578.00..69391.00 rows=2000000
width=4)
-> Seq Scan on orders (cost=0.00..31765.00
rows=2000000 width=4)
(6 rows)

On Wed, 2007-02-21 at 10:54, Adam Rich wrote:

It's got a query parser that's dumb as a brick.

While we're on this topic... I have a question on these series
of queries:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)

-- Query C
select count(*) from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null

I believe they all achieve the same thing. i.e. How many
customers have never placed an order? I ran these 3 on
MySQL & PG with the following results:

Query A: MySQL=4.74s PostgreSQL=4.23s
Query B: MySQL=4.64s PostgreSQL=?????
Query C: MySQL=5.07s PostgreSQL=3.39s

MySQL's time is pretty consistent for all 3. As you said,
the output from explain is pretty useless so there's not
much else to look at.

PostgreSQL runs A&C slightly faster, which I expected.
However, waiting for query B exceeded my patience and
I had to abort it. The explain output is below, is this
result due to some incorrect setting?

Nope, more like incorrect usage / inability to optimize by postgresql
due to architecture. The B query (like the B arc) is a bad choice here
because PostgreSQL has to actually create a giant OR list of all the
customer_ids from order.

But the queries I was referring to were more along the lines of multiple
level subselect queries with lots of aggregation on the outside, the
kind used for business intelligence reporting.

There might be some optimization trick for the B query I'm not familiar
with (cause every time I turn around, Tom has gone and made the query
optimizer smarter) but I haven't heard of it.

Adam Rich wrote:

-- Query A
select count(*) from customers c
where not exists ( select 1 from orders o
where o.customer_id = c.customer_id )

-- Query B
select count(*) from customers c
where customer_id not in ( select customer_id from orders)

-- Query C
select count(*) from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null

I believe they all achieve the same thing.

I think not. When using

INSERT INTO customers VALUES (1);
INSERT INTO customers VALUES (2);
INSERT INTO customers VALUES (NULL);

and

INSERT INTO orders VALUES (1);
INSERT INTO orders VALUES (3);
INSERT INTO orders VALUES (NULL);

I get

Query A: 2
Query B: 0
Query C: 3

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 2/21/07, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote:

At 07:31 PM 2/21/2007, Chad Wagner wrote:

On 2/20/07, gustavo halperin
<<mailto:ggh.develop@gmail.com>ggh.develop@gmail.com> wrote:
I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?

How about the fact that MySQL accepts the following query as legal:

SELECT foo, bar, COUNT(*)
FROM baz
GROUP BY foo

And produces, naturally, an unexpected result instead of an
error. Totally annoying, I don't know if it was ever fixed. It
seems that MySQL's parser is generally weak at syntax validation in
it's default configuration.

** syntax/misc gotchas

Too many. See other emails. Or search for MySQL gotchas.

** Feature gotchas
At first look MySQL seems to have all sorts of nice features and
great performance. BUT, when you start to get to the details, too
often you'd find that some features aren't so compatible with others
or take a bit (lot?) more effort to get working properly.

boy, you hit the nail on the head. mysql supports views and
subqueries, but apparently not at the same time. also, complex views
(such as you can write without subqueries) tend to run slower than
identical counterpart in .sql.

mysql supports pl/psm (yay) but unfortunately no FOR loops (yikes).

the mysql planner is an unpredictable thing, producing huge surprises
to the upside and the downside...however taken as a whole it is a
completely inferior planner.

merlin