My personal ToDo list for MySQL

First of all the good news: there are a lot of great things to come in MySQL! I recently found Ronald Bradford's article about the MySQL Users Group Meeting in Brisbane where he met with Brian Aker, MySQL's Director of Architecture. They discussed the features of MySQL 5.1 and also the future of MySQL beyond 5.1 - you should really read that ... there's great stuff to come!

However, there's still a lot of other work to be done. After I wrote about foreign key dependencies and Beat Vontobel joined in to extend my example to automatically remove foreign key dependencies with MySQL 5.0, we found a few new nasty bugs. So I decided to create a list of bugs (some of which are contributed by Beat ;-)) that I personally wish to have solved, together with feature requests that I think would be very important and would MySQL make an even better product. They relate to MySQL 5.0.18 and most of them also to MySQL 5.1.5 (there would be some more bugs for MySQL 5.0.18, but as they will - according to the Change History - be fixed in 5.0.19, I don't mention them here). So here they are - first the bugs.

Second CALL to procedure crashes Server (Bug report 17204)

This is the most recent bug that Giuseppe Maxia, Beat Vontobel, Roland Bouman and I found when we experimented with the key dependency issue. Unfortunately, there are still occasions when the bug occurs and occasions where it's not reproducable, but we hope that we can figure out why it sometimes works and sometimes not. The issue is that there's a Stored Procedure that works if it's called for the first time, but when it's executed a second time, the server crashes.

View with check option can be updated to an invalid value (Bug report 16813)

You can create a view on an underlying table and enforce conditions using the WITH CHECK OPTION clause. This is supposed to emulate check constraints, however - while it works with INSERT statements, it's still possible to generate invalid values with the UPDATE command.

CREATE TABLE xxx SELECT function_call() ... doesn't work in some cases (Bug report 14150)

Sometimes you would wish to create a new table based on a query that includes a call to a user defined function. This operation fails with the error message: ERROR 1100 (HY000): Table 'xxx' was not locked with LOCK TABLES

mysqldump problem with triggers (Bug report 16878)

I found out that there are problems if you dump triggers and try to re-import that dump. I didn't get into this problem any closer, but I believe that this is related to the bug report mentioned above.

Dumping views with --add-create-table generates a second set of DROP DATABASE/CRETE DATABASE commands (Bug report 17201)

If you dump databases that include views and you use the --add-create-table option, you get a dump file which looks something like the following:

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

-- then the tables and its data are created

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

-- then the views are created

This makes the creation of the tables and their data worthless.

NOW() optimized away to constant in BETWEEN ... AND ... in views (Bug report 15950)

MySQL treats NOW() as a constant at the time of creating a view.

CONVERT_TZ() not allowed in all places in views (Bug report 15153)

This bug has been "fixed" before, but CONVERT_TZ() still breaks in some places inside views.

Table and server crash on ALTER TABLE (Bug report 17001)

This one is really, really bad: ALTER TABLE COMMENT='...' destroyed a table beyond any repair possibilities.

Here are some (potential) feature requests. Some have already been filed, but there are some where I still have to look if there are feature requests in the Bug Tracking system. In the cases where I know the feature request IDs, they are of course indicated.

Inside a stored procedure, DATABASE() returns the database where the stored procedure resides instead of from where the stored procedure is called (Bug report/Feature request 14642)

Maybe a set of new functions like CURRENT_DATABASE() could provide the desired functionality.

DELETEs and UPDATEs don't work with certain subqueries

It's not possible to use DELETE and UPDATE commands, if they use subqueries that refer to the deleted or updated query - like this:

DELETE FROM table_name WHERE id = (SELECT col FROM table_name);
UPDATE table_name SET col1 = (SELECT col2 FROM table_name);
UPDATE table_name SET col1 = value WHERE id = (SELECT col2 FROM table_name);

I think, MySQL should first check the subquery and then independently execute the outer query.

Missing features for triggers

There is currently no privilege set for triggers (they require the SUPER privilege to be created or dropped) and there are no commands such as SHOW CREATE TRIGGER, SHOW TRIGGER STATUS (however, SHOW TRIGGERS works) and DROP TRIGGER IF EXISTS. The same set of commands exist for stored procedures and user defined functions, so it would be intuitive if they would work equally with triggers.

