The Boston Diaries

The ongoing saga of a programmer who doesn't live in Boston, nor does he even like Boston, but yet named his weblog/journal “The Boston Diaries.”

Go figure.

Tuesday, September 15, 2009

“Okay, I'm deleting records over a certain age, so the time needs to be … ”

I'm still working on the questionnaire from Hell and the approach that R and I worked out was to make the questionnaire as stand-alone as possible so it can be moved as one whole piece from the old undocumented overwrought PHP framework (217,981 lines of PHP, 34,973 lines of XSLT, 104,134 lines of JavaScript and 17,137 PNG files) to the new documented overwrought PHP framework (44,655 lines of PHP, no XSLT, 7,597 lines of JavaScript and 120 PNG files—so maybe not as overwrought as I initially thought).

It's going slowly.

R called and said there was a problem with the current site—that the server was running out of memory trying to serve up a page with all the people who took the questionnaire. I took a look at the existing, undocumented overwrought PHP framework and it's doing a database query (which is returning at least 10,000 records) and using XSLT to transform XML and the database results into HTML.

All in memory.

So of course it's running out of memory.

We discuss the issue and there are two solutions: paginate the output, or delete enough records so the page can be built in memory. We both took one look at the options (diving into 300,000 lines of code and modifying it, or a few lines of SQL to prune the database) and said “delete the records!”

Now, the database doesn't use the SQL DATETIME to store when the questionnaire was filled out. No, that would be too easy. No, it uses an INT to store the date, using the number of seconds since January 1st, 1970—it being MySQL under Linux (it's a Unix thing). So I have to figure out the number of seconds from January 1st, 1970 as of three months ago.

Fortunately, it's easy under Linux:

[spc]lucy:~>date -d '3 months ago' +%s
1245129383
[spc]lucy:~>

So anything older than that gets deleted.

mysql> DELETE FROM questionnaire WHERE answer_date > 1245129383;
Query OK, 2344 rows affected (0.10 sec)

Um … I was expecting over 8,000 rows to be … oh XXXX! I got the conditional backwards! It's supposed to be less than that number of seconds, not greater, since it's a date!

Sigh.

Backups to the rescue.

I'll be glad when this is over.

Obligatory Picture

Trying to get into the festive mood this year

Obligatory Contact Info

Obligatory Feeds

Obligatory Links

Obligatory Miscellaneous

Obligatory AI Disclaimer

No AI was used in the making of this site, unless otherwise noted.

You have my permission to link freely to any entry here. Go ahead, I won't bite. I promise.

The dates are the permanent links to that day's entries (or entry, if there is only one entry). The titles are the permanent links to that entry only. The format for the links are simple: Start with the base link for this site: https://boston.conman.org/, then add the date you are interested in, say 2000/08/01, so that would make the final URL:

https://boston.conman.org/2000/08/01

You can also specify the entire month by leaving off the day portion. You can even select an arbitrary portion of time.

You may also note subtle shading of the links and that's intentional: the “closer” the link is (relative to the page) the “brighter” it appears. It's an experiment in using color shading to denote the distance a link is from here. If you don't notice it, don't worry; it's not all that important.

It is assumed that every brand name, slogan, corporate name, symbol, design element, et cetera mentioned in these pages is a protected and/or trademarked entity, the sole property of its owner(s), and acknowledgement of this status is implied.

Copyright © 1999-2024 by Sean Conner. All Rights Reserved.