Fulltext mysql create search index

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
Ron2K
Registered User
Posts: 247
Joined: Sun May 25, 2003 6:10 pm
Location: Cape Town, South Africa
Name: Kieron Thwaites
Contact:

Fulltext mysql create search index

Post by Ron2K »

Apologies for the thread resurrection, but I'm having the exact same issue, and there doesn't appear to be any resolution to this; hence, I'm posting my information here.

Support Request Template
What version of phpBB are you using? phpBB 3.0.12
What is your board's URL? https://www.ron2k.za.net/forum/
Who do you host your board with? Self-hosted, full root access. Server running the MySQL 5.6 package provided in the official Ubuntu 14.04 LTS repositories.
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Update from a previous version of phpBB3
Is registration required to reproduce this issue? Yes
Do you have any MODs installed? No
What version of phpBB3 did you update from? phpBB 3.0.11
What styles do you currently have installed? Artodia: Deluxe Red
What language(s) is your board currently using? en-GB
Which database type/version are you using? MySQL 5.6.17-0ubuntu0.14.04.1
What is your level of experience? Comfortable with PHP and phpBB
What username can be used to view this issue? No answer given
What password can be used to view this issue? No answer given
When did your problem begin? Dropped mysql fulltext index, updated tables from MyISAM to InnoDB, attempted to recreate mysql fulltext index.
Please describe your problem. SQL ERROR [ mysql4 ]

InnoDB presently supports one FULLTEXT index creation at a time [1795]

SQL

ALTER TABLE phpbb3_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, 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: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/includes/db/dbal.php
LINE: 757
CALL: trigger_error()

FILE: [ROOT]/includes/db/mysql.php
LINE: 193
CALL: dbal->sql_error()

FILE: [ROOT]/includes/search/fulltext_mysql.php
LINE: 786
CALL: dbal_mysql->sql_query()

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

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

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

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

Issue persists after both mysqld and apache restarts.
Generated by SRT Generator

I've switched to fulltext native for the time being, but of course this isn't ideal.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26455
Joined: Fri Aug 29, 2008 9:49 am

Re: Fulltext mysql create search index

Post by Mick »

You should start your own topic, your situation isn't exactly the same as in the original topic. I've split this for you.
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
digitaltoast
Registered User
Posts: 105
Joined: Thu Oct 18, 2007 9:33 am

Re: Fulltext mysql create search index

Post by digitaltoast »

Using 3.1 RC5 and php 5.6/mysql 5.6 here, struggled with this most of the evening. Eventually read the error message a little closer, and ended up doing this directly in phpmyadmin:

Code: Select all

ALTER TABLE phpbb3_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject);
and then

Code: Select all

ALTER TABLE phpbb3_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject);
Eh voila! Native fulltext innodb search and 500Mb saved!
dero88
Registered User
Posts: 1
Joined: Sat Oct 31, 2015 6:07 pm

Re: Fulltext mysql create search index

Post by dero88 »

I'm using phpbb 3.1.6, php 5.5.9 / MySQL 5.6.27 and got the same issue than above. The solution mentioned by digitaltoast works. Phpbb tries to run 2 sql queries in one, both creating a fulltext index, what innodb engines don't support. Splitting this command in 2 parts corrects it.
digitaltoast
Registered User
Posts: 105
Joined: Thu Oct 18, 2007 9:33 am

Re: Fulltext mysql create search index

Post by digitaltoast »

I just had to do the same thing again, this time to create an index in 3.2

Should I file a bug?
User avatar
JimA
Former Team Member
Posts: 7833
Joined: Thu Jul 31, 2008 5:54 am
Location: The Netherlands
Name: Jim Mossing Holsteyn
Contact:

Re: Fulltext mysql create search index

Post by JimA »

digitaltoast wrote:I just had to do the same thing again, this time to create an index in 3.2

Should I file a bug?
Yes, please. :)
Jim Mossing Holsteyn - Former Community Team Leader
Knowledge Base | Documentation | Board rules

If you're having any questions about the rules/customs of this website, feel free to drop me a PM.
digitaltoast
Registered User
Posts: 105
Joined: Thu Oct 18, 2007 9:33 am

Re: Fulltext mysql create search index

Post by digitaltoast »

OK, done. https://tracker.phpbb.com/browse/PHPBB3-14941

Not holding out much hope as other bugs I've filed haven't even been looked at...
User avatar
DavidIQ
Customisations Team Leader
Customisations Team Leader
Posts: 18282
Joined: Thu Jan 06, 2005 1:30 pm
Location: Fishkill, NY
Name: David Colón
Contact:

Re: Fulltext mysql create search index

Post by DavidIQ »

digitaltoast wrote:OK, done. https://tracker.phpbb.com/browse/PHPBB3-14941

Not holding out much hope as other bugs I've filed haven't even been looked at...
We look at all tickets, even if we don't respond (how I got here). In reviewing the 2 other tickets you've filed: The first one you filed was actually responded to and is a bit more far-reaching than just the one area reported so will take time to properly address. The second one just doesn't have the best of solutions since most of the emoji support is provided by an images library so adding a font-size to an image basically does nothing. However the person that added the functionality has been notified and will respond soon enough.
Apply to become a Jr. Extension Validator
My extensions | In need of phpBB services? | Was I helpful today?
No unsolicited PMs unless you're planning on asking for paid help.
Locked

Return to “[3.0.x] Support Forum”