Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

nph-build problem -- abort or looping

(Page 1 of 2)
> >
Quote Reply
nph-build problem -- abort or looping
Some people have reported a problem with the build process. Either it aborts, or it endlessly loops.

Eliot had a problem, and some others did too. This is centered in the

sub build_category_pages

On Eliot's system, changing the $limit variable from 100 to:

$limit = &Links::DB_Utils::get_totalcats;

helped with the aborted "build" process, but then the process looped. It built all the categories, then started again.

The problem seems to be in the loop that wraps the actual page builds:

Code:
while (1)
...
last unless .....
foreach {
....
}

$offset++;
}
The problem seems to be that the while loop is _never_ exited. The "last unless" condition is never met.

The only exit condition is the $offset++.

For whatever reason, it works on my system and hundreds of others, but the exact code pasted in to Eliot's didn't -- and seems to bomb on random manchines.

The "hack" solution was to comment out the 3 lines of the while loop:

Code:
## while (1)

### last unles ...

##}
This eliminates any test for category existence, but if your site is set up, and has categories, it shouldn't bomb.

I _wish_ someone would explain this code to me..... use the ORIGINAL unaltered zip file, and explain it!

It's bugged me since day 1, that I couldn't understand this loop, and it seems to be an intermittant problem on some people's systems.

Alex, I don't want to bug you with this... the next release is more important, but if you do see this, and can answer... some permanent _fix_ or explanation would help. There have been at least 3 great minds driven crazy by this for quite some time :)

It has to be centered in the first 3 lines of the while loop, and the $offset++ line, since those are the only lines that have any effect on what is happening.

I just don't see it!!! (putting on dark glasses and breaking out white cane)

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
I would like to thank pugdog for addressing this issue and for fixing my problem. And I am stumped as well...I looked all over the codes to find where (1) is defined...and don't see it.

I was thinking (I know...dangerous Wink), pugdog, that the reason that this is not a wide spread problem is that most Links SQL users are upgrading their sites from earlier versions of the flat file version of Links and they are using their current category structure. I don't know...just a thought.

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
If it helps for comparison, here's my code (LSQL 1.11 stable):

Code:
sub build_category_pages {
# --------------------------------------------------------
# This routine builds all the category pages. Each category uses
# the same template which is defined in &site_html_category.
#
my ($s, $e, $f, $limit, $offset, $get_related, $subcat_info, $get_links, $get_alt);

print "Building category pages ... \n\n";
$s = time();

$limit = 100; $offset = 0;
$subcat_info = $CATDB->prepare ( qq!
SELECT Category.*
FROM Category, CategoryHierarchy
WHERE CategoryHierarchy.CategoryID = ? AND
CategoryHierarchy.Depth = 1 AND
CategoryHierarchy.SubCategoryID = Category.ID
!) or die "Can't prepare: $DBI::errstr";
$get_related = $LINKDB->prepare (" SELECT Category.Name FROM CategoryRelations, Category WHERE CategoryRelations.CategoryID = ? AND CategoryRelations.RelatedID = Category.ID");
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_alt = $LINKDB->prepare (" SELECT l.* FROM Links as l, CategoryAlternates as c WHERE l.ID = c.LinkID AND c.CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");

my ($categories_r, $links_r, $category_r, $subcategory_r, $alt_r, $total_links);
my ($directory, $url, $numlinks, $page_num, $tmp, $name, %OUT);

while (1) {
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset+1, sb => 'Name', so => 'ASC', tb => 0 });
last unless ($categories_r and @{$categories_r} > 0);

