It's been too long since I've spent an evening in the MySQL docs (something I once did every evening). Updating to the latest 5.0.x release is always a good thing, expecially when looking over the list of functionality changes and bug fixes in the recent releases.

A quick list of noteworthy things that have changed in the last several releases:

• 5.0.9 - Recursion in stored routines is now disabled
• 5.0.10 - SHOW TRIGGERS added, trigger lock problems fixed, trigger namespace changes from table-level to database-level
• 5.0.11 - Stored procedure limit increased from 64K to 4G, mysqldump includes triggers
• 5.0.12 - FLUSH no longer allowed from within routines, new sleep() function

Apologies to the developers who slaved over new features that's didn't catch my eye.

Perusing the MySQL docs tonight and wanted to note this change. In version 5.0.6 MySQL added writing (correctly) stored routine syntax to the binary log (which is used for replication and data recovery). Along with the CALL statement, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION all have been added to the items logged in the binary logs.

This is excellent news. However, this opens up at least one security issue, namely that these statements are replicated and run on slave machines with full privileges (not using the user's permissions) and can lead to unauthorized changes in the slaves.

To secure your routine statements on servers with binary logging enabled, 5.0.6 requires that to CREATE or ALTER procedures or functions one of two conditions must be satisfied:

1. The user must have the SUPER privilege in addition to having a CREATE/ALTER ROUTINE or CREATE/ALTER …

Yep, handling of hierarchical data in a relational database is a popular subject lately. I never heard of the nested set model until I stumbled upon it on the mysql newbie forum.
Now, it seems as if it's popping up everywhere. MySQL even republished the article pointed to in that thread. And, it's being read, too, at least by Clive Galway, who's using it manage Google Map data.

Anyway, I'm seriously considering to use this structure in the future too, so I decided to investigate it for a bit. And while I'm at it, I thought that it would be a nice opportunity to familiarize myself with MySQL a bit.
For starters, I think I won't do away with the …

Interesting article by David Rosenberg on the fact that there appears to be a shortage of open source marketers.  When I joined MySQL, I had a lot of discussions with the founders about how to build a strong team and what type of background would be appropriate for open source marketing.  Unfortunately, it was clear that there simply weren't a lot of open source marketers out there.  There were only one or two successful open source companies and there was no "deep bench" to draw from.  So instead, I recruited from the commercial software industry, drawing from people who had experience marketing to developers and DBAs.  From what David reports, and from the number of recruiters who call me looking for contacts, it sounds like there are a lot of openings out there in open source startups.  My recommendation to the recruiters is to hire folks who have …

LKML: Linus Torvalds: Re: [OT]Linus trademarks Linux?!!

thoughts on the trademark and notes about slashdot being a big public wanking session (which is, if nothing else - quite accurrate and quite funny)

An old year-2000 mail about the same stuff

Aware IM, the new database software product from Awaresoft, allows business professionals and developers to create comprehensive Web applications without programming.

We've got six dedicated web servers reading from and writing to just one database server. All fine and good - until something goes wrong. Over the past few months, lots of little things have been going wrong ... some developer runs a "bad" query which locks the tables needed by the web servers; a new ad campaign generates much more traffic than any previous, and the database is not up to handling that many requests all at once. Of course, I would love it if the web developers never accessed the database without first showing me their SQL statements, and if we could afford the server downtime necessary to completely restructure the tables (change them to InnoDB and add much better indexes). However, I came up with another means to alleviate some (not all) of the stress on the database...

Distribute the most critical tables to a tiny mysqld process on each web server, so that the Apache/PHP processes no longer depend on the central …

This might be old news to some, but I discovered some weeks ago that Breezy will have PHP5 and MySQL 4.1.

I've been using packages from Dexter (preferred - includes phpize) or dotdeb.org with my Sarge and Hoary servers. While these sources have worked well to date.... I'm excited about Breezy in October.

Most of the time when you are using SQL you will be selecting against a table in your database. But there are the odd occassions where you don't actually want to select something from a table but still display information.

In Oracle this is handled by the DUAL table. DUAL is a single row all purpose table which allows you to issue a select statement on values which are not stored in an actual table. Take for example the sysdate function which returns the current date.

SQL> select sysdate from dual;


SYSDATE
---------
23-AUG-05

MySQL has full support for the DUAL table also.

mysql> select curdate() from dual;
+------------+
| curdate()  |
+------------+
| 2005-08-23 |
+------------+
1 row in set (0.00 sec)

However MySQL doesn't really need the DUAL table to return values. You can …

You may have noticed in today's entry the SQL was shown in red and orange. I just wanted to point out if it wasn't obvious from now on any SQL or Code shown in red relates to Oracle and anything in Orange relaters to MySQL.