MySQL Shortcomings

I have been using MySQL with ASP .NET at work for about the last 3 years now.

I've been stewing on this for quite some time, hoping MySQL would release changes and fixes. As it stands, I cannot recommend MySQL's use to anyone. Even simple sites have a way of becoming complex over time, so don't think you won't eventually run into these very serious problems.

As of this writing, I'm using 5.0 as it's what comes packaged with our linux system. The current version is 5.1. However, these problems or deficiencies are on both versions.

Here are the main issues that I run into, which are ridiculous oversights:

• (Update 2011.03.15) Use of temporary tables breaks replication (!!!). Reference link.
• (Update 2011.02.02) No Check constraints (!!!). Reference link.
• (Update 2011.02.02) No Full Outer join (!!!). Reference link.
• Foreign key cascades do not fire triggers (!!!). Reference link.
• You cannot disable triggers (!!!). Reference link.
• You can't use the same temp table twice in the same SQL statement (!!!). Reference Link.
• There is no method for using the results of stored procedures or functions as a result set for other queries (!!!):
• Can't create or insert into a temp table with sproc results: E.g. "create temporary table mytemptable call my_sproc();" doesn't work. Neither does "insert into mytemptable call my_sproc();"
• Can't use a stored procedure as a table alias: E.g. "select * from (call my_sproc());" doesn't work
• Can't create a view from stored procedure results: E.g. "create view myview as call my_sproc();" doesn't work
• There is no table type in MySQL, so functions can't return a table and also can't be used to pass result rows to other queries. E.g. "select * from my_func();" doesn't work
• These things are not explicitly stated in the manual, but questions regarding this kind of result reusability do come up from time to time. E.g. here.
• No array support. This means that you cannot pass an array to, nor receive an array from, a stored routine or function. Reference link.
• Stored functions can only return a single value. Not explicitly stated, but implicit in the stored function syntax. Reference link.
• Unintelligible error messages. Reference link (older but still indicative).
• No user-raisable error messages. Reference link.
• Integration with ASP: Entity Framework stored procedure inserts/updates/deletes are not configurable from the GUI -- they must be configured by hand. Reference link.

I can't even wrap my head around a company releasing a database and calling it production ready with some of these issues, particularly the first several half-dozen.

Note that there are work-arounds to nearly all of these issues, but they all entail a certain amount of extra programming, and configuration management (at least having to remember why you had to use those hacky, unclear SQL statements). If you use the database extensively, several of these problems can join forces to make the developer / admin experience very frustrating.

Personally, I will not choose MySQL 5.x again for any new projects. I'll even switch away from it on existing projects if given the opportunity. Hopefully MySQL 6 will be better.

So instead, I'll use SQL Server (even Express if I have to) when integrating with .NET applications. Otherwise, I'll use Postgres, which is free and open source. I'm sure those systems have their problems as well, but at least some of the more serious ones above are absent or mitigated.