Fulltext mysql create search index

[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.

[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.

[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]

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]

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

Should I file a bug?

[JimA]

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.

[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...

[DavidIQ]

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.