Accept NEW.x as INOUT parameters to stored procedures from within triggers (Bug report/Feature request 14635)

NEW.x in triggers is assignable - so there's absolutely no reason to not accept it as an INOUT parameter.

INSTEAD OF TRIGGERS (Bug report/Feature request 16525)

This is a nice feature that's implemented in SQL Server which can trigger a set of statements (a procedure) without executing the original command.

Output of original view creation command (I know that a highly-asked-for feature request exists, unfortunately I don't have its ID available)

Currently, the SHOW CREATE VIEW command returns something like this:

DROP VIEW IF EXISTS `test`.`v_checktest`;
CREATE ALGORITHM=UNDEFINED DEFINER=`mpopp`@`localhost` SQL SECURITY DEFINER VIEW `v_checktest` AS select `checktest`.`id` AS `id`,`checktest`.`val` AS `val` from `checktest` where ((`checktest`.`val` >= 1) and (`checktest`.`val` <= 5)) WITH CASCADED CHECK OPTION;

instead of the original view creation command:

CREATE VIEW v_checktest AS
SELECT id, val FROM checktest
WHERE val >= 1 AND val <= 5
WITH CHECK OPTION

At the moment, there's no way to get the original view creation command directly out of MySQL (it's only available via the .frm file in the data directory)

Error handling in stored procedures/user defined functions/triggers

I would appreciate enhanced error handling capabilities in stored procedures, user defined functions and triggers, as they exist in other database servers.

Stored procedures don't accept parameters inside a LIMIT clause (Bug report/Feature request 11918)

Parameters can be used on many places in queries inside a stored procedure, but unfortunately not in a LIMIT clause. This would for example be useful for paging.

Check constraints

MySQL does currently not provide check constraints, like this:

CREATE TABLE tt (
d1 INT UNSIGNED NOT NULL,
d2 VARCHAR(200) NOT NULL,
email VARCHAR(200) NOT NULL,
CHECK(d1 >= 1 AND d1 <= 5),
CHECK(d2 like 'A%'),
CHECK(email REGEXP '^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$'));

This feature is implemented in most other database systems.

Unclear error message with foreign key constraints (Bug report/Feature request 16290)

If you create a foreign key constraint which references to a column that's not
indexed, you get the error message:

ERROR 1005 (HY000): Can't create table '.\test\b.frm' (errno: 150)

If you don't know that referenced columns have to be indexed, it can leave you searching for a long time.

DROP DATABASE remains the privileges for the database unchanged

If you drop a database, all users remain their privileges for that database. If another user later creates a database with the same name, there might still be privileges for users of which the creator doesn't even know that they exist (could this even be considered as bug?).

Error messages from stored routines should name their source (Bug report/Feature request 17034)

Current error reporting doesn't indicate in which module it occured.

CHECKSUM TABLE should be a function instead of a command (Bug report/Feature request 17009)

It's not possible to use the result of CHECKSUM TABLE in SQL.

It's impossible to use cursors on dynamic SQL (Bug report/Feature request 17153)

MySQL should allow cursors on dynamic SQL (prepared statements).

And last but certainly not least - since Oracle has bought Innobase and with it the rights for the InnoDB storage engine, MySQL should enforce implementing foreign key constraints, ACID transactions and all the advanced features of InnoDB into storage engines that are completely under MySQL's control. This way, a full featured storage engine could even become the default storage engine (or should I say, the 'default default storage engine' since the default storage engine can be changed) of MySQL.

This has become quite a long list and certainly it's not yet complete as there will be issues that I don't know about (so feel free to add your suggestions and 'favourite feature requests'). But don't get me wrong: this should not tell you that MySQL is a bad product! Giuseppe, Beat, Roland and I do a lot of work with MySQL, that's why we might find bugs that are deep inside MySQL (that often only occur in very special situations that even we have to try hard to reproduce) that most users probably will never have to deal with.

However, I believe that these are very important issues and together with the new features that we can expect (mentioned at the beginning of this article), this would bring me much closer to my dream of the perfect database system.