[2.0.21] Rebuild Search

All new MODs released in our MOD Database will be announced in here. All support for released MODs needs to take place in here. No new MODs will be accepted into the MOD Database for phpBB2
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.

Rating:

Excellent!
58
81%
Very Good
10
14%
Good
3
4%
Fair
0
No votes
Poor
1
1%
 
Total votes: 72

belzecue2
Registered User
Posts: 13
Joined: Sun Nov 20, 2005 4:57 pm
Location: Australia
Contact:

Re: A replication fly in the msaccess ointment

Post by belzecue2 »

chatasos wrote: Hi belzecue2,

I think i cannot quite understand you problem.
You said that you applied replication to your db and that all auto-increment values were changed to random values. If this is the default behaviour of msaccess replication, shouldn't all other phpbb tables (posts, topics, etc.) have problems too? Many phpbb tables use auto-increment values.


When you convert an Access db for replication, all 'autoincrement' fields get altered to 'random' values. Example values for an insert to a random datatype field are: 1423244298, -867554, -127854567, 43556 etc. When converting the original db, Access is smart enough to work out what foreign key values it needs to change. Having a relational table mapping probably helps. My PHPBB has been running without problems since I converted the original msaccess db for 2.0.18.

In your code, you assume the minimum 'start_post_id' is 1. Because the post_id in a replicated msaccess post table is a random value (signed large integer), the first record in the table is of course not going to be 1. Nor is the last post in the table going to be the current max post_id. The sequence could be that one from the previous paragraph.

For a post_id field with a random datatype, you would need to process the whole table from first record to last record, not rely on the post_ids being sequential, and not assume the logic of first post_id = 1 and last post_id = max(post_id).

Of course, this makes multiple incremental rebuild passes impossible -- when ending at post_id X, you can't tell the rebuild process to restart at post_id X+1 because in this case post_id is *not* in sequencial order.

This is a very unique case (having a replicated msaccess db) so I don't think you should waste time modifying your code to cope with it. Eventually, my db will be upsized to sqlserver and the post_id and other affected columns will be converted back to identity/autoincrement. Then I'll be able to use your excellent mod! :-)
BondGamer
Registered User
Posts: 436
Joined: Mon Dec 15, 2003 7:49 pm
Contact:

Post by BondGamer »

I used this mod instead of re-installing our search tables, it worked great.
My phpBB3 Mods: - None active =[
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Re: A replication fly in the msaccess ointment

Post by chatasos »

belzecue2 wrote: When you convert an Access db for replication, all 'autoincrement' fields get altered to 'random' values. Example values for an insert to a random datatype field are: 1423244298, -867554, -127854567, 43556 etc. When converting the original db, Access is smart enough to work out what foreign key values it needs to change. Having a relational table mapping probably helps. My PHPBB has been running without problems since I converted the original msaccess db for 2.0.18.

That is quite a strange behaviour!!!
belzecue2 wrote: For a post_id field with a random datatype, you would need to process the whole table from first record to last record, not rely on the post_ids being sequential, and not assume the logic of first post_id = 1 and last post_id = max(post_id).

Of course, this makes multiple incremental rebuild passes impossible -- when ending at post_id X, you can't tell the rebuild process to restart at post_id X+1 because in this case post_id is *not* in sequencial order.


Just to clarify some things here:
When you configure the mod to start from post_id=0 (or X), it actually means to start from the post_id that is right after post_id=0 (or X), which can mean post_id 1 (or X+1), 10 (or X+10), 50 (or X+50) and so on. :wink:

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
belzecue2
Registered User
Posts: 13
Joined: Sun Nov 20, 2005 4:57 pm
Location: Australia
Contact:

modding the mod for replication id

Post by belzecue2 »

When you configure the mod to start from post_id=0 (or X), it actually means to start from the post_id that is right after post_id=0 (or X)


:D

yep. Last night I started examining and rewriting the mod to cope with random replication post ids, but when I realised I only had a maximum of 4 hours sleep available, I dragged myself off to bed :lol:

I'll have another crack at it tonight. What I'll end up doing is reducing it to a simple one-pass process through the whole table, first to last record. Won't be difficult -- mind you, that's what I said to myself last night when I started modding a Watched Topic mod and ended up getting only 4 hours sleep :roll: I'm new to PHP and modding is an addictive way to learn.
foilzone.com
Registered User
Posts: 6
Joined: Tue Sep 07, 2004 12:26 am
Location: the Hague, the Netherlands
Contact:

Post by foilzone.com »

Chatasos,

You advice users of the mod to run a script that searches for the most used words.

This is the query you suggest to use:

SELECT swl.word_id, swl.word_text, COUNT(swm.word_id) as total
FROM `phpbb_search_wordlist` swl, `phpbb_search_wordmatch` swm
WHERE swl.word_id = swm.word_id
GROUP BY swm.word_id
ORDER BY total
DESC LIMIT 0,50


Now, the query runs fine, but what i see is that a LOT of search words have double or triple, lets say multiple word_id's.

So, for instance the word review has quite some word_id's. Is this normal?

A little example from the outcome of my query:

word_id word_text total
520 review 51
3090 review 50
3104 review 49
3305 review 48
3412 review 47
3427 review 46
719 leash 45
5717 review 45
1014 leash 43
6585 review 43
230 mastair 43
1647 mastair 42
920 advance 42


You see the word review being reported quite some times. What does this mean? Is it in any way wrong? Now i cannot see the real "heavy" words because this review is coming round all the time.

My db's are waaaaaay too heavy for my hosting account. DB size (not even with a full rebuild of the db's) is about 1,3 Gb which is really too big.

