Just implemented a small to medium accounting system in MySQL - about 50 tables and 160 stored procs and a couple of dozen views.
We actually switched from PDO to MySQLi. The error messages in PDO are broken for MySQL. You get very misleading reporting if newer MySQL versions throw an error PDO doesn't understand. Given how often MySQL throws errors, this is a real problem.
Also, PDO is not that portable. You have so many constants to set up with the different DB engines, that you end up wrapping the DB connection/transaction anyway.
One big advantage though, is that you will have to learn less when you switch DB.
That's what I thought. My experiences with trying to do serious DB work with MySQL have been so appalling that even at this late stage we are considering switching. Yep, rewriting 160 stored procs is preferable to this damn thing. We actually cancelled our Gold support contract with them, we were so frustrated.
1) I can crash the MySQL client in six lines of code, unless you start it with --no-auto-rehash.
2) On a 400 test test suite I get random errors about 25% of the time without transactions. That is, you get strange timing behaviour on 1/1600 queries. I had to wrap everything in a transaction.
3) Silent transaction dropping, which is especially a problem on migrations scripts :eek:.
4) Cannot recursively call procs.
5) Optimiser very confused by views. Rather unfortunate as that is our main way of refactoring repeated queries.
6) Pathalogically slow on common queries, especially in() and non-correlated subqueries (the optimiser thinks they are correlated).
7) Painfully slow cursors (we dropped them all anyway, so this is moot).
8) Broken OR clause! I kid you not :(. Had to rewrite OR queries with unions. This actually speeded them up. Uh?
9) Procs break replication every which way. Text based replication just doesn't work. Fixed in 5.1, but they just downgraded that to "alpha" :(.
10) No signal statement. Makes it a bit difficult to do any error handling at all. We had to emulate it by doubly writing to a unique field. That was a problem for replication. Just as well it wasn't working anyway...
In short, use PDO...with Postgres.