foreach $category_r (@{$categories_r}) {

# Get the subcategory info and store as a ref to a list of array refs.
$category_r = $CATDB->array_to_hash ($category_r);
$subcat_info->execute($category_r->{'ID'});
$subcategory_r = undef;
if ($subcat_info->rows()) {
$tmp = $subcat_info->fetchall_arrayref();
foreach my $cat (@{$tmp}) { $subcategory_r->{${$cat}[1]} = $CATDB->array_to_hash($cat) }
}
Wasn't sure if there may be a slight version difference, maybe not?

All the best
Shaun

Quote Reply
Re: nph-build problem -- abort or looping In reply to
I am also using Links v1.11 stable version and the codes are exactly the same.

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Hello all,

Maybe if you haven't a category with ID 1 there is problem, if you have a category with ID 1 it's fine ?

Sounds logically.. but it's really a wild wild quess !!

What's the jackpot?

Regards Startpoint.

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Nope that's not it...My Category table contains entries from 1 - 410.

But good guess! Smile

Next? Wink

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Is it related to 'Alt' categories? I don't use them and haven't had any trouble, do the other's who've had the same problems use 'Alt's' as well?

All the best
Shaun

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Doesn't matter in my case...the looping occurred with both records in the CategoryAlternates table and no records in the CategoryAlternates table.

Good guess though....

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Just in case people are confused:

Code:
while (1) {
....stuff....
}
Just means loop forever. 1 is always "true", so the while loop
always runs. You need an internal exit condition to exit the loop,
hence the

last unless ... stuff..;

As I said before, the only thing that appears to be changed, is
that $offset is incremented

$offset++;

between the end of the foreach loop (where the categories are iterated) and the next iteration of the 'while' loop -- which is _never_ supposed to occur.

But, $offset is also incremented (has one added to it's value before being used) in the ->query statement. Again, I can't figure that out.

It would seem it was either a hold-over from 'old' logic that was re-done for the release, or it was a first step to some new logic that wasn't implemented. I just don't get it....

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Hi pugdog,

It works the following way:

$offset=0; # Logic Bug : should be set to 1
$limit=100; # Number of results to handle in one loop
...
The main logic is to keep memory usage low. $limit=100; is used to define how many categorys are loaded to memory and stored in the array to be built. If you set it to low it will increase your building time because you have to query the database to often. In nph-build.cgi the number of SQL querys = (Number of Categorys) / ($limit).
In your solution you set $limit= number of categorys. This means the whole recordset with all categories are loaded to memory. When you have many categories you will get memory errors. (Im not shure when perl gives up. But every programming language has its definitions of the maximum size of an array or hash)


WHILE(1) # will always be TRUE
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset+1,.... # should be $offset,
# selects all categorys LIMIT ($offset * $limit), $limit
# see DBSQL.pm sub query
# and returns $limit categorys to work with in the next foreach loop
$offset++;
# increments lets call it the page counter
# in DBSQL.pm $offset (nh) gets multiplied with $limit (mh)
# # First let's see how many hits we got.
my ($offset, $table, $query);
$offset = ($nh -1) * $maxhits;

last unless... # quit loop if no results

I think this could be the problem of some users
im MySQL if the LIMIT statement is defined:

"The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)."

Now what I didn't find is the reaction of MySQL if the offset exeeds the number of rows. Maybe older versions of MySQL asume 0 if an invalid value (>maxrows) is given.
This would be an explanation for the endless loop. It works fine however with my version.

To check this it would be nice if some user with the endless-loop problem could use their SQL-monitor in admin and try following statement:

SELECT * FROM Category LIMIT "Offset", 100
"Offset should be replaced with a number larger than the number of categorys.


A solution for users having problems with the endless-loop would be
check the number of categorys
insert an internal counter
exit the loop if the counter exeeds the num of cats

I hope it helps,
regards, alexander

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Interesting point -- "blame MySQL" <G>

Your explanation would seem to work, which is why the two changes of increasing the limit to "max_num_categories" and eliminating the loop worked. We built all the categories, up to the "max_num_categories" and travelled through the 'loop' exactly once.

One thing I didn't think of is to look at the MySQL version.

There are/have been tweaks of MySQL and how it handles these conditions through the versions. That would explain why it works for some people, but not others, in a fashion independent of the actual code -- it's the _database_ causing problems, not the code <groan>

So... everyone... cough it up. What versions of MySQL are you running?????


Now... one thing. The logic bug.

We want $offset to be =1 the _first_ time through? If so, then $offset=1, get rid of the '$offset+1' in the query. Leave the increment $offset++;

What I don't understand, is why it worked, with the offset being screwy, or, if I fix this, will categories suddenly appear? It looks like it was skipping a page, since $offset was being incremented 2x in the first loop.

Code:
$offset=0 ==> set to '1' in the first 'query'
==> incremented to 2
==> set to 3 in the second query
==> incremented to 3
==> set to 4 in the third query...

... second page '$offset=2' never executed.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Hi,

no $offset is not incremented twice.
In the original code :

$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset +1,...
$offset is not incremented, $offset+1 is passed not $offset++

the logic:
$offset=0;
call query with $offset+1 (0+1 =1)
$offset++; # $offset == 1

next loop
call query with $offset+1 (1+1 =2)
$offset++; # $offset == 2

next loop
call query with $offset+1 (2+1 =3)
$offset++; # $offset == 3
.....

which is the same as:

$offset=1;
loop
call query with $offset (1)
$offset++; # $offset == 2

next loop
call query with $offset (2)
$offset++; # $offset == 3
.....


I think the query call $offset+1 was a debug after calling query with offset=0 which will result in an error because in DBSQL - sub query the real offset is defined as (nh-1)* maxhits which would be an offset of (-1 * maxhits) = invalid

regards, alexander




Quote Reply
Re: nph-build problem -- abort or looping In reply to
Hi,

I am using MySQL 3.21.33b it works fine

btw the (LIMIT offset, num) only works (bugfree?) with versions 3.21.17 +

regards, alexander

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Alexander,

Thank you for your replies...I really appreciate it. It makes sense...at least the logic behind the codes. It is weird that the original codes did not work for me.

pugdog....I believe my hosting company has MySQL 3.21 installed.

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
I don't really know how to handle MySQL and perl scripts, but I have the same problem of aborting the building process. Could somebody tell me, what lines of the npg-build.cgi I have to change? I do not understand what you are talking, so please give me an extract of a working nph-build.cgi.

Thank you very much.

Quote Reply
Re: nph-build problem -- abort or looping In reply to
All pugdog did for me was rem out the following lines of code in the sub build_category_pages routine:

Code:

###while (1) {
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, $offset+1, sb => 'Name', so => 'ASC', tb => 0 });
###last unless ($categories_r and @{$categories_r} > 0);


