Blog posts tagged "mysql"

4294967295 and MySQL INT(20) Syntax Blows

January 24th, 2010

Big Numbers 2 by pjern.

When you’ve been working with a technology for a long time, it’s difficult not to develop Stockholm syndrome. Not sure when I started using MySQL, but I bought my first license in 1998. I think it wasn’t until mid-to-late ’98 when we had to call Monty long distance to Sweden to get help with some tricky issues. Which is to say its been a long time since I thought about how confusing MySQL’s CREATE TABLE syntax can be.

Which is not to say that the documentation isn’t clear:

M indicates the maximum display width for integer types. The maximum legal display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 10.2, “Numeric Types”. For floating-point and fixed-point types, M is the total number of digits that can be stored.

But last week Flickr had a hiccup. We hit 4,294,967,295 photos. Or as a geek might say it, the largest number that can be represented by a 32-bit unsigned integer. This didn’t exactly catch us by surprise. We’d switched to using 64-bit ids for some things January, Friday the 13th, 2006. That and we got bit a few years ago when we hit 2,147,483,647 photos (that’d be the max signed 32 bit integer). Shortly after that we did a full audit of our tables.

But somehow we went on writing code after that, and we managed to slip a couple of new tables into the mix. And some of those tables ended up with INT(20) columns. Which simply mean we were adding some non-significant zeros to pad the display but truncating photo ids over 4294967295.

INT(5), INT(10), INT(20), and INT(255) all store the same amount of data.

Funny thing is, when I told this story to folks last week, this caught them by surprise. Sophisticated engineers, some of whom had deployed quite large MySQL backed sites. Because they were right, that syntax is dumb. And confusing. And I’d been taking it for granted so long I hadn’t thought about it in a decade. Which is why I’d bother to write a blog post about a popular piece of software, behaving exactly as it’s extensively documented to work.

Also, it’s interesting to note how if you keep making the same mistakes they become easier and easier to fix.

If you’re ever debugging a problem and you see the number 42-mumble-mumble-mumble-7295 you’ve run out of 32-bit storage. If you see 2-mumble-mumble-mumble-647 (2147483647) you’ve run out of signed 32-bit storage. 167-mumble-mumble-15 (16777215) you’ve run out of 24-bits and 65-mumble-mumble-35 (65535) you’ve run out of 16-bits of integers.

Somehow those numbers just jump out at me after all this time, you ignore the numbers in the middle, and notice the significant bits at the front and the end.

Photo from pjern

Flushing Rails Database (MySQL) Sessions

December 7th, 2005

I use Rails’ database session backend for LM. (for login, as well as “flash”) Without any sort of built in garbage collection the sessions table gets very large, very quickly. Beyond aesthetic issues, this can also cause MySQL’s key buffer to fill up. (which on Debian is by default set quite low)

So I wrote up a quick flush method, and saved it in a file models/session.rb.

class CGI::Session::ActiveRecordStore::Session
  def self.flush_old_empty_sessions
     self.delete_all "DATE_SUB(NOW(),INTERVAL 6 HOUR) > 
     updated_at and BIT_LENGTH(data) <= 688"

This says nuke all sessions which are over 6 hours old, and which are empty. (688 is the length of the serialized session with an empty flash)

MySQL specific, and susceptible to changes in either session structure or its serialization. But it was quick and easy and worked for me.

Then you simply need a cron job like: ruby script/runner 'CGI::Session::ActiveRecordStore::Session.flush_old_empty_sessions'

Tagged: Uncategorized , , , , ,

Package Surgery

August 16th, 2004

I’ve started putting together Debian packages for our dev platform at work, and stumbled upon an obvious, and yet new to me technique for building quick and dirty .debs … package surgery. I’ve got a MySQL 4.1.3 package (an official package won’t be showing up anytime soon is the rumor) by the simple expedient of downloading the 4.1.3 source from, and copying over the debian directory from the official package. Similarly a PHP 5.0.1 package that is compatible with Apache2 (apache2-mpm-prefork) is available by downloading DotDeb’s php5 package, and tweaking its debian/rules file. (rumor has it an official one of these might show up soon-ish)

Both are still pretty rough around the edges (I’m having trouble with config files not being installed, and not being updated properly), but hopefully in the next few days they’ll be available to a few brave souls. (Though they already install pretty cleanly on an up-to-date sarge)

In the mean time, anyone know where I can find instructions on setting up an apt source?

Tagged: Uncategorized , , , ,

MySQL, and the CASE for Class Table Inheritance

August 14th, 2004

At work we’re using Class Table Inheritance to model the core data structures of our as yet nameless open source CRM. (actually it has a code name, but I don’t like it, so we’ll pretend it’s nameless)

This week as I learned both the name of this pattern, and the SQL to implement it efficiently in MySQL I thought I’d share some notes on what we’ve come up with.

Read the rest of this entry »

Tagged: Uncategorized , , , , , , , , ,


January 31st, 2004

In which our protagonist begins a more thorough exploration of PEAR, and quickly runs into a challenge. Non-PHP hackers should feel free to avert their eyes. Maybe this is a well known hack, maybe (hopefully!) there is a better way to do this, but a quick Google search turned up a deafening silence, a marked dearth, of information on how to access a MySQL insert_id (the value created by inserting a row into a table with an AUTO_INCREMENT column).

I understand that using AUTO_INCREMENT columns is the “wrong” way to do things as far as PEAR is concerned, I’m supposed to use its implemented in PHP sequences, rather then the native and implemented in C method I’ve been using quite happily for years. (If I find myself migrating my MySQL app to Oracle sometime soon, I promise you AUTO_INCREMENT columns are going to be the least of my worries.)

Perl DBI faced a similar dilemma many years ago, and decided, wisely, to punt on the whole issue. They didn’t provide a solution, and they didn’t preclude you from using your native solution. Turns out sometimes doing less is more.

And lastly, when the hell did PHP get so high and holy that it refuses to let me code the way I want to code? Stuff like this constantly turns me off of PEAR, the whole thing just kind of has a bad smell about it.

But enough with the ranting, so here is my current, hold-me-over-until-someone-points-out-the-error-of-my-ways solution.

Your PEAR::DB object $dbh holds a reference to the MySQL connection object in $dbh->connection. Which allows you take a stroll down memory lane to the bad old days with this little gem:

$id = mysql_insert_id($dbh->connection)

For all our veneer of OO civilization, it doesn’t take much to scratch away the surface and expose our naked barbarian PHP soul does it?

Tagged: Uncategorized , , , ,