When using MySQL 5.1 Enterprise after years of using other database products like Sybase, Infomix, DB2; I run into things that MySQL just doesn't do. For example, it can only generate an EXPLAIN query plan for SELECT queries.
What other things I should watch out for?
Full outer joins. But you can still do a left outer join union right outer join.
It doesn't cost a fortune. Try building a clustered website on multiple machines with multiple cores on an oracle database. ouch.
One thing I ran into late in a project is that MySQL date types can't store milliseconds. Datetimes and timestamps only resolve to seconds! I can't remember the exact circumstances that this came up but I ended up needing to store an int that could be converted into a date (complete with milliseconds) in my code.
MySQL's JDBC drivers cache results by default, to the point that it will cause your program run out of memory (throw up OutOfMemory exceptions). You can turn it off, but you have to do it by passing some unusual parameters into the statement when you create it:
Statement sx = c.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
sx.setFetchSize(Integer.MIN_VALUE);
If you do that, the JDBC driver won't cache results in memory, and you can do huge queries BUT what if you're using an ORM system? You don't create the statements yourself, and therefore you can't turn of caching. Which basically means you're completely screwed if you're ORM system is trying to do something involving a lot of records.
If they had some sense, they would make that configurable by the JDBC URL. Oh well.