Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Inaccurate links count

(Page 1 of 2)
> >
Quote Reply
Inaccurate links count
I am getting an inaccurate links count for the tag <%grand_total%> on the home.html template

I have:

Verified Links
Rebuild Search
Repair Tables

All is good the program states there are 4,400 links in the database but the home page is saying:

There are 11349 links for you to choose from!

Can some one assist me in correcting this issue.

Thank you

Sandra R


Sandra Roussel
Chonsa Group Design - Fresh Start Housing

Last edited by:

SandraR: Oct 13, 2004, 12:22 PM
Quote Reply
Re: [SandraR] Inaccurate links count In reply to
Very odd. <%grand_total%> is just taken from a table count (on the "lsql_Links" table I believe).

You definate its not a cache problem with mod_perl or similar?

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Inaccurate links count In reply to
Hi Andy- I'm working with Sandra on this project. The problem is that lsql_Links has that number in the table. I created categories and uploaded a datafeed. The information from the datafeed appeared messed up, so I deleted the links using the Database/Links/Delete search feature from the admin menu. The links were removed, but the count wasn't adjusted. Also deleted the categories and started over.
Quote Reply
Re: [nmjudy] Inaccurate links count In reply to
Bump - does anyone have an answer for this isse?


Sandra Roussel
Chonsa Group Design - Fresh Start Housing
Quote Reply
Re: [SandraR] Inaccurate links count In reply to
Sandra,

I don't have an answer but I have had the same problem and it seems to be stemming from the CatLinks table. Below is what I observed.
  • I had a number of categories with items created.
  • I deleted them from the Category Browser and the CatLinks table reflected 1362 before the delete
  • Rebuild, Repair
  • Create new Categories
  • grandtotal count was off by 1362 once I imported items into the new categories.
  • LinkID an CatID in CatLinks table did not start at 0 or 1363 (not sure how it's supposed to index after deleting categories)

That's as far as I've gotten on my testing. I was going to try and replicate the problem but haven't had a chance to try it..

Maybe that helps some of the guru's out???
Tony
Quote Reply
Re: [SandraR] Inaccurate links count In reply to
Hi,

This is the global used to calculate the value for grand total:


sub _grand_total {
# -------------------------------------------------------------------
# Calculates the total in three queries as it can be significantly faster
# on large db's.
#
my $total = $DB->table('CatLinks')->count - $DB->table('Links')->count({ isValidated => 'No' });
if ($CFG->{payment}->{enabled}) {
$total -= $DB->table('Links')->count(
GT::SQL::Condition->new(
ExpiryDate => '<' => time,
isValidated => '=' => 'Yes'
)
);
}
return $total;
}

This means that it uses the CatLinks table and the Links table. I assume that when you deleted the categories you removed the entries in the CatLinks table? As far as I can see this counts links that are in multiple categories multiple times - could this be the problem? If so, you could just use a new global that counts the number of links in the Links table:

sub {
my $total;
if ($CFG->{payment}->{enabled}) {
$total = $DB->table('Links')->count(
GT::SQL::Condition->new(
'ExpiryDate' => '>=' => time,
'isValidated' => '=' => 'Yes'
)
);
} else {
$total = $DB->table('Links')->count(
isValidated => 'Yes'
); }
return $total;
}

Last edited by:

afinlr: Oct 14, 2004, 2:59 PM
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
Hi Afinlr,

When I deleted my categories I simply deleted the category from the Browse window. I wasa under the impression that this would remove all of the associations in all tables including the CatLinks table. Is that a wrong impression on my part and should I be removing them in a different manner?

Tony
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
No that should be fine - the problems with CatLinks only occur if you delete records from tables outside Links, eg in mysqlman.

However, I still the that the reason your total is not correct might be that you have links in multiple categories - do you?

Last edited by:

afinlr: Oct 14, 2004, 5:07 PM
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
Each link was only in one category so I don't think that was my problem. As for the process for deleting categories I did it in the browser and then after seeing the error I started digging in with MySQLMan and saw that the linkid and the categoryid were still in the table.

Then I tried to use mysqlman and delete the 1362 linkid's that were the differnece in my count and got an error that said something like "category no longer exists". The only way I could get rid of them was through phpMyAdmin .. That's not to say I wasn't doing something wrong in MySQLman since I'm still getting my feet wet Cool ..
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
Agree with Tony all links were deleted utilizing the Admin panel, is this perhaps a bug?


Sandra Roussel
Chonsa Group Design - Fresh Start Housing
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
In Reply To:
Hi Afinlr,

When I deleted my categories I simply deleted the category from the Browse window. I wasa under the impression that this would remove all of the associations in all tables including the CatLinks table. Is that a wrong impression on my part and should I be removing them in a different manner?

Tony

It all depends on how large the category is.

I wrote a script for Sandra a while back, which deletes all the links in a specific category (run via SSH). Let me know if you need a copy Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [SandraR] Inaccurate links count In reply to
I've just done a new install and tested this and it seems to be working as far as I can see, so I don't think it is a bug.

If you are deleting categories and finding that the entries in the CatLinks table are not being deleted, it sounds like you are missing the foreign key relationship between the CatLinks table and the Category table - although I'm not sure how this could have happened. To check, go to admin/defs/CatLinks.def and see whether you have

'fk' => {
'lsql_Category' => {
'CategoryID' => 'ID'
},
'lsql_Links' => {
'LinkID' => 'ID'
}
},

near the bottom of the file; and in admin/defs/Category.def you should have something like

'fk_tables' => [
'lsql_CatPrice',
'lsql_CatLinks',
'lsql_CatRelations',
'lsql_Editors'
],

If you have both of these then I'm not quite sure why it isn't working.
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
My CatLinks.def matches yours but my Category.def does not match yours. Here is what mine looks like

'fk' => {},
'fk_tables' => [],
'index' => {
'catndx' => [
'Name'
],
'fthrindex' => [
'FatherID'
],
'namndx' => [
'Full_Name'
]
},
'pk' => [
'ID'
],
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
OK - that's the problem. You need to edit the def file and add the tables in.
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
afinlr,

I haven't made any changes to my def files so do you know if this is a known issue or how this might have happened?
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
No, sorry, I really don't know how this might have happened.
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
Well I got overly excited that afinlr's solution cured all of my problems. It did cure the problem of the grand_total net reflecting the deletion of entire categories but I have since had time to go back and do some testing.

I was able to duplicate Sandra's problem exactly. Here is what I did to test.

Imported about 5000 links
Repair, Rebuild
grand_total reflected right number
Database/Links/Delete search
Deleted links and count wasn't adjusted
Repair, Rebuild
grand_total still reflected initial number..

Any ideas?
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
Are the foreign key tables still in your Category.def file - at some point in the past your Category.def file was obviously changed - so I wonder whether the thing that initially changed it might have done it again since you put the changes in there?

Edit: Having just reread your post I see that you are just deleting links and not categories. Can you check your Links.def file and see what the fk tables etc contain at the bottom of the file - you should have something like this:

'fk' => {
'lsql_Users' => {
'LinkOwner' => 'Username'
}
},
'fk_tables' => [
'lsql_Changes',
'lsql_Reviews',
'lsql_CatLinks',
'lsql_Verify',
'lsql_Payments'
],

Last edited by:

afinlr: Oct 18, 2004, 2:06 PM
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
Well, once again you seem to have hit the nail on the head. For whatever reason my code was not there and after making those changes the links seem to be deleting and reflecting the decrease in the grand_total..

So that I don't cross this road again I took a look at some of my other .def files. All of the ones I looked at have the following line rather than the series of lines you have shown me needed adding.

'fk_tables' => [],

I guess that brings me to two questions..

1) Should I replace all .def files code with the code you have shown me works to prevent any future problems?

