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 …

While attending Lars Thalmann’s session on Row Based Replication I began to think about how the new features will allow us to do creative new things with MySQL Replication. For example we can now issue an update query with a join and have one slave update only rows from table a and another slave only update rows from table b. This is very powerful but also very dangerous.

My experience has been that the average php developer can write average sql but doesn’t tend to think about the overall impact on a system when writing code. This is where system architects apply. A system architect can view an entire system and design it’s components to scale well, be fault tolerant and easily maintained. An application level developer will implement the system based on the design. Replication is a very deceptive convergence between the architect role and the developer …