Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Suddenly I have duplicate records

Quote Reply
Suddenly I have duplicate records
Hi,

I'm running LinkSQL 2.1.1 and have just run into a problem where each and every one of my 10481 links is listed twice, so the total links count displayed is actually 20962! The individual duplicates have the same linkid as the originals and they DO NOT appear in the MySQL database. See http://www.aeroseek.com/links/

I noticed this problem when I was performing my usual maintenance and ran nph-build.cgi --repair. All of a sudden I received messages stating that "category xyz should have 200 links but has 100 -repairing", etc...

Any help would be appreciated.
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Hi,

In Database > MySQLMan , how many records does it show in the lsql_Links and lsql_CatLinks tables?

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] Suddenly I have duplicate records In reply to
Hi Andy,

lsql_links: 10725
lsql_CatLinks: 21296

Thanks for looking into this.
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Mmm... all I can suggest, is writing a script to "clean" the duplicates out. Something like this should work (call it cleanup.cgi, or something);

Code:
#!/usr/local/bin/perl

use strict;
use lib './';
use Links qw/$IN $DB $CFG/;
use Links::Plugins;
use Links::SiteHTML;
use CGI::Carp qw(fatalsToBrowser);

local $SIG{__DIE__} = \&Links::fatal;

Links::init('./');

my $seen;
my $sth = $DB->table('CatLinks')->select( ['DISTINCT(LinkID)'] ) || die $GT::SQL::error;
while (my $hit = $sth->fetchrow_hashref) {
next if $seen->{$hit->{LinkID}};
$seen->{$hit->{LinkID}} = 1;
$DB->table('CatLinks')->delete( { LinkID => $hit->{LinkID} } ) || die $GT::SQL::error;
$DB->table('CatLinks')->insert( $hit ) || die $GT::SQL::error;
print "Updated ID: $hit->{LinkID} \n";
}

print "Done.." . $sth->rows();

Then run from SSH/Telnet, with;

cd /path/to/admin
perl cleanup.cgi

NB: If you have links that are assigned to multiple categories, this MAY LEAVE THEM ONLY IN ONE CATEGORY!

BE SURE TO BACKUP YOUR DATABASE FIRST THOUGH

Hope that helps.

CheersSmile

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!

Last edited by:

Andy: Jul 20, 2005, 8:57 AM
Quote Reply
Re: [Andy] Suddenly I have duplicate records In reply to
Tried it and got the following error:

[Thu Jul 14 14:11:08 2005] cleanup.cgi: syntax error at www/cgi-bin/admin/cleanup.cgi line 17, near "$DB"
[Thu Jul 14 14:11:08 2005] cleanup.cgi: syntax error at www/cgi-bin/admin/cleanup.cgi line 17, near "} }"
[Thu Jul 14 14:11:08 2005] cleanup.cgi: Execution of www/cgi-bin/admin/cleanup.cgi aborted due to compilation errors.

thanks
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Hi,

Sorry, this;

Code:
$seen->{$hit->{LinkID} = 1;

..should be;

Code:
$seen->{$hit->{LinkID}} = 1;

Hope that helps :)

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] Suddenly I have duplicate records In reply to
It didn't quite work. Categories listing stated that I have x number of links, but there was nothing listed. In other words the DB was wiped. I'm restoring it now.

Do you have any other ideas?
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
I restored the DB w/o problems.
I checked the lsql_links and lsql_CatLinks tables again in phpMyAdmin. lsql_links shows no duplicates and is fine. lsql_CatLinks is the culprit and has a duplicate of every record.

I tried deleting one of the duplicates manually, but when I did that, it deleted BOTH of the records. Not good.
I believe your script was supposed circumvent that, but it didn't quite get it right.

I would sincerely appreciate you looking into this further. I can't understand why this problem just appeared out of nowhere.
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Here is an interesting thing:

I manually inserted a record into lsql_CatLinks and it automatically duplicated itself! Anybody know why?
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Mmm.. that does sound dodgy. Sounding more and more like a MySQL problem Unsure

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] Suddenly I have duplicate records In reply to
Yeah it does, but the funny thing is lsql_CatLinks is the ONLY table that duplicates records when they are inserted manually. NONE of the other tables do it....do you know of ANYONE who might be able to help me figure out this problem?
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
OK, making some progress.
Talked to my hosting support about the problem and they re-set MySQL and the duplication no longer occurs Smile Also, I can now delete the duplicates manually and with your code, Andy. However, your code strips the CategoryId values and makes them all zeros. If that little problem could be fixed, I'll be all set! Smile
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
Hi,

Try the script here again;

http://www.gossamer-threads.com/perl/gforum/gforum.cgi?post=284007#284007

As far as I can see, that should work fine =)

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] Suddenly I have duplicate records In reply to
Nope...that didn't work either Unsure It still set the number of links in each category to zero:

Started at Tue Jul 26 14:34:59 2005.
Building HTML Pages ...
Checking category stats ...
Category Air Carriers should have 0 links, but is set to 1218, repairing... 0 ok!
Category Employment should have 0 links, but is set to 206, repairing ... 0 ok!
Category GA and Sport Aviation should have 0 links, but is set to 228, repairing ... 0 ok!
ETC...

Here is what I'm running:
Code:

#!/usr/bin/perl
use strict;
use lib '/home/aeroseek/public_html/cgi-bin/admin';
use Links qw/$IN $DB $CFG/;
use Links::Plugins;
use Links::SiteHTML;
use CGI::Carp qw(fatalsToBrowser);

local $SIG{__DIE__} = \&Links::fatal;
Links::init('./');
my $seen;
my $sth = $DB->table('CatLinks')->select( ['DISTINCT(LinkID)'] ) || die $GT::SQL::error;
while (my $hit = $sth->fetchrow_hashref) {
next if $seen->{$hit->{LinkID}};
$seen->{$hit->{LinkID}} = 1;
$DB->table('CatLinks')->delete( { LinkID => $hit->{LinkID} } ) || die $GT::SQL::error;
$DB->table('CatLinks')->insert( $hit ) || die $GT::SQL::error;
print "Updated ID: $hit->{LinkID} \n";
}
print "Done.." . $sth->rows();

Do you see anything wrong with this code?
Jack R.
http://www.aeroseek.com/
Quote Reply
Re: [aeroseek] Suddenly I have duplicate records In reply to
It's all better now Cool
I dug out the old MySQL book and figured out how to delete the duplicates.
Basically I exported lsql_CatLinks into a temporary table and used the DISTINCT parameter.

Code:
mysql> CREATE TABLE lsql_CatLinksTemp
SELECT DISTINCT lsql_CatLinks.LinkID, lsql_CatLinks.CategoryID
FROM lsql_CatLinks;


Then I renamed lsql_CatLinks to lsql_CatLinksOriginal and renamed lsql_CatLinksTemp to lsql_CatLinks
Worked like a charm Cool

Andy, thank you for your assistance.
Jack R.
http://www.aeroseek.com/

Last edited by:

aeroseek: Jul 26, 2005, 2:53 PM