2) Is there a way to get the count synched back up or do I have to Empty all tables except the Category table?

Thanks again,
Tony
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
Hi,

No don't add those lines to any other def file - they are all table specific. In a new install I think that the only def files that should have an entry in fk_tables are Category, Links, Payments and Users. And the defs files that should have an entry for 'fk' are CatLinks, CatPrice, CatRelations, Changes, Editors, Links, PaymentLogs, Payments, Reviews, Sessions and Verify.

To get the count synced back up - if you have deleted all your links then you should empty the CatLinks table.

Laura.
The UK High Street
Quote Reply
Re: [afinlr] Inaccurate links count In reply to
Yes, that's most likely the problem. A while ago, we found a bug in the SQL code that in certain situations, recreating/syncing your database makes it lose the fk_tables entries. For Links SQL, this means the following tables should look like:
Code:
Category:
'fk_tables' => [
'lsqldev_CatPrice',
'lsqldev_CatLinks',
'lsqldev_CatRelations',
'lsqldev_Editors'
],

Links:
'fk_tables' => [
'lsqldev_Payments',
'lsqldev_Changes',
'lsqldev_Reviews',
'lsqldev_CatLinks',
'lsqldev_Verify'
],

Payments:
'fk_tables' => [
'lsqldev_PaymentLogs'
],

Users:
'fk_tables' => [
'lsqldev_Links',
'lsqldev_Changes',
'lsqldev_Reviews',
'lsqldev_Editors',
'lsqldev_Sessions'
],

Adrian
Quote Reply
Re: [brewt] Inaccurate links count In reply to
Adrian,

You mention that in certain situations you have seen this bug.. Is it something I'm doing wrong and can prevent in the future??

BTW, every one of my tables, other than the ones afinlr already had me fix, had the missing parameters you indicated should be in there..

Thanks again to everyone for their help..

Tony
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
It happens when you do a database overwrite over an existing database (admin => Setup => SQL Server), and also if you resync the def files. If you avoid doing this, then you shouldn't run into this problem again.

Adrian
Quote Reply
Re: [tondelo] Inaccurate links count In reply to
Tony-
Was wondering what def files you changed to get the count corrected. I've read through the post and understand how the problem occurred - but don't have a firm handle on what info in which defs need to be changed.

Thanks!
Quote Reply
Re: [nmjudy] Inaccurate links count In reply to
Judy,

I checked all of the def files that brewt listed " Category, Links, Payments and Users". In my case all of them were at

'fk_tables' => [],

Once I made the changes to all the tables everthing worked great.. So far I have added/deleted thousands of links and every thing has adjusted itself accordingly..

Tony
> >