Note: The bolded lines are what you need to rem out with the # sign.

I also changed $limit = 100; to the following:

Code:

$limit = &Links::DB_Utils::get_totalcats;


This is referencing a sub that I added in the DB_Utils.pl that counts the total number of categories in the Category table.

The sub you need to add in the DB_Utils.pl file is as follows:

Code:

sub get_totalcats {
#-----------------------------------------------------
my ($CATDB, $GRAND_TOTAL);
$GRAND_TOTAL = 0;
$CATDB = new Links::DBSQL "$LINKS{admin_root_path}/defs/Category.def";
$GRAND_TOTAL = $CATDB->total();
return $GRAND_TOTAL;
}


BTW: You can create a global variable in your HTML_Templates.pm like the following:

Code:

totalcats => &Links::DB_Utils::get_totalcats;


Then you can use <%totalcats%> in all your template files. Wink

Yes...the same thing can be done with links...rename the above sub, rename the .def file and global variables in that sub, then rename the global variable in the HTML_Templates.pm.

Hope this helps.

Regards,

Eliot Lee

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Thanks for the help, but it does not work, I always get a software error, for example:

Software error:
[Thu Aug 31 02:17:38 2000] HTML_Templates.pm: syntax error at Links/HTML_Templates.pm line 65, near "totalcats" BEGIN failed--compilation aborted at nph-build.cgi line 33.


What is wrong? I copied the lines out of your answer, but it does not work. Is there another possiblity? I really need to build my site today, and I just have a few hours left.

Thanks in regard,
Andreas

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Code:
totalcats => &Links::DB_Utils::get_totalcats;
This is a problem of typing too fast, and knowing what you want to say, but not doing it right -- we are all guilty of it... just think what he _meant_ to do...

in the %GLOBALS hash, where you pass all the values to the templates, you want to add a line near the top:

Code:
totalcats => &Links::DB_Utils::get_totalcats,
NOTE: The comma _MUST_ be there, and if it's the LAST entry, there
MUST BE NO comma. So add this near the top, and you'll be ok.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Thanks! But sorry, I get an error. I give you the scripts, where I put the changes, maybe you know, what I've done wrong:


in the ngh-build.cgi:

sub build_category_pages {
# --------------------------------------------------------
# This routine builds all the category pages. Each category uses
# the same template which is defined in &site_html_category.
#
my ($s, $e, $f, $limit, $offset, $get_related, $subcat_info, $get_links, $get_alt);

print "Building category pages ... \n\n";
$s = time();

$limit = &Links::DB_Utils::get_totalcats; $offset = 0;
$subcat_info = $CATDB->prepare ( qq!
SELECT Category.*
FROM Category, CategoryHierarchy
WHERE CategoryHierarchy.CategoryID = ? AND
CategoryHierarchy.Depth = 1 AND
CategoryHierarchy.SubCategoryID = Category.ID
!) or die "Can't prepare: $DBI::errstr";
$get_related = $LINKDB->prepare (" SELECT Category.Name FROM CategoryRelations, Category WHERE CategoryRelations.CategoryID = ? AND CategoryRelations.RelatedID = Category.ID");
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_alt = $LINKDB->prepare (" SELECT l.* FROM Links as l, CategoryAlternates as c WHERE l.ID = c.LinkID AND c.CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");

my ($categories_r, $links_r, $category_r, $subcategory_r, $alt_r, $total_links);
my ($directory, $url, $numlinks, $page_num, $tmp, $name, %OUT);

###while (1) {
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset+1, sb => 'Name', so => 'ASC', tb => 0 });
###last unless ($categories_r and @{$categories_r} > 0);

