I just read Eric Bergen's blog on row-based replication and application development from the presentation of Row-based replication at the User's Conference. Eric is giving all kinds of new ways to use the replication, for example that for a statement you can now configure the replication to only replicate changes to one of the tables in a multi-table statement. He is, however, missing the most important aspect: everything that we can do now and will be able to do in the future that we couldn't do with just statement-based replication. Here are some things that you can do with row-based replication that was not possible with statement-based replication.

Cluster Replication

Cluster replication is already in 5.1, but it's worth to …

In response to Kai Voigt's post about unxepected results from dates here is what is going wrong.

To add and subtract time using + and - you need to use the INTERVAL keyword either before or after the + or - like so..

mysql> select now() - interval 2 day;
+------------------------+
| now() - interval 2 day |
+------------------------+
| 2006-04-24 07:31:02    |
+------------------------+
1 row in set (0.00 sec)

In Kai's example it's valid to use - on it's own but that handles the date simply as an integer and subtracts the number specified. In small cases as in the example this can seem as if it's the seconds being subtracted but if for example you subtract 100 it simply reduces the number by that amount and not by 1 minute 40 seconds as it sould for a date type.

mysql> select now() - 100 day, now(); …

This one puzzled me a bit yesterday. Why doesn?t the following query return what I expected? (BTW, this is one of the most frequent support questions :) )

mysql> SELECT NOW() - 2 DAY;

No solution provided, I keep it for the reader. Hint: Compare to the output of NOW()

Had dinner with Joe Kotke from Feedburner and Gavin King at a local sushi place, and then came back to the hotel to kick off the Java BoF.

Got a lot of interesting feedback from the small community of Java developers at this conference (have to figure out how to get more to attend…Gavin and I were both talking at lunch between each other and other Java users here at the conference that there are a lot of people using Java and MySQL together, much more so than is represented here).

Maybe one way to get more Java developers here is to do what happened in an ad-hoc way tonight, which was to have Gavin sit in on the Geronimo BoF with Jeff Genender from Virtuas, a committer on the Geronimo project. Sparks flew, it got heated (but still a good discussion with a lot of participation), but at the end both guys shook …

Had dinner with Joe Kotke from Feedburner and Gavin King at a local sushi place, and then came back to the hotel to kick off the Java BoF.

Got a lot of interesting feedback from the small community of Java developers at this conference (have to figure out how to get more to attend...Gavin and I were both talking at lunch between each other and other Java users here at the conference that there are a lot of people using Java and MySQL together, much more so than is represented here).

Maybe one way to get more Java developers here is to do what happened in an ad-hoc way tonight, which was to have Gavin sit in on the Geronimo BoF with Jeff Genender from Virtuas, a committer on the Geronimo project. Sparks flew, it got heated (but still a good discussion with a lot of participation), but at the end both guys shook …

Had dinner with Joe Kotke from Feedburner and Gavin King at a local sushi place, and then came back to the hotel to kick off the Java BoF.

Got a lot of interesting feedback from the small community of Java developers at this conference (have to figure out how to get more to attend…Gavin and I were both talking at lunch between each other and other Java users here at the conference that there are a lot of people using Java and MySQL together, much more so than is represented here).

Maybe one way to get more Java developers here is to do what happened in an ad-hoc way tonight, which was to have Gavin sit in on the Geronimo BoF with Jeff Genender from Virtuas, a committer on the Geronimo project. Sparks flew, it got heated (but still a good discussion with a lot of participation), but at the end both guys shook …

After a long day packed with sessions at the MySQL Users Conference it seems like the evening would be a time to relax, right? Wrong.

The evenings seem to be more packed than the days. Four birds of a feather sessions that all were equally interesting but overlapping tonight. Didn't make it to the "Server Failover" BoF because I wanted to check out the Perl BoF. Had to slip out of that to hit the "building a new SHOW command." Just when that was getting underway (and was looking really cool) I had to slip out to attend the "How to have a good MySQL meetup." This week is turning out to be one of the most packed weeks I've had in a long time.

But this is a very good kind of busy.

Oh, and I finally crossed paths with Frank after a lot of hit and miss. So very cool to have met and hung out …

This happened to me 2 weeks ago on a site I run pro bono, and I forgot to blog about it. A comment in the “MySQL Automated Failover with Scripts” BOF reminded me about it, so here goes.

Basically, the web application stopped being able to talk to the database. I run the staging/test server AND the database, and the production site is run by someone else. So I checked out the logs, and indeed, there are many of the following:

050814 18:16:42 Aborted connection 241474 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got an error reading communication packets)
050814 23:58:43 Aborted connection 241487 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got timeout reading communication packets)

(and of course going up through the time it crashed, and even today). I believe –log-warnings is ON by default, because it’s not in my my.cnf and show variables shows that it’s on. But if …

Last presentation of Tuesday at MySQL UC is Giuseppe Maxia talking about cool things you can do with dynamic SQL in MySQL. I've had a chance to hang out with Giuseppe a bit during the conference, a fun person to be around (great stories). Just learned that Giuseppe is a perlmonk, even more cool than before.

(Sheeri's doing a writeup two seats over, I think I'll just kick back and enjoy this!)

Advanced Stored Routines techniques

Higher-order functions in math and computer science are functions which:
take one or more functions as an input
OR
output a function
in MySQL, we’re talking about stored routines that create other stored routines.

Why?
Pivot tables,
Global table checksums
Getters and setters
Multi-row insert query makers
Iterators
Dynamic functions allow you to access data from INFORMATION_SCHEMA and create dedicated dynamic routines and overcome syntax that doesn’t accept variables.

Officially, MySQL stored routines can’t do it.

However, Guiseppe is a really smart guy (as I’m learning firsthand this week)…

One way:
You can create a text definition for a stored procedure from a routine, even though you can’t actually create the stored procedure. Get the text back into the …