That's amazing - almost like a chain reaction! Suddenly, everybody at Planet MySQL is writing something on triggers.

Well, to all those that want to try it too - that is - have a trigger perform an insert or update in anohter table here's a little tip that might be of use.

Suppose our trigger table has got an auto_increment column, and you'd want to be able to get your hands on the value of that item, what should you do? The answer was a bit of a surprise to me...

Suppose we have to tables, A and B and we want to have a trigger on A that inserts into B:
So, we need to someway transfer the generated id value for A into the corresponding field in B. Normally, you'd resort to the LAST_INSERT_ID, but this does tnot work inside a trigger.

Instead, use the new. pseudocolumn. That …

So I am listening to a talk by Rusty on talloc(). Its a memory allocation system.

Sounds great.

My question is, why do people keep writing these things? Apache has the same thing, its called APR. MySQL has mysys, GNOME has one as well (which MySQL uses for its GUI tools), and I've seen several others over the years.

Oh well... perhaps I will go write a string class, or yet another CMS system.

Why isn't this a completely annoying talk? Because Rusty is a great presenter :)

I'm just listening to a talk by Rusty Russell (an excellent speaker anyway) about Tridge's talloc. It extends the traditional malloc() mechanism by making every returned pointer effectively be a memory pool. You can allocate memory that is "attached" to an existing pointer. When a parent pointer is freed, so are its children.

There are additional functions for stealing (re-attaching) a pointer to a different parent, optional destructors, and other useful trickery.

Tridge uses talloc in Samba4, and Rusty has also uses it in various places. Good stuff.

Frank asks Can MySQL Triggers Update Another Table?

Frank then sets his blog to only allow comments from registered Blogger users. Bad Frank, no comment for you.

If I could have commented, I would have shown him how it is done in the SampleDB:

–
– Table structure for table `film`
–

CREATE TABLE film (
film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
…
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Table structure for table `film_text`
–

CREATE TABLE film_text (
film_id SMALLINT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY  (film_id),
FULLTEXT KEY idx_title_description …

Revision control is of course very important in software development, particularly when many developers are doing a lot of commits in a very distributed environment - like with MySQL: our developers are based all over the planet, but also travelling, off-line at times, and so on.
So revision control interests us a great deal, and distributed (and off-line) systems in particular.

A few years ago, BitKeeper used to be the only one that pretty much had all this licked.
Some emergent Open Source projects didn't continue, but the current "contenders" appear to be GIT (started by Linus Torvalds), Mercurial (by Matt Mackall) and Monotone (Graydon Hoare), with Bazaar NG (Martin Pool, Canonical) also showing …

Just placing this here so I do not forget it:

A friend had an issue where he added a new web server to his network, but wanted to use the MySQL of the old server. Problem was he could not connect to the MySQL server from the new web box.

I took a look and it appeared to be a firewall issue. I generally steer clear of iptables, so I joined #iptables on freenode.

Here’s what I learned:

1) Save the current firewall settings:

iptables-save > ~/iptablessave

2) Make sure you saved them:

cat ~/iptablessave

3) Drop the firewall rules to check if the firewall is at fault (danger Will Robinson, no protection!)

iptables -P INPUT ACCEPT && iptables -P OUTPUT ACCEPT && iptables -P FORWARD ACCEPT && iptables -F && iptables -X && iptables -Z

4) Repeat 1-3 on second machine, then try connecting. If it works, you have a firewall …

LinuxConfAU is generally held at a university in the Australian/New Zealand summer holidays. This gives the event enough space with big lecture theatres and other infrastructure.

The easiest accomodation is at the campus colleges (ye roughing it ;-), which have shared bathrooms. Someone who doesn't need to shave can optimise their bathroom presence and therefore better take advantage of any shower becoming available. Just a thought.

I got an email reminder message (several actually) from Matt Asay about the upcoming OSBC conference in San Francisco.  For those of you in high tech in silicon valley who want to understand open source, or anyone looking for a fun way to spend Valentine's day, OSBC is the place to be.  It's the first and best business conference about open source.  Two years ago, when Matt started the conference, open source business sounded like an oxymoron.  Now it's practically a mantra in the valley.  Keynotes include the likes of Nick "Does IT matter?" Carr from Harvard, John Roberts from SugarCRM, Bill Hilf from Microsoft, Peter Thielf from Paypal, Jonathan Schwartz from Sun and Lawrence Lessig from Stanford.

Don't think about it, just sign up.

• OSBC: …

Markus has a very nice writeup on how to use Triggers to emulate check constraints in MySQL. Is there another method? Absolutely. Using views.

Views in MySQL support a concept called Cascaded Check Options. These types of check options are powerfull in my opinion. Traditional check constraints are defined in the schema (ala Oracle), and are not "inheritable" in nature. By using the CASCADED CHECK OPTION when creating a view, the developer can have a complex set of filters for constraint checking. Views can based on other views as well, and if the new view also contains the CASCADED CHECK OPTION clause, then the constraint traverses down the chain of views, constraint checking at each level.

Here is a really simple example that illustrates how this works:

CREATE TABLE Cards (
  card char(1),
  suit varchar(8)
);

CREATE VIEW CardsSpades AS
SELECT card,suit
FROM Cards
WHERE suit = 'Spades' …

In August, I've filed a Feature Request and suggested to allow logging into database tables additionally to writing log files to the file system. The development is in progress and it's told that the feature will probably be available in one of the earlier 5.1 releases.

We also know, that in MySQL 5.1.6 the Event Scheduling Feature will be available - read more about it here: http://dev.mysql.com/tech-resources/articles/event-feature.html

The combination of these two new features will give great new possibilities. The log data will be available more conveniently and it will also be very easy to filter those records that are of interest (with simple SQL statements). The Event Scheduling Feature can be run at regular intervals to filter out particular …