foreach $category_r (@{$categories_r}) {



in the DB_Utils.pm:

sub get_totalcats {
#-----------------------------------------------------
my ($CATDB, $GRAND_TOTAL);
$GRAND_TOTAL = 0;
$CATDB = new Links::DBSQL "$LINKS{admin_root_path}/defs/Category.def";
$GRAND_TOTAL = $CATDB->total();
return $GRAND_TOTAL;
}


sub cgi_to_hash {
# --------------------------------------------------------
# Converts a CGI object to a hash ref.
#
my $in = shift;
my %rec = ();
foreach ($in->param) { $rec{$_} = join "\0", $in->param($_); }
return \%rec;
}



and in the HTML_Template:

# You can put variables here that you would like to use in any
# of your templates.

%GLOBALS = (
date => \&Links::DBSQL::get_date,
time => \&Links::DBSQL::get_time,
db_cgi_url => $LINKS{db_cgi_url},
build_root_url => $LINKS{build_root_url},
site_title => $LINKS{build_site_title},
css => $LINKS{build_css_url},
totalcats => &Links::DB_Utils::get_totalcats,
banner => ''

);


Quote Reply
Re: nph-build problem -- abort or looping In reply to
The error you gave above is in the HTML_Templates.pm file.

That is after you make the call to write out the template, so you must have done something when adding in the variable.

Make sure you have a:

use Links::DB_Utils;

at the top of your HTML_Templates.pm file. I don't remember if that is in there by default, or if we've added it over time.

Also, you need to add:

&get_totalcats

To the @EXPORT array at the top of DB_UTILS.pm ... just spaces, no commas, just follow the other examples.





http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
I don't know, what's wrong, but I get a software error:

Execution of nph-build.cgi aborted due to compilation errors.

The use Links::DB_Utils; at the HTM_Template is there by default, but I added - as you suggested - the &get_totalcats in the DB_Utils. But it doesn't work.

In the nph-build.cgi if have this line:

$limit = &Links::DB_Utils::get_totalcats; $offset = 0;

Do I have to delete $offset = 0 or should it be there? If I turn the limit ($limit = x) up to the number of my categories, it doesn't work to. I'm just playing around with these script, but I do not really know what I am doing.

Is it possible that you could send (or post here) a complete extract of the nph-build.cgi, DB_Utils and HTML_Templates? Just all the lines, that matter. That would be really great. In Germany it is now just in the beginning of the morning, and I would really like to go to bed. Yesterday I did the same....

Please help!




Quote Reply
Re: nph-build problem -- abort or looping In reply to
The problem is that most of our files will not run on other sites.

We've added extra codes...

The logic is there, you need to maybe get some sleep, then try again.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Hi Andreas,

1. Well one problem I see in the mod is that you remmed out the beginning of the WHILE(1) loop and maybe forgot to rem the closing bracket of it.

While(1) { # start of loop with opening bracket
...
... # the whole bunch of code
} # the closing bracket

to kill the loop

# While(1) { # start of loop with opening bracket
...
... # the whole bunch of code
# } # the closing bracket

2. Are you shure to do it this way, Loading the whole bunch of categories into memory and build the pages???

Here is what I would suggest:

in the beginnig of the sub build_category_pages define a counter and a totalhits variable.

my $counter = 0;
my $totalhits =0;


now change the while loop

old code: while(1){
new code: while($counter <= $totalhits){

after the category query statement
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh ......
insert:

$totalhits = $CATDB->hits();

after the inner foreach loop
foreach $category_r (@{$categories_r}) {

insert:

$counter ++;

with this modification you are able to use the $limit and don't need to waste memory ( and will not get problems with your ISP)

I hope it helps,
regards Alexander

Quote Reply
Re: nph-build problem -- abort or looping In reply to
Thanks a lot! I'll try it this night, and give you a reply if it works. During the day it is just not possible to "build all", it always stops while "calculating category stats". Maybe my host provider is too slow.

BTW: what is done with "calculating category stats"? Can one skip this or is it necessary? I also do not need the votes and the ratings, can I deactivate this in the building option (only if it is faster)?

Quote Reply
Re: nph-build problem -- abort or looping In reply to
The short answer is _yes_ in the 1.11 version you really need to calculate the category stats for the site to build properly.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

> >