Search Index not working in phpbb 3.7

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Search Index not working in phpbb 3.7

Post by mubasshar »

hi i am new to phpbb can any body tell me what is the benefit of Search index and when i am trying to create index i am getting this error and than process keeps on running for infinite time.


General Error
SQL ERROR [ mysql4 ]

Column 'post_text' cannot be part of FULLTEXT index [1283]

SQL

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

BACKTRACE


FILE: includes/db/mysql.php
LINE: 174
CALL: dbal->sql_error()

FILE: includes/search/fulltext_mysql.php
LINE: 779
CALL: dbal_mysql->sql_query()

FILE: includes/acp/acp_search.php
LINE: 365
CALL: fulltext_mysql->create_index()

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

FILE: includes/functions_module.php
LINE: 507
CALL: acp_search->main()

FILE: adm/index.php
LINE: 74
CALL: p_master->load_active()


Mubasshar
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

What is your table type? Fulltext indexes are only supported on myisam tables.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

yes all tables have myisam type..
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

Check collations on post_subject and post_text. Collations need to be the same to use these columns together in a fulltext index.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

in which table i need to check these columns..
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

any body have solution for this................... guide me
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

Please run this query in phpmyadmin and post the result here:

Code: Select all

show create table phpbb_posts
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

thx 4 your reply

the result is

