2008-02-28

MySQL Date and Time Arithmetic

One thing that sometimes bugs me in MySQL is the huge amount of errata. The database is packed with features and abilities that are side-effects from "historical decisions". Decisions that were made when it wasn't planned to be an enterprise product, or changes made to ease porting applications to MySQL from other databases. Legacy, so to speak.

The timestamp data type is a good example - the timestamp manual section is a long list of exceptions and special cases. I usually re-read the manual page before using it. Another good example is the amount of date and time functions that exist in MySQL. There are way too many of them, some overlapping in function, and some just exist as aliases to other functions for compatibility. Just go over the list.

On the other hand, this artistic freedom has its merits.
For example, take the date arithmetic. Chances are that if you came from SQL Server background like me, you were used to all the variations on the DATEDIFF and DATEADD commands. While it works as well (the DATEADD function is there and works almost the same way), MySQL supports a much readable form of date and time arithmetic.

For example:

SELECT NOW() - INTERVAL 5 MINUTE

Will return, naturally, current date+time minus 5 minutes. Or:

SELECT NOW() + INTERVAL 10 DAY

You guessed it, this will return the current date+time plus 10 days.

Clean and simple, isn't it? The syntax goes like this:

date + INTERVAL expression unit

The unit can be MINUTE, HOUR, DAY, MONTH, YEAR etc. The expression can be any legal T-SQL expression, and the date is just the date variable, which can be a result from another expression, a variable or a table column. Check out the manual for more examples.

This is an elegant solution to a common problem, which is also easy to remember and use. Why so few people use it is a mystery to me - I've almost never seen it in code samples.

2008-02-21

Hello Planet MySQL!

My blog been added several days ago to the Planet MySQL feed, and am now one step closer to world domination.

I'll assume that most people who see this entry at their feed readers would be unfamiliar with this blog, so I should be including an introduction here. Instead, I invite you to take a look at the home page and look at some olds posts of mine that might interest you. Not everything is about MySQL, and the Planet MySQL feed takes only the ones that I've labeled as such.

The following posts are the top search engine keywords that this blog gets, so you might want to start there:

I wonder how many comments of this sort I'll get?

What do you want me to do?  LEAVE?  Then they'll keep being wrong!

2008-02-18

Mac OS X: Neverending Windows Bashing

This is a screen capture I made just now of a standard network browsing feature - this is how Mac users see Windows computers on the network.


Gotta love the "preview" of how a user experience on a Windows computer feels like: old CRT screens and BSODs.

All this "Macs are better" elitism is amusing at times, but very immature. This Penny Arcade comic really got it right.

2008-02-17

MySQL 5.1.23 Regression Bugs

I've started testing the new Release Candidate (5.1.23) today. This was a version with a huge amount of fixes relative to latest MySQL releases - but I didn't expect to run into two bugs this fast.
  • Bug #34626 which I just filed in today and was already verified. This is a basic scenario which is used in the deployment scripts we use at Delver. Just try and pipe an empty file into the mysql command line client - and it crashes.
  • Bug #32633 which was apparently fixed in 5.1.24, and should have also presented itself in 5.1.22, but didn't for some reason. The upgrade brought it up.
    This one basically means that you just can't create stored procedures at all, unless you clean the sql_mode variable from certain tags. Which was really frustrating to find. I almost filed another bug but managed to find someone who already filed a duplicate 6 days ago.
    The SQL Mode is a session/global variable that determines which SQL compatibility mode the current session works as. More details here if it interests you.
I could say that I'm doing the community a favor by testing products or whatnot, but I'd just prefer the QA procedures at MySQL AB to be a bit stricter - I consider these as really trivial scenarios.
Conclusion: new versions fix old bugs and bring new ones. Trust no one. Test for yourself.

2008-02-15

Recommendation: ImgBurn

I'd like to recommend a fine piece of software which I found to be the best program to use for casual CD/DVD burning. I use it exclusively for more than a year. It's fast, simple and functional. Moreover, it's completely free.
So now that you know, please stop using that cracked version of Nero. It's not as good as ImgBurn anyway.

2008-02-09

Assumption is The Mother of All Fuckups - Firefox Password Manager

Consider the following:
  1. Are you using Firefox?
  2. Do you use the "Save Password" feature for web forms?
If you answered yes to these questions, you are at risk. Not the kind of "cool buffer overflow" risk, that I personally ignore most of the time, but the kind of "anyone knowing this sitting near my computer knows all my passwords in 2 seconds" risk.
You are probably assuming, that some geek security freak at the Mozilla Foundation made sure your passwords are safe, encrypted one-way into a unbreakable AES/MD5/SomeHashBuzzTLA.
Well, they are not.

Follow these steps:

Open the preferences screen in Firefox (this is the OS X version, but the Windows version is just the same)

Click on "Show Passwords"
We're not there yet. Click "Show Passwords" one more time (it can't be that easy), and get the following:


I blurred the images for obvious reasons, but you can guess how it looks - the entire site/username/password list is there in clear text.

I know this "feature" is well documented if you bother to look it up, and it can be somewhat mitigated if you place a master-password over the configuration. Still, I find it unacceptable for a browser to behave this way by default.

My recommendation - assume passwords saved in Firefox are compromised to begin with, and only save passwords for sites where you don't care if someone knows the password.