MySQL Bugs: #3820: Need Modern RDMBS Features

Bug #3820	Need Modern RDMBS Features
Submitted:	18 May 2004 20:49	Modified:	4 Aug 2005 23:05
Reporter:	Jimi Thompson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
I'm an experienced DBA for Oracle, Postgres, Informix, and Sybase. I've also worked with MSSQL (ewwww) and now I find myself in a shop where I'm the one person who understands datbases. Management of serveral MySQL databases has been passed to me and I wanted to give you some feedback about your product. I'm not very happy with this version (4.1) at all.

MySQL doens't support foreign keys out of the box. That really needs to change. Foreign keys all but put the "R" in RDBMS. Now I find out that I can't create views. Since I couldn't create a view, I thought I'd try to build a stored procedures. I can't do that either! I've also been having trouble finding out how to write triggers (e.g. things like having a new entry in a specific table generate an email), so I'm guessing that you don't support them either.

Your performance tuning options are also not what I'm accustomed to. I realize that most folks lack understanding about how their databases live and breathe, but I do not. Setting options to a livable default as part of the install package is acceptable. Removing that control from my hands completely by not including it is not a good idea. It means that you'll always be small database that lacks enterprise features. While we're on the issue of performance, I also don't appreciate being forced to create indexes on a field simply because it's a foreign key in another table. My experience has been that this creates additional and unnecessary load on the db server.

How to repeat:
Install product and hope for the best

Suggested fix:
1) Support foreign keys out of the box
2) Support for named views
3) Support for stored procedures with lesser access rights than the tables
4) Support for triggers
5) Performance tuning options
6) Dump unnecessary indexing

1) MySQL has had support for this for some time now: see http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

2-4) Already planned; See http://dev.mysql.com/doc/mysql/en/Roadmap.html

5) See http://dev.mysql.com/doc/mysql/en/Server_system_variables.html along with http://dev.mysql.com/doc/mysql/en/Server_status_variables.html

6) Already done in 4.1, though it is a recent change and is not in 4.1.1.

If you think that real databases need to have these features, why you are using mysql?! old programs in CLIPPER use dbaseIV and have just one problem (index) now that we don't have this problem why we can't survive with it?! By the way, mysql is fast on myisam and don't matther about others table types like innodb that support this features, if you don't like mysql see sqlite or postgresql.

Views, triggers, and stored procedures are all supported in MySQL 5.0.