Search Index issues

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Search Index issues

Post by Zherog »

I've opted to post this as a new issue, instead of as a reply to this thread. While I think they're related, I also think they're independent enough to be a separate topic. If a mod/admin disagrees, of course, please go ahead and merge the topics.

So, if I navigate to ACP -> Maintenance -> Search Index, I get this:
Search index
An indexing process has been started. In order to access the search index page you will have to complete it or cancel it.
And a screenshot, for the visual:
den1.JPG
The thing is, based on the errors documented in the thread linked at the top, I don't think the search index is actually running. For example, it's been over 2 hours now and this query returns 0 rows.

Code: Select all

select *
from phpbb_3_search_wordlist
So... in this state, how do I either cancel whatever indexing process is running or if it's not actually running (like I expect) what do I do so the ACP screen knows it's dead and will allow me to try again...

As always, Thanks!
User avatar
ViolaF
I've Been Banned!
Posts: 1609
Joined: Tue Aug 14, 2012 11:52 pm

Re: Search Index issues

Post by ViolaF »

may try another search-index-mode...
User avatar
ViolaF
I've Been Banned!
Posts: 1609
Joined: Tue Aug 14, 2012 11:52 pm

Re: Search Index issues

Post by ViolaF »

Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Thanks. At the moment, I don't seem to have an option to switch even if I wanted to, as the page for building indexes is the one that gives the message in my initial post.
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Alright... In the screenshot in my original post, you can see Submit and Reset buttons. For giggles and grins, I clicked the submit button. It spun for a while, and then I got this error message:
General Error
SQL ERROR [ mysqli ]

MySQL server has gone away [2006]

SQL

ALTER TABLE phpbb_3_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 997
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 196
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 329
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/search/fulltext_mysql.php
LINE: 1067
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/includes/acp/acp_search.php
LINE: 369
CALL: phpbb\search\fulltext_mysql->create_index()

FILE: [ROOT]/includes/acp/acp_search.php
LINE: 46
CALL: acp_search->index()

FILE: [ROOT]/includes/functions_module.php
LINE: 676
CALL: acp_search->main()

FILE: [ROOT]/adm/index.php
LINE: 82
CALL: p_master->load_active()
Does this help anybody with helping me sort this out? At this point, honestly, I'm quite happy to switch from MySQL Search to PHP Native Fulltext if somebody can help me "kill" whatever PHPBB uses to think a MySQL Search index is running, if that's easier to solve.
robtig
Registered User
Posts: 37
Joined: Thu May 21, 2015 3:45 pm

Re: Search Index issues

Post by robtig »

this might help some. I use php native.

viewtopic.php?t=762865
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Thanks; I've seen that (it was linked above).

Right now, I can't switch even if I wanted to. If I go to ACP -> Maintenance -> Database -> Search Index I don't have any options to rebuild indexes because it says, "An indexing process has been started. In order to access the search index page you will have to complete it or cancel it." (see screenshot in first post.)

It looks like in addition to causing the "General Error" posted in my last post, pressing the submit button has borked something in the database - most likely the phpbb_3_posts table, since I can do anything (it looks like) except view a thread. And attempting to repair the table / database so far has failed. I have a ticket open with Bluehost for that; my hunch is the ALTER TABLE command shown in the error message took too long and lead to a timeout, and that's what is borking the phpbb_3_posts table.

As I said, I'm quite willing to switch to a different search method. I just need to know how to make the ACP allow me to do so.

edit: Fixed typo...
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 52768
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Search Index issues

Post by stevemaury »

There should be another browser tab in which the indexing is running and from which you can cancel it.

If not, do a structure only backup of the three search* tables, and then delete (DROP)v those tables and then use the structure backup to recreate them. That ought to stop it.
I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Thanks for the suggestions, Steve. There's not another browser tab open. There was when I first started trying. I've long since closed it.

And dropping and recreating the tables didn't seem to help, either. I still have this when I go to ACP -> Maintenance -> Database -> Search Index:
Search index
An indexing process has been started. In order to access the search index page you will have to complete it or cancel it.
I have this even after changing ACP -> General -> Database -> Search Settings from MySQL to Native. So something else is telling PHPBB that a search index process is running other than the contents of those three tables (which were empty, by the way).

Given the error messages in my last post, and that the tables were empty, I suspect I'm timing out on the ALTER TABLE command. It's my biggest table, of course, with 543,655 rows. Big, but not ridiculous.

Any other ideas from anybody on how to get PHPBB to give me back the search index options so I can try a different one? I considered doing the ALTER TABLE command manually, either in PHPMyAdmin or via the command prompt, but I don't think that'll help as I'm sure when I press the s
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

I'm going to go ahead and give this a bump, just this once, in the hopes of finding an answer. Without some ideas, we'll be stuck without having search - which means relying on Google to crawl through everything eventually.

