Generally I really like MySQL, have used it for a number of different projects, and I will continue to do so. However, recently I have started working with larger databases and using some of the more advanced features and am running into some annoying problems.
Passing a table name as a parameter
The number one gripe I have actually turned out to be just as ugly using Postgres and Oracle as well, so I can't hold this one against MySQL.
I have found myself wanting to pass table names as parameters to stored procedures, but this makes the code really nasty with string concatenations as below:
DELIMITER $$
DROP PROCEDURE `GetMaxID`$$
CREATE PROCEDURE `GetMaxID`(IN pTableName CHAR(50))
BEGIN
SET @sql = CONCAT('SELECT MAX(ID) FROM ', pTableName, ' INTO @max');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END$$
Now imagine a complex stored procedure that needs to reference that table name a lot. Yick. I'm going to try and avoid this situation by putting the logic in the application until MySQL brings out something like a format string :P
I can't raise an error inside a stored procedure
This sucks. I do some sanity checking inside my stored procedure but there is no way to fire an error. Well,
no convenient/useful way anyway. Apparently this has been addressed in 5.2, which is not soon enough.
No foreign key enforcement in MyISAM
This is a shame, and I know I can choose InnoDB if I want FK support, but what I really want is the speed of MyISAM with the option of having my FK constraints enforced. I want to be able to turn it on, see how fast it is, and if it is too slow turn it off again.
Can't build cursors from dynamic SQL
This is annoying. The workaround is to
use a temporary table, but that is messier than I would like.
No Partition Pruning on Timestamps
This is pretty sucky and MySQL doesn't seem to be in a hurry to fix it. You can't get
partition pruning optimisation on timestamps - so partitioning is pretty mcuh useless. You have to
workaround it by using unsigned int's and UNIX_TIMESTAMP and FROM_UNIXTIME. You can partition on Date fields but they are twice the size, which is a big disadvantage for large databases.
Wasn't a real database before 5.1
MySQL just doesn't seem to have been a real database before version 5.1. I guess everything has to start somewhere, but this is a bit too recent for my liking and I seem to be hitting plenty of 5.0 installs that just don't have the advanced features I want.
Worst for security according to David Litchfield
At
AusCERT 2007 I asked
David Litchfield, database security ninja, which of the popular databases had the worst security - he said MySQL :( I tried to push for some specifics but he didn't give me any solid information.
