The things you learn on IRC

When I mentioned in the #postgresql channel that FreshPorts/FreshSource had moved to PostgreSQL, the first question was: where’s your Powered By PostgreSQL logo? My reply? At the bottom of the FreshPorts about page. Granted, those logos could be a little higher up on the page.

But that’s not what prompted this post. Someone actually found a problem with the FreshPorts code. larryrtx (aka Larry Rosenman) found a problem when added a new port to his watch list. What he did was:

  1. login
  2. click on Watch list – Categories
  3. click on net-mgmt
  4. check iog
  5. click on submit

BANG!

The error displayed was “error clear list before saving”.

Luckily, I was able to repeat the same error. It did not occur on PostgreSQL 7.4.7. I check the code, and found that string. It was in with stuff like this:

$result = pg_exec ($db, $sql);
${"port_".$value} = 1;
if (!$result) {
  syslog(LOG_ERROR, $_SERVER["PHP_SELF"] . ": could not clear watch list '$wlid' owned by '$Use->id' of element '$value' in " . __FILE__ . ':' . __LINE__);
  die("error clear list before saving");
}

I tracked down the error to an SQL issue. In the logs I found:

Jan 20 07:37:38 havoc postgres[56873]: [1-1] ERROR: missing FROM-clause entry for table "ports_categories"

OK. Let’s keep looking. In the web server logs I found:

[Fri Jan 20 07:37:38 2006] [error] PHP Warning: pg_query(): Query failed: ERROR: missing FROM-clause entry for table "ports_categories" in /path/to/somewhere/watch_list.php on line 144

If you look at the code, you’ll see something like this:

DELETE FROM watch_list_element
WHERE ports_categories.category_id    = $CategoryID
  AND ports_categories.port_id        = ports.id
  AND ports.element_id                = watch_list_element.element_id
  AND watch_list.id                    = $WatchListID
  AND watch_list.user_id              = $UserID
  AND watch_list_element.watch_list_id = watch_list.id";

I changed the query to include a USING clause:

DELETE FROM watch_list_element
WHERE ports_categories.category_id    = $CategoryID
USING ports_categories, ports, watch_list
  AND ports_categories.port_id        = ports.id
  AND ports.element_id                = watch_list_element.element_id
  AND watch_list.id                    = $WatchListID
  AND watch_list.user_id              = $UserID
  AND watch_list_element.watch_list_id = watch_list.id";

Fixed. Done. Thanks larryrtx.

larryrtx then found another problem. If you have no default watch list, and you try to remove something from your watch list, it will not be removed. This will only happen if you also select the option “When clicking on Add/Remove for a port, the action should affect the default watch list[s]”.

I’m not sure what to do about this issue…

But that’s not all. yain (Alexey Dvoychenkov) suggested that FreshPorts notify users of new categories. Now that’s a nice idea!

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

Leave a Comment

Scroll to Top