CREATE TABLE `phpbb_posts` (
`post_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`forum_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`poster_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`icon_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`poster_ip` varchar(40) NOT NULL,
`post_time` int(11) unsigned NOT NULL DEFAULT '0',
`post_approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
`post_reported` tinyint(1) unsigned NOT NULL DEFAULT '0',
`enable_bbcode` tinyint(1) unsigned NOT NULL DEFAULT '1',
`enable_smilies` tinyint(1) unsigned NOT NULL DEFAULT '1',
`enable_magic_url` tinyint(1) unsigned NOT NULL DEFAULT '1',
`enable_sig` tinyint(1) unsigned NOT NULL DEFAULT '1',
`post_username` varchar(255) NOT NULL,
`post_subject` varchar(255) NOT NULL,
`post_text` mediumtext NOT NULL,
`post_checksum` varchar(32) NOT NULL,
`post_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
`bbcode_bitfield` varchar(255) NOT NULL,
`bbcode_uid` varchar(8) NOT NULL,
`post_postcount` tinyint(1) unsigned NOT NULL DEFAULT '1',
`post_edit_time` int(11) unsigned NOT NULL DEFAULT '0',
`post_edit_reason` varchar(255) NOT NULL DEFAULT '',
`post_edit_user` mediumint(8) unsigned NOT NULL DEFAULT '0',
`post_edit_count` smallint(4) unsigned NOT NULL DEFAULT '0',
`post_edit_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`post_id`),
KEY `forum_id` (`forum_id`),
KEY `topic_id` (`topic_id`),
KEY `poster_ip` (`poster_ip`),
KEY `poster_id` (`poster_id`),
KEY `post_approved` (`post_approved`),
KEY `tid_post_time` (`topic_id`,`post_time`),
KEY `post_username` (`post_username`)
) ENGINE=MyISAM AUTO_INCREMENT=8313 DEFAULT CHARSET=utf8
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

Ah, collation is not displayed in create table. Please do this instead:

Code: Select all

show full columns from phpbb_posts
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

Field Type Collation Null Key Default Extra Privileges Comment
post_id mediumint(8) unsigned NULL NO PRI NULL auto_increment select,insert,update,references
topic_id mediumint(8) unsigned NULL NO MUL 0 select,insert,update,references
forum_id mediumint(8) unsigned NULL NO MUL 0 select,insert,update,references
poster_id mediumint(8) unsigned NULL NO MUL 0 select,insert,update,references
icon_id mediumint(8) unsigned NULL NO 0 select,insert,update,references
poster_ip varchar(40) utf8_general_ci NO MUL NULL select,insert,update,references
post_time int(11) unsigned NULL NO 0 select,insert,update,references
post_approved tinyint(1) unsigned NULL NO MUL 1 select,insert,update,references
post_reported tinyint(1) unsigned NULL NO 0 select,insert,update,references
enable_bbcode tinyint(1) unsigned NULL NO 1 select,insert,update,references
enable_smilies tinyint(1) unsigned NULL NO 1 select,insert,update,references
enable_magic_url tinyint(1) unsigned NULL NO 1 select,insert,update,references
enable_sig tinyint(1) unsigned NULL NO 1 select,insert,update,references
post_username varchar(255) utf8_general_ci NO MUL NULL select,insert,update,references
post_subject varchar(255) utf8_general_ci NO NULL select,insert,update,references
post_text mediumtext utf8_general_ci NO NULL select,insert,update,references
post_checksum varchar(32) utf8_general_ci NO NULL select,insert,update,references
post_attachment tinyint(1) unsigned NULL NO 0 select,insert,update,references
bbcode_bitfield varchar(255) utf8_general_ci NO NULL select,insert,update,references
bbcode_uid varchar(8) utf8_general_ci NO NULL select,insert,update,references
post_postcount tinyint(1) unsigned NULL NO 1 select,insert,update,references
post_edit_time int(11) unsigned NULL NO 0 select,insert,update,references
post_edit_reason varchar(255) utf8_general_ci NO select,insert,update,references
post_edit_user mediumint(8) unsigned NULL NO 0 select,insert,update,references
post_edit_count smallint(4) unsigned NULL NO 0 select,insert,update,references
post_edit_locked tinyint(1) unsigned NULL NO 0 select,insert,update,references
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

This I think is the problem:
mubasshar wrote:Field Type Collation Null Key Default Extra Privileges Comment

post_subject varchar(255) utf8_general_ci NO NULL select,insert,update,references
post_text mediumtext utf8_general_ci NO NULL select,insert,update,references
ALTER TABLE phpbb_posts ADD FULLTEXT (post_subject), MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT (post_text), ADD FULLTEXT post_content (post_subject, post_text)
If you run this query:

Code: Select all

MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL
you should then be able to create fulltext indexes.

Code reference: http://tps.projects.bsdpower.com/browse ... 10399#L744
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL

i tried to run this command but it gives me run time error but any how i changed the collation to unicode and its working now. thanks for the help.

can u tell me the purpose of the search index also.

I did not use this command
ALTER TABLE phpbb_posts ADD FULLTEXT (post_subject), MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT (post_text), ADD FULLTEXT post_content (post_subject, post_text)

Best Regards

Mubasshar
Oleg
Former Team Member
Posts: 1221
Joined: Sat Jan 30, 2010 4:42 pm
Location: NYC
Contact:

Re: Search Index not working in phpbb 3.7

Post by Oleg »

Without a search index the database must sift through content of all posts ever made whenever a search is performed. A search index is a data structure allowing the database to locate matching posts without examining all posts in the database.
Participate in phpBB development: Get involved | Issue tracker | Report a bug | Development board | [url=irc://chat.freenode.net/phpbb-dev]Development IRC chat[/url]
My stuff: mindlinkgame.com
mubasshar
Registered User
Posts: 62
Joined: Sun Sep 27, 2009 10:50 am

Re: Search Index not working in phpbb 3.7

Post by mubasshar »

thanks
User avatar
heredia21
Registered User
Posts: 942
Joined: Sun Apr 18, 2010 6:14 pm
Contact:

Re: Search Index not working in phpbb 3.7

Post by heredia21 »

I'm getting the same error. So the only query to run should be:

Code: Select all

MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL
And then I should be able to create fulltext table in ACP?
Best BlackBerry website for all users! BlackBerry News - http://blackberryempire.com
Locked

Return to “[3.0.x] Support Forum”