CREATE PROCEDURE and CREATE FUNCTION in MySQL currently (as of 5.0.18) don't allow for an explicit DEFINER= clause. This has dangerous implications for any setup in which stored routines and replication are used together.

The DEFINER of a stored routine currently is always set to the CURRENT_USER at the time of the CREATE statement, there is no possibility to override this with an explicit setting. If you issue a CREATE command as root on the MySQL server, that's what you get:

mysql-master [root]> CREATE PROCEDURE test() DETERMINISTIC SELECT 'test';
Query OK, 0 rows affected (0.00 sec)

mysql-master [root]> SELECT * FROM information_schema.routines
    WHERE ROUTINE_NAME='test'\G
*************************** 1. row ***************************
     SPECIFIC_NAME: test
               ...
           DEFINER: root@localhost
1 row in set …

I have a small network at home running a Windows XP laptop, a Windows desk top and a Mac Mini. I recently move the Mac over to the TV so I could connect it up and watch TV programs I saved using a digital TV gizmo I purchased for my Mac, it's great because it has pretty much made the video machine redundant and recording is a breeze.

However this meant that the Mac was too far away from the ADSL gateway to be connected via a cable. Given that it was a first generation Mac Mini it didn't come with a wireless card installed. I decided that given that an Airport card would have cost almost twice as much as a windows wireless card and that the Mac Mini at least seems difficult to upgrade yourself, I choose to move the gateway closer to the Mac and get a wireless card for the PC.

This seemed great for a while, but I have been having plenty of problems with the wireless connection going down which needs a reboot to fix, this …

I ran into Ransom Love, former CEO of Caldera, at this past Open Source Business Conference. He's in a new gig and is much happier. Talking with Ransom reminded me of just how far we've come in open source (or how far we've fallen, if you're of that mind).

Years ago, Ransom was vilified for saying things that we now take largely for granted. He wanted to charge a per unit license charge, arguing that support was not a good enough business model (or, at least, not the only business model for open source), and was classified a "parasite." Today, Red Hat has created a fantastic business with a per unit license model. (Yes, they call it "support," but it's really a license fee.) Today, SugarCRM, Alfresco, etc. etc. etc. all essentially charge this way, though we've become creative in …

I got an email from Peter Mescalchin a few weeks ago asking me to check a problem he was having with a select statment. He has two fairly large tables one of 14,000 rows and another of 79,994 he is joining these two tables together using a column on the second which has an index. With the index in place the query executes quickly (0.19 seconds on my machine) but when the index is removed his machine hangs and on mine a P4 Hyperthread 2.8Mhz machine one of the CPU threads is kept at 70-80%, it finally completed 1 Hour, 16 Minutes, 2.16 seconds later. A similar thing happened on the Mac but the connect timed out before it returned.

I'm not a performance expert so I wasn't able to give Peter much of an answer other than to confirm what he was seeing. Anybody have any idea why there is such a dramatic difference?

For those looking to test here are the details Peter was using.

Two tables:

 …

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.

…

I have finally found sometime this weekend to update www.mysqldevelopment.com. So in the spirit of the site I'm looking forward and adding content on some of the new features in 5.1, first up it's the event scheduler.

I have been using my Mac less and less recently simply because I have been using it as a media centre and it now lives over by the TV, I generally connect remotely via putty or VNC. So given that I was on my Windows PC this morning I decided to install the latest release of 5.1 on there.

The download and install went perfectly and I started up the MySQL command line ready to have a play with the event scheduler. So I ran

mysql> SHOW GLOBAL VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

Great …

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 …

O'Reilly just published a nice article on Building a High-Availability MySQL Cluster

up to 3 copies of data (3*DataMemory)

+ 64MB * NoOfFragLogFiles (default=8)

+ UNDO log (dependent on update speed)

For example:

DataMemory=1024MB

idea on disk usage= 1024*3 + 64 * 8 =  3584MB + UNDO log

It’s very tempting to have a “SHOW ESTIMATES” command in the management client/server that performs all these equations (and the answers). I bet Professional Services would love me :)