What's your professional advice?

Thanks a lot!

Mark
Foilzone.com
Have fun!
Mark


Happy kiting, whatever you fly!

foilzone.com -------------- foils.nl

All we do is foilkites!
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Post by chatasos »

foilzone.com wrote: Now, the query runs fine, but what i see is that a LOT of search words have double or triple, lets say multiple word_id's.

So, for instance the word review has quite some word_id's. Is this normal?

A little example from the outcome of my query:

word_id word_text total
520 review 51
3090 review 50
3104 review 49
3305 review 48
3412 review 47
3427 review 46
719 leash 45
5717 review 45
1014 leash 43
6585 review 43
230 mastair 43
1647 mastair 42
920 advance 42


You see the word review being reported quite some times. What does this mean? Is it in any way wrong? Now i cannot see the real "heavy" words because this review is coming round all the time.

Normally, each word should have only 1 word_id, since the "phpbb_search_wordlist" table has the "word_text" field as primary key, so every word stored there should be unique.

This is the structure of my forum's "phpbb_search_wordlist" table:

Code: Select all

CREATE TABLE `phpbb_search_wordlist` (
  `word_text` varchar(50) binary NOT NULL default '',
  `word_id` mediumint(8) unsigned NOT NULL auto_increment,
  `word_common` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`word_text`),
  KEY `word_id` (`word_id`)
) 
Try to execute the following query in phpmyadmin and if you get multiple records, something is wrong with your db/forum.

Code: Select all

SELECT *
FROM `phpbb_search_wordlist`
WHERE word_text = "review";
PS: Maybe another mod has changed the table's structure or search functionality?

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
foilzone.com
Registered User
Posts: 6
Joined: Tue Sep 07, 2004 12:26 am
Location: the Hague, the Netherlands
Contact:

Post by foilzone.com »

chatasos wrote:
foilzone.com wrote:

Code: Select all

SELECT *
FROM `phpbb_search_wordlist`
WHERE word_text = "review";
If i run this query, the result is just that it reports how fast the query has been executed.

I don't see "results"...

My forum's phpbb_search_wordlist table (as displayed in PHPmyadmin):

word_id int(4) Nee auto_increment
word_text varchar(50) latin1_swedish_ci Ja NULL
word_common tinyint(1) Ja NULL


The difference seems you got NOT NULL, i have NULL.
Another difference is that my primary key is on word_id, not on word_text.

I run the attach_mod and Mail2forum as mods that influence the db, and some other, non db-influential mods.

My phpbb version is 2.0.11 (still not found time to update).

Mind you, i'm no high-roller when it comes to mysql or php. :oops:
Have fun!
Mark


Happy kiting, whatever you fly!

foilzone.com -------------- foils.nl

All we do is foilkites!
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Post by chatasos »

foilzone.com wrote: My phpbb version is 2.0.11 (still not found time to update).


Time to upgrade then...:wink:
I believe my mod isn't compatible with this version (if i remember right it needs >2.0.14).

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
foilzone.com
Registered User
Posts: 6
Joined: Tue Sep 07, 2004 12:26 am
Location: the Hague, the Netherlands
Contact:

Post by foilzone.com »

