Posts tagged MySQL
Delayed Job dying silently
Aug 3rd
I’ve just completed migrating a client site from BackgrounDRb to delayed_job (which is a huge relief on several levels). I had hoped to complete the process this morning, but the delayed_job process kept dying on me without any apparent explanation in the usual logs.
Thankfully the RPM log in my app turned out to be capturing one vital detail — the MySQL server had “gone away”, Armed with that knowledge I was able to find this thread on github and from that Brandon’s pointer to the ‘reconnect’ option in database.yml. Setting that appears to have solved that last lingering problem.
And since it took a while to track down, it seemed worth sharing.
MySQL 5.1
Jan 17th
The MySQL developers seem to be developing a habit of rolling out useful new features in their .1 releases. MySQL 4 was a good release, but it’s the group_concat feature from 4.1 that I miss most often when forced to use a 3.x or 4.0 install. MySQL 5 made some huge leaps forward, but already there’s plenty of talk about the new features coming in 5.1.
In particular, I’m looking forward to support for XPath and event scheduling.
With the former it will now be a simple matter to query arbitrary XML data stored in text fields. It’s by no means an XML database, and it remains to be seen what performance looks like, but for those of us who sometimes need to store smaller snippets of XML or who want another option for flexibly searching the data we’re storing, it looks to be a nice addition.
The latter means we’ll be able to dispense with all those quick maintenance scripts dispatched by cron and instead (if the current syntax makes it into the release) set up triggers such as:
CREATE EVENT
session_expire
ON SCHEDULE
EVERY 1 DAY
DO
UPDATE session SET expired = 1
WHERE
DATE_SUB(CURDATE(), INTERVAL 7 DAY) > last_updated;
Naturally, developers will need to decide how much control to cede to the data layer and how much ought to be kept in their application, and backup strategies will need to be adjusted accordingly. But for those of us tired of having to maintain configuration data across multiple scripts (or load in the configuration framework for quick, standalone scripts) this is a very handy addition.
Next/Previous with MySQL
Jun 27th
For one current project involving the presentation of a program for an arts festival, I needed a way to present ‘next’ and previous’ links within the results of a MySQL query. For example, the user might search for all artists who are sculptors and order the results by the artist’s last name. Ordinarily when building next/previous links I’m working without that level of sorting, and I ended up doing quite a bit of searching to find the best way to achieve this.
Of course I could load entire result sets into my program, and process them to find the results I need, but that could quickly eat memory, and I prefer whenever possible to do my processing within the database management system. What I really wanted to do was to return the initial search results with an extra column that represents the sequencing of the results, and then run a subsequent query to select particular results.
Eventually I found this posting in the MySQL forums which led me to the following pair of instructions:
DO @a := 0; SELECT @a := @a + 1 AS sequence, * FROM mytable WHERE artist_type = 'clay' ORDER BY artist_lastName;
That returns the sequence. Then, in order to get the result I’m looking for I need to do reset the increment and get my next/previous results. Say I have the third result already I’d issue:
DO @a := 0; SELECT @a := @a + 1 AS sequence, * FROM mytable WHERE artist_type = 'clay' ORDER BY artist_lastName HAVING sequence = 2 OR sequence = 4;
For a low-demand application that will probably work, but for a higher-demand application there is a high likelihood that we will end up overwriting our sequence variables mid-query. In order to put this into production, therefore, we’ll probably want to come up with a way to generate our sequence variables’ labels (eg. ‘a’) in a way that avoids such conflicts.