I would like to set a trigger where upon inserting a new record with permalink set to "myfilename" would become "myfilename-id" where id represents the auto-incremented value of the id field. So I tried:

drop trigger article_filename;
CREATE TRIGGER article_filename BEFORE INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );

However this creates a permalink of myfilename-0 as the id value was obviously not available.

So I tried changing the above to AFTER INSERT:

CREATE TRIGGER article_filename AFTER INSERT ON adoppt.articles2 FOR EACH ROW SET NEW.permalink = CONCAT(NEW.permalink, "-" , NEW.id );

and got:

#1362 - Updating of NEW row is not allowed in after trigger

So I tried

CREATE TRIGGER article_filename …

Warning: Rough Notes Follow

Any user-focused social networking community should aim to keep its members informed about what?s happening in the community.

At the very least, the community should inform members of important events that relate to them.

By default all communication should be turned on to avoid confusing members with various signups. In each e-mail that is sent out, a member should be given the option to conveniently unsubscribe from receiving all future notifications of that type.

These are the notes for what we are doing at Adoppt.com. The "cleaner version" of these notes and how I actually did all this with Ruby on Rails and MySQL will be in my upcoming book, Pro Rails (the first few chapters of which are almost ready to be mailed out to beta readers who have kindly notified me about beta reading the book. Thanks).

On …

Every so often, somebody asks me a question about MySQL replication. Usually that question is phrased something like "I think I've run into a bug in MySQL replication", or "does mysql replication always work" or something along those lines. These are almost always related to errors resulting from data inconsistency between the master and the slave. I've never personally seen a case where a bug resulted in inconsistent data between a master and a slave.

So, if your slave encounters an error because you have a duplicate key, then you have a data consistency problem. The data on your slaves doesn't match the data on your masters. In most cases this is because statements were skipped on one or more slaves. If that isn't the case, then something is writing to one or more slaves, which leaves the master inconsistent. You could also have a replication rules problem if you are restricting replication only to certain tables or databases.
…

Updated 28-mar-2006
There really is no way to do a comparision by numbers in features and functionality when it comes to Oracle and MySQL in the area of Stored Procedures and Triggers. Oracle does provide a far greater and extensive features list, however having more only means it has more.

We all know this, MySQL is a growing evolving database product, having just celebrated it’s tenth anniversary, Oracle on the other hand, will next year be 30, and has had significant funding in R&D being up until recently the second largest software company world wide. However, being open source, MySQL has the advantages of being lean and mean, providing only …

Dear MySQL users, MaxDB users and friends,

it happened what we always told you what might happen: we did not make it to write an article for you in this week. Of course, this is not the end of the series. It will continue, but other duties might force us in the future to skip a week again. As a small “excuse” for you, we have written an FAQ like entry.

Can I recover a MaxDB backup on a different version and/or a different system?

It depends: the processor architecture and the MaxDB version must be compatible. Given these two main preconditions you can take a backup from one system and recover it on a different system. This is a common situation when you are planning to upgrade the database software and the server hardware in one step. In general, we do not recommend to do these two steps at once. The simple reason that we do not is, that you should never change two variables at once. This makes debugging extremly …

Dear MySQL users, MaxDB users and friends,

it happened what we always told you what might happen: we did not make it to write an article for you in this week. Of course, this is not the end of the series. It will continue, but other duties might force us in the future to skip a week again. As a small “excuse” for you, we have written an FAQ like entry.

Can I recover a MaxDB backup on a different version and/or a different system?

It depends: the processor architecture and the MaxDB version must be compatible. Given these two main preconditions you can take a backup from one system and recover it on a different system. This is a common situation when you are planning to upgrade the database software and the server hardware in one step. In general, we do not recommend to do these two steps at once. The simple reason that we do not is, that you should never change two variables at once. This makes debugging extremly …

The discounted (50%!) 5.0 beta certification exams that I mentioned a couple of weeks ago are still running. At the current rate it looks as though we will be closing down the beta try-out period on or around April 15.

So don’t walk, run to your nearest VUE testing center to register for your chance to be on the first 5.0 certification train.

• More information on MySQL 5.0 certification
• More information on MySQL certification betas

A little more than a year ago, I was hired to implement row-based replication to the MySQL database server. Since the principles are easy enough, I thought this would be a straightforward task to be done in a few months, tops. As always, I quickly got punished for my hybris: getting a basic row-based replication up and running was relatively straightforward but as the saying goes, the devil is in the details. Row-based replication is now safely tucked away in MySQL 5.1 for anybody who wishes to use it, but the obvious question is then what does it give me and why should I use it?

When using statement based replication, the replication is accomplished by replicating the actual SQL statements to the slave server directly. That works fine for most statements, but in some situations, this does not work as expected. This time, we will only look into one such situation. Consider the following SQL statements:

UPDATE account
 SET …

I have recently seen a couple of posts on the MySQL forums with regard to debugging stored procedures in MySQL. The people asking have been Oracle developers who like most people developing stored procedures in Oracle have been using an Oracle built in package called DBMS_OUTPUT. The package essentially accepts text which is then inserted into a buffer which can be viewed after a procedure has been executed (or even during using the correct tools). It got me thinking how easy it would be to add a similar debug method to MySQL so I came up with the following.

I created a new database called debug, this isn't strictly necessary but I like the idea of having a set area for the debug constructs to reside. I then created a table to hold the output, to keep it simple I simply gave it an id column so we could use the debug across a number of procedures at once, a text column to hold out debug statements and an auto increment column so I could …

I'm sorry to report that the video of the last Boston MySQL meetup isn't going to see the light of day. The meetup was good, ~20 in attendance to listen to a presentation on MySQL cluster that I put together and have a discussion about the technology.

I spent some time fiddling with the video we shot, but the fact that we had no microphone or tripod, and were in a huge auditorium at MIT where the camera had to be set at the back to capture the slides on the mega-screen all add up to a dark video with unacceptable (many times inaudible) audio. There are chunks of time where the video is almost pure black while hearing comments from attendees. Next time will be better.

As always, thanks to MySQL AB for pizza and soda. Also to the group member who scrambled to find a new room when the room we were supposed to use was double-booked.