chatasos wrote:
foilzone.com wrote: My phpbb version is 2.0.11 (still not found time to update).


Time to upgrade then...:wink:
I believe my mod isn't compatible with this version (if i remember right it needs >2.0.14).


Shit... :-(

You know if this change has been done in the versions later then 2.0.11? Is the searchtables functionality this much changed?

Okay, will update.. :roll:
Have fun!
Mark


Happy kiting, whatever you fly!

foilzone.com -------------- foils.nl

All we do is foilkites!
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Post by chatasos »

foilzone.com wrote: *beep*... :-(

You know if this change has been done in the versions later then 2.0.11? Is the searchtables functionality this much changed?

Okay, will update.. :roll:

Sorry, but i don't know that. But surely you have to upgrade, mainly for security reasons!

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
foilzone.com
Registered User
Posts: 6
Joined: Tue Sep 07, 2004 12:26 am
Location: the Hague, the Netherlands
Contact:

Post by foilzone.com »

Chatasos,

Dropped the two searchtables completely and recreated them according to the right file layout. Apparently (like you see in my previous post) the layout of the file was that word_text was nonunique, hence a humongous file.

Just rebuilt the forum's searchtables yesterday night after dropping and recreating the new tables, and now my searchtables are about 40 mb in total.

Works like a charm now Chatasos!!!

Lots and lots of compliments for a great great mod!!!!
Have fun!
Mark


Happy kiting, whatever you fly!

foilzone.com -------------- foils.nl

All we do is foilkites!
Venturous
Registered User
Posts: 22
Joined: Mon Sep 05, 2005 5:54 am
Location: WV

Post by Venturous »

I have added two other search related mods. One allows users to display the last 12h, 24h, 36, 72h of posts and the other allows users to search topic subjects only.

Will this rebuild my search tables with these other search mods installed?

Thanks.
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Post by chatasos »

Venturous wrote: I have added two other search related mods. One allows users to display the last 12h, 24h, 36, 72h of posts and the other allows users to search topic subjects only.

Will this rebuild my search tables with these other search mods installed?

Thanks.

I guess there wont be a problem :wink:

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
TheSeeKerUK
Registered User
Posts: 3
Joined: Thu Nov 17, 2005 3:58 pm
Location: Berkshire, UK

Blank Page

Post by TheSeeKerUK »

Hi,

This is my first post here so please be gentle :wink:

I recently upgrade my phpBB to 2.0.18. I suffered a few problems, during which I discovered my backups since JULY were incomplete :evil:

As a result I had to manually put back about 70 posts (luckly the data was available elsewhere), but did this with SQL rather than via the board.

This meant that I needed to reindex and repopulate my search tables, so I turned to this MOD. I've installed it OK and checked the permissions, but when I click on "rebuild search" I get a blank page.

I have found one other post of a problem like this by Captain Kirk but he (?) was using phpNUKE, and I'm not...

but I am doing this on VMS rather than Windoze or UNIX, but that shouldn't make a difference.

Any ideas ?
TIA
chatasos
Registered User
Posts: 748
Joined: Wed May 15, 2002 1:16 pm
Location: Paralia

Re: Blank Page

Post by chatasos »

TheSeeKerUK wrote: Hi,

This is my first post here so please be gentle :wink:

I recently upgrade my phpBB to 2.0.18. I suffered a few problems, during which I discovered my backups since JULY were incomplete :evil:

As a result I had to manually put back about 70 posts (luckly the data was available elsewhere), but did this with SQL rather than via the board.

This meant that I needed to reindex and repopulate my search tables, so I turned to this MOD. I've installed it OK and checked the permissions, but when I click on "rebuild search" I get a blank page.

I have found one other post of a problem like this by Captain Kirk but he (?) was using phpNUKE, and I'm not...

but I am doing this on VMS rather than Windoze or UNIX, but that shouldn't make a difference.

Any ideas ?
TIA


HI TheSeeKerUK,

Please make sure you have followed exactly the instructions as they are written. Also all the template changes that refer to subSilver must be done on all your installed templates too.

Report Posts 1.2.3c (MODDB) - Report Posts 2.1.5 (ALPHA)
Rebuild Search 2.4.0 (MODDB)
MOD Version Checker 1.2.0 (MODDB)
Mega Mail System 0.9.8 (ALPHA)
Pagination Select List & Input Box (MODDB)
Post Reply

Return to “[2.0.x] MOD Database Releases”