Thanks in advance for any assistance!
robtig
Registered User
Posts: 37
Joined: Thu May 21, 2015 3:45 pm

Re: Search Index issues

Post by robtig »

do you have a DB backup from before you did the recent indexing?
robtig
Registered User
Posts: 37
Joined: Thu May 21, 2015 3:45 pm

Re: Search Index issues

Post by robtig »

apparently the utf encoding can get messed up. Wont hurt to check. Then do a manual rebuild.
Back up your database
from acp/maintenance/search_index delete all indexes

then go to php myadmin

Code: Select all

 ALTER TABLE phpbb3_acl_groups CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_acl_options CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_acl_roles CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_acl_roles_data CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_acl_users CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_attachments CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_banlist CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_bbcodes CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_bookmarks CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_bots CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_config CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_confirm CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_disallow CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_drafts CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_extensions CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_extension_groups CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_forums CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_forums_access CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_forums_track CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_forums_watch CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_groups CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_icons CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_lang CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_log CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_moderator_cache CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_modules CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_poll_options CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_poll_votes CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_posts CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_privmsgs CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_privmsgs_folder CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_privmsgs_rules CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_privmsgs_to CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_profile_fields CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_profile_fields_data CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_profile_fields_lang CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_profile_lang CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_ranks CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_reports CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_reports_reasons CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_search_results CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_search_wordlist CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_search_wordmatch CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_sessions CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_sessions_keys CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_sitelist CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_smilies CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles_imageset CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles_imageset_data CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles_template CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles_template_data CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_styles_theme CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_topics CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_topics_posted CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_topics_track CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_topics_watch CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_users CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_user_group CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_warnings CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_words CONVERT TO CHARACTER SET utf8;
 ALTER TABLE phpbb3_zebra CONVERT TO CHARACTER SET utf8;
then this will do a fulltext index manually on the post_text column of the phpbb_posts table through phpMyAdmin.

Code: Select all

 ALTER TABLE `phpbb3_posts` ADD FULLTEXT (
`post_text`
) 
after this go back to ACP and try rebuilding the indexes

hopefully this helps
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Thank you! This looks promising. One follow-up question, even though I think I know the answer.

When I go to ACP -> Maintenance -> Search Index, I don't have the option to delete anything. As I've said, it seems to be "stuck" thinking that whatever I started previously is still running. This is what my screen looks like:
den2.JPG
My assumption would be to proceed with your steps while skipping this, since I can't perform that task at the moment?

And, actually, I'll ask a second question here too, that's barely tangentially related. I found a link on Bluehost's Knowledge Base that was tangentially related to my issue. Basically, I stumbled across this because when I originally submitted the search index job, and when I pressed the "Submit" button in the screenshot above one time, the job eventually dies while trying to do the ALTER TABLE command on phpbb3_posts (see a previous post for the whole error). When that happens, anything related to the posts table - like view a thread - goes to heck in a handbasket. "Repair Database" seemed to be the place to go for that, but it was failing from Bluehost's console. Sadly, it also failed when I tried from within phpMyAdmin. Eventually - I'm guessing about 24 hours or so - it'll clear up and let me perform Repair Database either way.

In that link, it discusses issues with using the InnoDB engine - and that is what all the phpbb3 tables are using. Is it worthwhile to switch the tables from InnoDB to MyISAM, which is what my phpbb v2 tables use? I've done some poking around, and it looks like the biggest differences are that InnoDB supports row-level locking while MyISAM uses table locking; and InnoDB supports Transactions, and MyISAM does not. That last one seems to be the bigger deal to me, as what I've read says that means InnoDB supports Commit and Rollback, and MyISAM does not. It may not matter, though, if there are no "Rollback" commands ever issued via the phpbb3 code.

Since I'm doing ALTER TABLE commands on all the tables, is it worthwhile to also switch to MyISAM? Or will I lose too much valuable functionality with the loss of Commit / Rollback?

That... turned out longer than I wanted. Ugh. Thanks!
robtig
Registered User
Posts: 37
Joined: Thu May 21, 2015 3:45 pm

Re: Search Index issues

Post by robtig »

Yes if there is nothing to delete in ACP then skip that step.
Give it a shot and let me know how it goes. Make sure to fully back up first!! Also when doing database modifications it is best to have the forum inactive as you do not want DB accessing going on while trying to make changes. This can causa all kinds of unexpected issues.

I really think you should stick with InnoDB. I do not see any potential gains from switching and the potential loss of commit/rollbacks could be a problem in the future.
Let me know how it goes. Best of luck!
Zherog
Registered User
Posts: 33
Joined: Thu Jan 03, 2008 3:47 am

Re: Search Index issues

Post by Zherog »

Great, thank you! Our forum is already in disabled mode, and I plan to leave it there for now.

I'll take care of this tonight after work and soccer practice, so I can give my full attention to it. I'll update afterward.
Post Reply

Return to “[3.2.x] Support Forum”