Sep 142006
 

I got into trouble yesterday. Or rather, FreshPorts got into trouble because of me. It all centered around a new category called net perl5. I first notice the problem when I received an SQL error message:

ERROR: null value in column "last_update" violates not-null constraint
CONTEXT: SQL statement "INSERT INTO category_stats ( SELECT categories.id AS category_id, count(ports_active.id) AS count,
max(commit_log.commit_date) AS updated FROM categories, ports_active left outer join commit_log on (
ports_active.last_commit_id = commit_log.id ) WHERE categories.id = ports_active.category_id AND categories.is_primary GROUP BY
categories.id, categories.name, categories.description, is_primary, categories.element_id UNION SELECT categories.id AS
category_id, count(ports_active.id) AS count, max(commit_log.commit_date) AS updated FROM ports_categories, categories,
ports_active left outer join commit_log on ( ports_active.last_commit_id = commit_log.id ) WHERE ports_active.id =
ports_categories.port_id AND categories.id = ports_categories.category_id AND NOT categories.is_primary GROUP BY categories.id,
categories.name, categories.description, is_primary, categories.element_id)"

Yes, that’s pretty hard to read, but I don’t apologize. That’s what I had to work with. My first thought was about a category without any commits against it. But that can’t be right. New categories are only ever created when a port is committed against that category. So what is going wrong?

I approached this by running the subquery [from the above code] to see which category was coming up empty. I found it: net perl5. My thoughts: OK, that’s an error. Catgories don’t look like that. Somehow the data is messed up.

I started looking at the ports_categories table to see what port was involved. I found one: net/p5-Net-Analysis. Browsing to that URL, I found it was a non-port, or so the website thought. Clearly it was supposed to be *a* port. I checked the commit message and then the Makefile. It was there that I discovered the CATEGORIES setting contained a tab between net and perl5.

I went searching for the code that set values in the ports_categories table. It’s in a trigger for the ports table. Within that trigger is code that looks like this:

Category := split_part(CategoryNames, ‘ ‘, CategoryCount);

That is splitting the CATEGORIES clause based upon spaces. In this situation, that is a problem. Rather than change this code, I went to the source. I changed the data going into th database. I used a perl regular expression to convert all whitespace to a simple space:

$this->{categories} =~ s/\s+/ /g;

To fix the existing situation I reran the commit through FreshPorts. This fixed up the net/p5-Net-Analysis page. Then I deleted the funny category net perl5.

That seems to have fixed the problem completely.

The odd thing is, I had this recollection of seeing the create category message go by, but I couldn’t find it yesterday. Today, as I started this article, I found it:

NOTICE: we need to create category net perl5

That was my clue right there. But I missed it.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive