Last night happened what I've always been afraid of. It started when I tried to update PostgreSQL to the latest version, something that I'm not very familiar with (so to be honest - it was completely my own fault). I also have to admit that I'm not a Linux guru - I like Linux and I can work with it quite comfortably, but when it comes to problems that I didn't expect, I sometimes end up trying and messing things up instead of really keeping everything under control.
First I tried to update the server with the .tar.gz file and followed the instructions from the PostgreSQL documentation. The installation process seemed to work fine, but when I thought, the installation was done, I didn't manage to start the server with the new version.
Next I downloaded the .rpm file from the SuSE 10.1 development repository. That's where the real problems started to arise - there were dependencies over and over and I tried to solve them one by one until I found out, that more things were broken than repaired. Finally I gave up upgrading PostgreSQL - MySQL still worked and was completely unaffected by my actions. But the disaster was finished when I realized that I couldn't connect via SSH anymore.
So I had no other choice than to start the re-installation from the customer interface of my server provider. It took several hours until the installation was finished with a completely fresh server (unfortunately, still a SuSE 9.0). Nevertheless, the rest was easy - MySQL was quickly set up again (the 5.0 server through an .rpm binary, the 5.1 server through the .tar.gz file). I was clever enough to backup the user's data shortly before the server crashed, so I could easily recover both MySQL servers with hardly any data loss (how great would it be if I had a second server with a replication system ;-)). So in the end, not much damage was done, except a few hours of downtime and for the moment there's no more PostgreSQL server.
My server provider informed me that very soon (probably next week) they will provide a new SuSE 10.0 image. So probably, I'll soon make an update, but in contrast to the situation last night, I'll be prepared well and everything should run quite quickly and smoothly. And maybe, I'll also get the PostgreSQL server up running again with at least an 8.0 version.
Sunday, February 26, 2006
Friday, February 24, 2006
That's really tough!
I recently registered myself for the MySQL User Conference from 24th to 27th April in Santa Clara, California.
Now I try to make a plan which sessions to attend. That's an extremely tough task! There are so many interesting sessions, held my many people who I'm really excited to meet - so it's really hard to decide which ones to drop.
I really need a few clones of myself!
Now I try to make a plan which sessions to attend. That's an extremely tough task! There are so many interesting sessions, held my many people who I'm really excited to meet - so it's really hard to decide which ones to drop.
I really need a few clones of myself!
Free VMWare server to expire in July?
Maybe it's not a typical article for a MySQL blog, but since VMWare might be an interesting product for people who do testings on different platforms, I feel that it's not completely wrong here.
Today I've installed the new free (like beer) VMWare server for Linux. Installation worked fine, but as I looked into the "About VMWare" dialog, I found a line that the license expires 13th July 2006.
Is there a time limitation in the free VMWare server?
Today I've installed the new free (like beer) VMWare server for Linux. Installation worked fine, but as I looked into the "About VMWare" dialog, I found a line that the license expires 13th July 2006.
Is there a time limitation in the free VMWare server?
Monday, February 20, 2006
To separate or to not separate
First I have to correct myself. In my last blog article I wrote about a bug (17564) that MySQL remains database privileges after a database has been renamed. This behaviour is described in the manual, so it's no bug.
However, this raises an interesting question. Should data definition statements also affect the privilege settings? If you for example drop a database or a table, the privileges still remain for this object. Or if you rename a database or a table, the privileges will not be set to the new name. Should the definition statements and the privilege settings be separated, or handled together?
I would be interested, what other people think about this, what SQL standard suggests and how other RDBMS handle this. Is this the default behaviour among the RDBMS, or is it a Gotcha?
Could it be a good idea to add an option to the data definition statements, something like (just an idea) PRESERVE PRIVILEGES or MODIFY PRIVILEGES?
I would really appreciate your thoughts.
However, this raises an interesting question. Should data definition statements also affect the privilege settings? If you for example drop a database or a table, the privileges still remain for this object. Or if you rename a database or a table, the privileges will not be set to the new name. Should the definition statements and the privilege settings be separated, or handled together?
I would be interested, what other people think about this, what SQL standard suggests and how other RDBMS handle this. Is this the default behaviour among the RDBMS, or is it a Gotcha?
Could it be a good idea to add an option to the data definition statements, something like (just an idea) PRESERVE PRIVILEGES or MODIFY PRIVILEGES?
I would really appreciate your thoughts.
Take care with RENAME DATABASE
MySQL 5.1.7 introduces a new RENAME DATABASE command. Today, I've updated my BitKeeper MySQL 5.1 version, so I had the opportunity to already test this command. I found two bugs, which will definitely be in 5.1.7, as the development tree has already switched to 5.1.8:
Bug 17564: the RENAME DATABASE command doesn't change the privileges, so privileges might be lost, when the database name is changed.
Bug 17565: all events that were stored in the database will be lost after the database is renamed.
Bug 17564: the RENAME DATABASE command doesn't change the privileges, so privileges might be lost, when the database name is changed.
Bug 17565: all events that were stored in the database will be lost after the database is renamed.
Saturday, February 18, 2006
Outdated versions on dev.mysql.com
I'm a guy who very often watches if there are new versions of MySQL related programs (the server, the client programs and the APIs). There are good ways to quickly receive notifications if new versions come out, the best might be the Announcement Mailing List.
The dev.mysql.com page also lists the most current versions of the relevant products - or lets better say - it should list the most current versions. Unfortunately, it often takes quite a long time until this list is updated, when a new version comes out - for example:
MySQL MigrationToolkit is listed as 1.0.22 beta, although version 1.0.23 RC is out (also 1.0.22 had RC in its file name).
MySQL Administrator is listed as version 1.1.7, but the current Windows version is 1.1.8, while the current Linux version is 1.1.6.
MySQL QueryBrowser is listed as version 1.1.18, which is right for the Linux version. However, for Windows, there's already a 1.1.20.
MySQL Connector/J is listed as 5.0.0-alpha on the dev.mysql.com page, while the file name indicates that it's a beta version.
MySQL Connector/MXJ is listed as 1.0.10 (GA), although the higher version 1.1.6 would also be a GA version, which I think might have a higher kind of priority than 1.0.10.
From my point of view, these are little blemishes, but for someone who's new to MySQL, this could easily be irritating.
The dev.mysql.com page also lists the most current versions of the relevant products - or lets better say - it should list the most current versions. Unfortunately, it often takes quite a long time until this list is updated, when a new version comes out - for example:
MySQL MigrationToolkit is listed as 1.0.22 beta, although version 1.0.23 RC is out (also 1.0.22 had RC in its file name).
MySQL Administrator is listed as version 1.1.7, but the current Windows version is 1.1.8, while the current Linux version is 1.1.6.
MySQL QueryBrowser is listed as version 1.1.18, which is right for the Linux version. However, for Windows, there's already a 1.1.20.
MySQL Connector/J is listed as 5.0.0-alpha on the dev.mysql.com page, while the file name indicates that it's a beta version.
MySQL Connector/MXJ is listed as 1.0.10 (GA), although the higher version 1.1.6 would also be a GA version, which I think might have a higher kind of priority than 1.0.10.
From my point of view, these are little blemishes, but for someone who's new to MySQL, this could easily be irritating.
Friday, February 17, 2006
New mysql_upgrade program
I just found two interesting entries in the Changelogs for MySQL 5.0.19 and 5.1.7:
Added the mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade (rather than mysql_fix_privilege_tables).
Added the FOR UPGRADE option for the CHECK TABLE statement. This option checks whether tables are incompatible with the current version of MySQL Server. Also added the --check-upgrade to mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.
This seems to be a very useful program, as the table versions change from time to time and it's not always easy for users to keep track of these table versions and to ensure that they are up-to-date (unless you always dump and restore all tables on each upgrade).
I don't know the details about this new program (actually, I don't know more than what the Changelog says), but I guess that with the new program and the new option for the CHECK TABLE command, you can automatically upgrade the table's versions and also ensure that the tables in the mysql schema are correct.
Running this program will probably also reduce the danger of being affected from some bugs, as there might be bugs that only occur when the table versions are outdated.
Added the mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade (rather than mysql_fix_privilege_tables).
Added the FOR UPGRADE option for the CHECK TABLE statement. This option checks whether tables are incompatible with the current version of MySQL Server. Also added the --check-upgrade to mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.
This seems to be a very useful program, as the table versions change from time to time and it's not always easy for users to keep track of these table versions and to ensure that they are up-to-date (unless you always dump and restore all tables on each upgrade).
I don't know the details about this new program (actually, I don't know more than what the Changelog says), but I guess that with the new program and the new option for the CHECK TABLE command, you can automatically upgrade the table's versions and also ensure that the tables in the mysql schema are correct.
Running this program will probably also reduce the danger of being affected from some bugs, as there might be bugs that only occur when the table versions are outdated.
Thursday, February 16, 2006
What has happened to the Open Source Database Consortium?
These days, everybody's talking about Oracle's attempts to buy MySQL and their previous purchases of Innobase and Sleepycat.
As I already mentioned earlier, I hope that Oracle will never gain influence on how MySQL evolves. MySQL is on its way to become a high end database system and so might - at least in some business areas - be a serious competitor of Oracle. What really turns my stomach upside down is the thought that MySQL could (in some people's minds, hopefully never in reality) become a lower-end alternative of something. MySQL should make it's way independently and never let any proprietary competitor gain any influence in any way.
However, what I would appreciate very much would be more co-operation among the Open Source database producers. A while ago, people from the major Open Source database systems have met to form the Open Source Database Consortium - that was in October 2005.
OK, that's not that long ago, but I hope that the ambitions to co-operate aren't over again. It was told that a website will be created at www.osdbconsortium.org. There's nothing to see except a "Just a web page" note. I haven't heard any news about this since October - I believe, it would be nice if MySQL, PostgreSQL, Firebird etc. could do some things together. That would certainly be more welcoming than any deal with a proprietary database vendor.
As I already mentioned earlier, I hope that Oracle will never gain influence on how MySQL evolves. MySQL is on its way to become a high end database system and so might - at least in some business areas - be a serious competitor of Oracle. What really turns my stomach upside down is the thought that MySQL could (in some people's minds, hopefully never in reality) become a lower-end alternative of something. MySQL should make it's way independently and never let any proprietary competitor gain any influence in any way.
However, what I would appreciate very much would be more co-operation among the Open Source database producers. A while ago, people from the major Open Source database systems have met to form the Open Source Database Consortium - that was in October 2005.
OK, that's not that long ago, but I hope that the ambitions to co-operate aren't over again. It was told that a website will be created at www.osdbconsortium.org. There's nothing to see except a "Just a web page" note. I haven't heard any news about this since October - I believe, it would be nice if MySQL, PostgreSQL, Firebird etc. could do some things together. That would certainly be more welcoming than any deal with a proprietary database vendor.
Tuesday, February 14, 2006
InnoDB and Berkeley DB under Oracle's control?
As a "little developer", I don't really know what's exactly going on behind the scenes - business stuff is not so my big thing.
However, I feel that it would be good for MySQL to have its own full featured storage engine(s) (with foreign key constraints, ACID transaction and all that stuff) under its control. Maybe there are no risks for the short term (I don't know), but it would still be better for MySQL's reputation to be completely independent from any competitor and from anybody else.
Insecure users will more likely migrate to another database systems than those who know what the future will bring. And people who now use other database systems will more likely not migrate to MySQL, if they can't anticipate how data will be stored in the future. So I would see an own and independent full featured storage engines as one of the very highest priorities for MySQL's future - not only from the technical point of view, but also for MySQL's reputation.
However, I feel that it would be good for MySQL to have its own full featured storage engine(s) (with foreign key constraints, ACID transaction and all that stuff) under its control. Maybe there are no risks for the short term (I don't know), but it would still be better for MySQL's reputation to be completely independent from any competitor and from anybody else.
Insecure users will more likely migrate to another database systems than those who know what the future will bring. And people who now use other database systems will more likely not migrate to MySQL, if they can't anticipate how data will be stored in the future. So I would see an own and independent full featured storage engines as one of the very highest priorities for MySQL's future - not only from the technical point of view, but also for MySQL's reputation.
Monday, February 13, 2006
My first EVENT and table logging tests
I've been very excited to test the new EVENT and table logging features, so I downloaded and installed MySQL 5.1 from the development source tree to get the very latest development version - 5.1.7-beta (no alpha anymore!).
The first thing you have to do to test the features is to enable event handling and logging (I also enabled the slow-query-log) in the my.cnf file - like this:
First I needed a table - I created quite a simple one:
The event should insert a random value into column d every 5 seconds. With Trudy Pelzer's documentation it was quite easy to do that:
There's just one mistake that I found: the documentation names the options to enable and disable the event as ENABLED and DISABLED, wheras I found out that it works with ENABLE and DISABLE.
So lets start the event:
Currently there's a bug in the Event Scheduler which prevents commands from inside an event from being logged in the general log. However, the mysql.general_log table shows the log data like we were used to from the general_log stored in the file system:
Nice stuff, indeed :-).
The first thing you have to do to test the features is to enable event handling and logging (I also enabled the slow-query-log) in the my.cnf file - like this:
[mysqld]
log
log-slow-queries
event-scheduler=1
First I needed a table - I created quite a simple one:
DROP TABLE IF EXISTS `test`.`tt`;
CREATE TABLE `test`.`tt` (
`id` int(10) unsigned NOT NULL auto_increment,
`d` int(10) unsigned NOT NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The event should insert a random value into column d every 5 seconds. With Trudy Pelzer's documentation it was quite easy to do that:
delimiter //
DROP EVENT IF EXISTS insertIntoTt //
CREATE EVENT insertIntoTt
ON SCHEDULE EVERY 5 SECOND
STARTS current_timestamp + INTERVAL 5 SECOND
ON COMPLETION PRESERVE
DISABLE
DO
BEGIN
INSERT INTO test.tt (d) VALUES (floor(rand() * 20));
END //
delimiter ;
There's just one mistake that I found: the documentation names the options to enable and disable the event as ENABLED and DISABLED, wheras I found out that it works with ENABLE and DISABLE.
So lets start the event:
E:\mysql_dump>mysql -h 10.2.4.1 -P 3307
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 419 to server version: 5.1.7-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> ALTER EVENT insertIntoTt ENABLE;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:14 | 14 |
+----------------+----------+
1 row in set (0.00 sec)
mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:22 | 16 |
+----------------+----------+
1 row in set (0.00 sec)
mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:30 | 17 |
+----------------+----------+
1 row in set (0.02 sec)
Currently there's a bug in the Event Scheduler which prevents commands from inside an event from being logged in the general log. However, the mysql.general_log table shows the log data like we were used to from the general_log stored in the file system:
mysql> SELECT * FROM mysql.general_log
-> ORDER BY event_time
-> LIMIT 10\G
*************************** 1. row ***************************
event_time: 2006-02-13 18:52:42
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 114
server_id: 0
command_type: Quit
argument:
*************************** 2. row ***************************
event_time: 2006-02-13 18:53:19
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 115
server_id: 0
command_type: Connect
argument: mpopp@10.2.1.1 on
*************************** 3. row ***************************
event_time: 2006-02-13 18:53:23
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 115
server_id: 0
command_type: Query
argument: SELECT DATABASE()
*************************** 4. row ***************************
event_time: 2006-02-13 18:53:23
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 115
server_id: 0
command_type: Init DB
argument: test
*************************** 5. row ***************************
event_time: 2006-02-13 18:53:27
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 115
server_id: 0
command_type: Query
argument: DROP EVENT IF EXISTS insertIntoTt
*************************** 6. row ***************************
event_time: 2006-02-13 18:53:27
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 115
server_id: 0
command_type: Query
argument: CREATE EVENT insertIntoTt
ON SCHEDULE EVERY 5 SECOND
STARTS current_timestamp + INTERVAL 5 SECOND
ON COMPLETION PRESERVE
DISABLE
DO
BEGIN
INSERT INTO test.tt (d) VALUES (floor(rand() * 20));
END
*************************** 7. row ***************************
event_time: 2006-02-13 18:54:01
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 116
server_id: 0
command_type: Connect
argument: mpopp@10.2.1.1 on test
*************************** 8. row ***************************
event_time: 2006-02-13 18:54:01
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 116
server_id: 0
command_type: Query
argument: SET SESSION interactive_timeout=1000000
*************************** 9. row ***************************
event_time: 2006-02-13 18:54:01
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 116
server_id: 0
command_type: Query
argument: SELECT @@sql_mode
*************************** 10. row ***************************
event_time: 2006-02-13 18:54:01
user_host: mpopp[mpopp] @ [10.2.1.1]
thread_id: 116
server_id: 0
command_type: Query
argument: SET SESSION sql_mode=''
10 rows in set (0.00 sec)
Nice stuff, indeed :-).
Is there a bug?
I've just downloaded and installed the new Sakila Sample Database.
Then I wanted to create a dump file to transfer it to my MySQL 5.1 installation on the same machine. Here's what I did:
Here are my default settings for mysqldump:
Line 392, where the error occurs, starts the CREATE TABLE statement for the payment table:
This looks perfectly right to me - also the foreign key creations. FOREIGN_KEY_CHECKS is set to 0, so it shouldn't be a problem that some of the referenced tables don't exist, yet.
The complete dump file can be downloaded from here.
Anybody have a clue?
Then I wanted to create a dump file to transfer it to my MySQL 5.1 installation on the same machine. Here's what I did:
E:\mysql_dump>mysqldump --triggers=false sakila > sakila.sql
Enter password: *******
E:\mysql_dump>mysql -P 3307 sakila < sakila.sql
Enter password: *******
ERROR 1005 (HY000) at line 392: Can't create table
'sakila.payment' (errno: 150)
E:\mysql_dump>perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
Here are my default settings for mysqldump:
all TRUE
all-databases FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
master-data 0
max_allowed_packet 25165824
net_buffer_length 1047551
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
routines FALSE
set-charset TRUE
shared-memory-base-name (No default value)
single-transaction FALSE
socket (No default value)
tab (No default value)
triggers TRUE
tz-utc TRUE
user mpopp
verbose FALSE
where (No default value)
Line 392, where the error occurs, starts the CREATE TABLE statement for the payment table:
CREATE TABLE `payment` (
`payment_id` smallint(5) unsigned NOT NULL auto_increment,
`customer_id` smallint(5) unsigned NOT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`rental_id` int(11) default NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`payment_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `fk_payment_rental` (`rental_id`),
CONSTRAINT `fk_payment_customer`
FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`customer_id`)
ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental`
FOREIGN KEY (`rental_id`)
REFERENCES `rental` (`rental_id`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff`
FOREIGN KEY (`staff_id`)
REFERENCES `staff` (`staff_id`)
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This looks perfectly right to me - also the foreign key creations. FOREIGN_KEY_CHECKS is set to 0, so it shouldn't be a problem that some of the referenced tables don't exist, yet.
The complete dump file can be downloaded from here.
Anybody have a clue?
Saturday, February 11, 2006
"Entirely uninterested in fancy features like stored procedures, triggers, and the like"
Here's my response to David Heinemeier Hansson's statement about being "entirely uninterested in fancy features like stored procedures, triggers, and the like" and Frank Mash's article about this issue.
MySQL simply *needs* features like Stored Procedures, User Defined Functions, Triggers etc. to be taken serious on the database market! So long has MySQL been criticized for not providing them - now as they have been introduced, it would be stupid to criticize MySQL *for* providing them.
MySQL has a very good chance to get as advanced as any other database system without losing it's simplicity. I don't think that performence will suffer a lot in the long run, because performence optimizations will certainly go on forever and will certainly overcome the speed decreases that may come from implementing new features. On the other hand, the other database systems will hardly ever have a chance to become as convenient to work with as MySQL (of course, people will always like what they are used to - if somebody has always worked with another product, he'll be more comfortable with this than with another - but for new users, MySQL is probably less painful to start with than the others).
The great thing is - people can still work with MySQL 5.0 the way that they were used to with earlier versions. They can decide whether to use the new features, or not. And that's exactly the right way for MySQL! Continuing to get as advanced as possible without breaking downward compatibility - where possible (and useful).
However, it is very important that MySQL also makes migration from other database systems as simple as possible. MySQL plans e.g. to introduce new languages for SPs - this would be a good chance to introduce languages that are compatible to other database systems to make it easier to migrate from them to MySQL.
I certainly wouldn't like to go without the new features anymore and if MySQL wouldn't have chosen to introduce them, I would have had to consider very seriously to move completely to e.g. PostgreSQL. In fact, the introduction of these new features was even my prime motivation to get involved more deeply with MySQL and to create the db4free.net project. Probably, it wouldn't have happened if MySQL hadn't chosen to implement them.
MySQL is on the right track - and I hope (and I'm very confident) that MySQL continues to stay there. Also MySQL 5.1 makes further important steps towards the right direction :-).
MySQL simply *needs* features like Stored Procedures, User Defined Functions, Triggers etc. to be taken serious on the database market! So long has MySQL been criticized for not providing them - now as they have been introduced, it would be stupid to criticize MySQL *for* providing them.
MySQL has a very good chance to get as advanced as any other database system without losing it's simplicity. I don't think that performence will suffer a lot in the long run, because performence optimizations will certainly go on forever and will certainly overcome the speed decreases that may come from implementing new features. On the other hand, the other database systems will hardly ever have a chance to become as convenient to work with as MySQL (of course, people will always like what they are used to - if somebody has always worked with another product, he'll be more comfortable with this than with another - but for new users, MySQL is probably less painful to start with than the others).
The great thing is - people can still work with MySQL 5.0 the way that they were used to with earlier versions. They can decide whether to use the new features, or not. And that's exactly the right way for MySQL! Continuing to get as advanced as possible without breaking downward compatibility - where possible (and useful).
However, it is very important that MySQL also makes migration from other database systems as simple as possible. MySQL plans e.g. to introduce new languages for SPs - this would be a good chance to introduce languages that are compatible to other database systems to make it easier to migrate from them to MySQL.
I certainly wouldn't like to go without the new features anymore and if MySQL wouldn't have chosen to introduce them, I would have had to consider very seriously to move completely to e.g. PostgreSQL. In fact, the introduction of these new features was even my prime motivation to get involved more deeply with MySQL and to create the db4free.net project. Probably, it wouldn't have happened if MySQL hadn't chosen to implement them.
MySQL is on the right track - and I hope (and I'm very confident) that MySQL continues to stay there. Also MySQL 5.1 makes further important steps towards the right direction :-).
Friday, February 10, 2006
Trigger privileges in MySQL 5.1.6
This blog post has moved. Please find it at:
http://www.mpopp.net/2006/02/trigger-privileges-in-mysql-5-1-6/.
http://www.mpopp.net/2006/02/trigger-privileges-in-mysql-5-1-6/.
Logging into database tables available in MySQL 5.1.6
I just received an email notification stating that the "Logging into database table" feature as of my Feature Request 12304 has been pushed into MySQL 5.1.6.
If I got that right, there will be two new tables in the mysql schema - "slow_log" and "general_log". You will have the choice whether to store log information in the file system, like you were used to - or to store them inside these new tables.
Together with the new Event Scheduling Feature (which can perfectly play together with the new logging feature - as I already wrote about in an earlier article), MySQL 5.1.6 will provide very interesting new features - I'm very much looking forward to testing them.
If I got that right, there will be two new tables in the mysql schema - "slow_log" and "general_log". You will have the choice whether to store log information in the file system, like you were used to - or to store them inside these new tables.
Together with the new Event Scheduling Feature (which can perfectly play together with the new logging feature - as I already wrote about in an earlier article), MySQL 5.1.6 will provide very interesting new features - I'm very much looking forward to testing them.
Wanna know what's going on in the development trees?
There are perfect places to look at - watch here:
MySQL 5.1 (http://mysql.bkbits.net:8080/mysql-5.1-new)
10 Changesets in the last day
35 Changesets in the last two days
73 Changesets in the last three days
102 Changesets in the last four days
128 Changesets in the last week
309 Changesets in the last two weeks
447 Changesets in the last three weeks
612 Changesets in the last four weeks
992 Changesets in the last eight weeks
1505 Changesets in the last twelve weeks
3410 Changesets in the last six months
5331 Changesets in the last nine months
7492 Changesets in the last year
13858 Changesets in the last two years
17219 Changesets in the last three years
MySQL 5.0 (http://mysql.bkbits.net:8080/mysql-5.0)
8 Changesets in the last day
15 Changesets in the last two days
22 Changesets in the last three days
34 Changesets in the last four days
43 Changesets in the last week
92 Changesets in the last two weeks
139 Changesets in the last three weeks
219 Changesets in the last four weeks
353 Changesets in the last eight weeks
671 Changesets in the last twelve weeks
2415 Changesets in the last six months
4172 Changesets in the last nine months
6272 Changesets in the last year
12625 Changesets in the last two years
15986 Changesets in the last three years
MySQL 4.1 (http://mysql.bkbits.net:8080/mysql-4.1)
1 Changesets in the last two days
2 Changesets in the last three days
5 Changesets in the last four days
7 Changesets in the last week
16 Changesets in the last two weeks
31 Changesets in the last three weeks
56 Changesets in the last four weeks
87 Changesets in the last eight weeks
150 Changesets in the last twelve weeks
668 Changesets in the last six months
1222 Changesets in the last nine months
1970 Changesets in the last year
6531 Changesets in the last two years
9653 Changesets in the last three years
MySQL 4.0 (http://mysql.bkbits.net:8080/mysql-4.0)
1 Changesets in the last three weeks
3 Changesets in the last four weeks
6 Changesets in the last eight weeks
12 Changesets in the last twelve weeks
61 Changesets in the last six months
104 Changesets in the last nine months
195 Changesets in the last year
958 Changesets in the last two years
2302 Changesets in the last three years
Another interesting place to look at is always the Snapshot page.
MySQL 5.1 (http://mysql.bkbits.net:8080/mysql-5.1-new)
10 Changesets in the last day
35 Changesets in the last two days
73 Changesets in the last three days
102 Changesets in the last four days
128 Changesets in the last week
309 Changesets in the last two weeks
447 Changesets in the last three weeks
612 Changesets in the last four weeks
992 Changesets in the last eight weeks
1505 Changesets in the last twelve weeks
3410 Changesets in the last six months
5331 Changesets in the last nine months
7492 Changesets in the last year
13858 Changesets in the last two years
17219 Changesets in the last three years
MySQL 5.0 (http://mysql.bkbits.net:8080/mysql-5.0)
8 Changesets in the last day
15 Changesets in the last two days
22 Changesets in the last three days
34 Changesets in the last four days
43 Changesets in the last week
92 Changesets in the last two weeks
139 Changesets in the last three weeks
219 Changesets in the last four weeks
353 Changesets in the last eight weeks
671 Changesets in the last twelve weeks
2415 Changesets in the last six months
4172 Changesets in the last nine months
6272 Changesets in the last year
12625 Changesets in the last two years
15986 Changesets in the last three years
MySQL 4.1 (http://mysql.bkbits.net:8080/mysql-4.1)
1 Changesets in the last two days
2 Changesets in the last three days
5 Changesets in the last four days
7 Changesets in the last week
16 Changesets in the last two weeks
31 Changesets in the last three weeks
56 Changesets in the last four weeks
87 Changesets in the last eight weeks
150 Changesets in the last twelve weeks
668 Changesets in the last six months
1222 Changesets in the last nine months
1970 Changesets in the last year
6531 Changesets in the last two years
9653 Changesets in the last three years
MySQL 4.0 (http://mysql.bkbits.net:8080/mysql-4.0)
1 Changesets in the last three weeks
3 Changesets in the last four weeks
6 Changesets in the last eight weeks
12 Changesets in the last twelve weeks
61 Changesets in the last six months
104 Changesets in the last nine months
195 Changesets in the last year
958 Changesets in the last two years
2302 Changesets in the last three years
Another interesting place to look at is always the Snapshot page.
Thursday, February 09, 2006
More about my ToDo article
Thanks for all the great feedback on my "My personal ToDo list for MySQL" article. I hope that developers read this article and all its comments and try to solve these issues as soon as possible. There are some great new suggestions from Roland Bouman and Giuseppe Maxia which add perfectly to the bugs and feature requests that I wrote about.
There's another idea for a feature request (which I think is also already filed): DATETIME and TIMESTAMP could support milliseconds - that's also something that I found was criticized by people who migrated from other database systems.
Generally, people who try to migrate from other database systems to MySQL might be good suggestors for features or what could be done better - so it's always a good thing to listen to what they have to say.
Even though most of the usual criticisms have become outdated, there are still several points where things could still be done better - often watched from other database systems, or sometimes because features don't work the way that a newbie or somebody who migrates to MySQL would expect to.
But still the very most important thing is to keep MySQL free of major bugs - nothing can do more harm to the reputation of a database system than a bug that destroys data. The harm can hold on much longer (sometimes year) as until the bug is fixed - people and companies migrate away from this product and always consider it unreliable. They might publicly tell about their problems which might make other people sceptical. So they also might choose another product which they think is safer, what might not be true anymore at the time they here about a damage that happened maybe years ago.
Unfortunately, it won't be possible to prevent evil bugs all the times. Where humans work, mistakes happen.
But there's a lot that we - the Community - can do. We can make the developers aware of such bugs and what we think is not yet perfect. This gives them the chance to fix those issues to keep the danger as low as possible that such damage happens too often.
There's another idea for a feature request (which I think is also already filed): DATETIME and TIMESTAMP could support milliseconds - that's also something that I found was criticized by people who migrated from other database systems.
Generally, people who try to migrate from other database systems to MySQL might be good suggestors for features or what could be done better - so it's always a good thing to listen to what they have to say.
Even though most of the usual criticisms have become outdated, there are still several points where things could still be done better - often watched from other database systems, or sometimes because features don't work the way that a newbie or somebody who migrates to MySQL would expect to.
But still the very most important thing is to keep MySQL free of major bugs - nothing can do more harm to the reputation of a database system than a bug that destroys data. The harm can hold on much longer (sometimes year) as until the bug is fixed - people and companies migrate away from this product and always consider it unreliable. They might publicly tell about their problems which might make other people sceptical. So they also might choose another product which they think is safer, what might not be true anymore at the time they here about a damage that happened maybe years ago.
Unfortunately, it won't be possible to prevent evil bugs all the times. Where humans work, mistakes happen.
But there's a lot that we - the Community - can do. We can make the developers aware of such bugs and what we think is not yet perfect. This gives them the chance to fix those issues to keep the danger as low as possible that such damage happens too often.
Wednesday, February 08, 2006
Updated QueryBrowser again
A while ago I complained about a range of very evil bugs inside the Windows version of MySQL QueryBrowser: http://db4free.blogspot.com/2006/01/whats-wrong-with-mysql-querybrowser.html
Now, version 1.1.20 has been released. After skipping (but still tested, at least for a short amount of time) five versions in a row (still used 1.1.14), I found out that the "killer bugs" that I knew about are now removed, so I gave 1.1.20 a try and updated MySQL QueryBrowser to its most current version again.
It's still too short to really say that this version is now free of heavy bugs, but it looks well so far and I'm confident that at least the times of skipping versions are over again.
Now, version 1.1.20 has been released. After skipping (but still tested, at least for a short amount of time) five versions in a row (still used 1.1.14), I found out that the "killer bugs" that I knew about are now removed, so I gave 1.1.20 a try and updated MySQL QueryBrowser to its most current version again.
It's still too short to really say that this version is now free of heavy bugs, but it looks well so far and I'm confident that at least the times of skipping versions are over again.
MySQL products CD/DVD?
Ronald Bradford's article about Testing/Trialing new MySQL Releases (an excellent idea!) made me think of another idea which could help MySQL to promote its products.
There are still many people without fast speed access to the internet to download files with many MBs of size. And there might be many people who would be interested in testing MySQL, but they would still need a little impulse to really get into it.
So my idea would be to sell CDs, or even better DVDs with all the free products around MySQL - maybe at net costs. This could include (for all common platforms)
* the current GA releases of MySQL server
* the current development releases of MySQL
* the client tools like QueryBrowser, Administrator, MigrationToolkit, etc.
* all the API drivers like JDBC, .NET, PHP etc.
* the current MaxDB release
* free 3rd party tools (like phpMyAdmin, Apache, PHP, ...)
* the manuals
* tutorials
* training videos (speeches at Conferences, ...)
* newsletters
* maybe even a 'best of' from blog articles
and so on (to keep it short - everything around MySQL that's available for free).
This CD/DVD could be renewed for example every six months. Maybe it would even be possible to attach it to Computer Magazines or give free copies to big companies, where people could redistribute their copy and get some of their employees in tough with MySQL (which would not only be a deal for the IT departments, but also for other office clerks who still abuse Excel as a database - maybe special tutorials could be tailored especially for this target group ;-)). There would be a lot of great opportunities, I think!
Even I - although I'm one of the guys who keeps his MySQL products much more up-to-date than on a half-a-year basis - would seriously consider buying this CD/DVD to have everything packed up together nicely.
There are still many people without fast speed access to the internet to download files with many MBs of size. And there might be many people who would be interested in testing MySQL, but they would still need a little impulse to really get into it.
So my idea would be to sell CDs, or even better DVDs with all the free products around MySQL - maybe at net costs. This could include (for all common platforms)
* the current GA releases of MySQL server
* the current development releases of MySQL
* the client tools like QueryBrowser, Administrator, MigrationToolkit, etc.
* all the API drivers like JDBC, .NET, PHP etc.
* the current MaxDB release
* free 3rd party tools (like phpMyAdmin, Apache, PHP, ...)
* the manuals
* tutorials
* training videos (speeches at Conferences, ...)
* newsletters
* maybe even a 'best of' from blog articles
and so on (to keep it short - everything around MySQL that's available for free).
This CD/DVD could be renewed for example every six months. Maybe it would even be possible to attach it to Computer Magazines or give free copies to big companies, where people could redistribute their copy and get some of their employees in tough with MySQL (which would not only be a deal for the IT departments, but also for other office clerks who still abuse Excel as a database - maybe special tutorials could be tailored especially for this target group ;-)). There would be a lot of great opportunities, I think!
Even I - although I'm one of the guys who keeps his MySQL products much more up-to-date than on a half-a-year basis - would seriously consider buying this CD/DVD to have everything packed up together nicely.
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.
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.
Monday, February 06, 2006
Subscribe to:
Posts (Atom)
