SQL error on Full search MySQL

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Suggested Hosts
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
Vazze
Registered User
Posts: 10
Joined: Mon Mar 12, 2012 8:47 pm

SQL error on Full search MySQL

Post by Vazze »

Support Request Template
What version of phpBB are you using? phpBB 3.1.3
What is your board's URL? http://forum.11433.net
Who do you host your board with? Hetzner
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? Prosilver
What language(s) is your board currently using? ENG / SV
Which database type/version are you using? MySQL 5.6 (INNODB)
What is your level of experience? Comfortable with PHP and phpBB
What username can be used to view this issue? must be admin
What password can be used to view this issue? must be admin
When did your problem begin? After upgrade
Please describe your problem. I get an error trying to get the full text search with MySQL again.

SQL ERROR [ mysqli ]

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

SQL

ALTER TABLE phpbb_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_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: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 194
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: 800
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/includes/acp/acp_search.php
LINE: 360
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: 674
CALL: acp_search->main()

FILE: [ROOT]/adm/index.php
LINE: 81
CALL: p_master->load_active()
Generated by SRT Generator
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: SQL error on Full search MySQL

Post by Oyabun1 »

I think that is the result of a bug in MySQL. For certain operations it drops all the indexes and then recreates them with a single statement and that triggers the error. That was supposed to be fixed in MySQL 5.6.8.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
Vazze
Registered User
Posts: 10
Joined: Mon Mar 12, 2012 8:47 pm

Re: SQL error on Full search MySQL

Post by Vazze »

Oyabun1 wrote:I think that is the result of a bug in MySQL. For certain operations it drops all the indexes and then recreates them with a single statement and that triggers the error. That was supposed to be fixed in MySQL 5.6.8.
I did not have this problem on my other host (with phpbb 3.0.11) and mysql Ver 14.14 Distrib 5.6.17

Now im on 3.1.3 with mysql Ver 14.14 Distrib 5.6.23
Khaos-Rage
Registered User
Posts: 71
Joined: Sun Jul 13, 2008 4:31 am

Re: SQL error on Full search MySQL

Post by Khaos-Rage »

Vazze wrote: SQL ERROR [ mysqli ]

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

SQL

ALTER TABLE phpbb_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_content (post_text, post_subject)
Had this issue as well running MariaDB 10.0.17, what you have to do is run the Modify query's separately from one another using phpMyAdmin, HeidiSQL, etc.

So run these two query's:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject)

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)
Then you can go into the admin panel and create the index.
Vazze
Registered User
Posts: 10
Joined: Mon Mar 12, 2012 8:47 pm

Re: SQL error on Full search MySQL

Post by Vazze »

Khaos-Rage wrote:
Vazze wrote: SQL ERROR [ mysqli ]

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

SQL

ALTER TABLE phpbb_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_content (post_text, post_subject)
Had this issue as well running MariaDB 10.0.17, what you have to do is run the Modify query's separately from one another using phpMyAdmin, HeidiSQL, etc.

So run these two query's:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject)

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)
Then you can go into the admin panel and create the index.
I've altered the tables as you suggested, i then try to run create the MySQL-index and still get.

Code: Select all

SQL ERROR [ mysqli ]

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

SQL

ALTER TABLE phpbb_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_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: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 194
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: 800
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/includes/acp/acp_search.php
LINE: 360
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: 674
CALL: acp_search->main()

FILE: [ROOT]/adm/index.php
LINE: 81
CALL: p_master->load_active()
Khaos-Rage
Registered User
Posts: 71
Joined: Sun Jul 13, 2008 4:31 am

Re: SQL error on Full search MySQL

Post by Khaos-Rage »

Vazze wrote:I've altered the tables as you suggested, i then try to run create the MySQL-index and still get.
So I just deleted the current MySQL Index from ACP and tried to use the create index button and it did the same thing (error).

These are the exact steps I used to re-create the Index. Delete any Indexes that you currently have.

1) Go to Search Index and click "Create Index" let it error out.
2) Click your browsers back button and leave it on the "Search Index" page.
3) Open what ever you use to run the querys on the DB in a new tab or window.
4) Run this query:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject)
5)Run this query separately and clear out the window that was used to query the server:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)
6) Go back to the "Search Index" page in the admin panel click "Create Index" and it will give you the pop-up window and will immediately say

Code: Select all

Information

Successfully indexed all posts in the board database.

« Back to previous page

And that's how it worked for me.

You may, instead of doing this just create a phpBB Native Index instead.
Vazze
Registered User
Posts: 10
Joined: Mon Mar 12, 2012 8:47 pm

Re: SQL error on Full search MySQL

Post by Vazze »

Khaos-Rage wrote:
Vazze wrote:I've altered the tables as you suggested, i then try to run create the MySQL-index and still get.
So I just deleted the current MySQL Index from ACP and tried to use the create index button and it did the same thing (error).

These are the exact steps I used to re-create the Index. Delete any Indexes that you currently have.

1) Go to Search Index and click "Create Index" let it error out.
2) Click your browsers back button and leave it on the "Search Index" page.
3) Open what ever you use to run the querys on the DB in a new tab or window.
4) Run this query:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject)
5)Run this query separately and clear out the window that was used to query the server:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)
6) Go back to the "Search Index" page in the admin panel click "Create Index" and it will give you the pop-up window and will immediately say

Code: Select all

Information

Successfully indexed all posts in the board database.

« Back to previous page

And that's how it worked for me.

You may, instead of doing this just create a phpBB Native Index instead.
Thanks, but i gave up on this.
Running with the Sphinx-search instead, works great and takes no time to set-up (a little bit of hassle was it though with the script that updates the search db..) But now its rolling like never before, superfast searches :)
marvin-miller
Registered User
Posts: 56
Joined: Wed Mar 12, 2014 1:52 am

Re: SQL error on Full search MySQL

Post by marvin-miller »

Khaos-Rage wrote: These are the exact steps I used to re-create the Index. Delete any Indexes that you currently have.

1) Go to Search Index and click "Create Index" let it error out.
2) Click your browsers back button and leave it on the "Search Index" page.
3) Open what ever you use to run the querys on the DB in a new tab or window.
4) Run this query:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT '' NOT NULL, ADD FULLTEXT (post_subject)
5)Run this query separately and clear out the window that was used to query the server:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL, ADD FULLTEXT post_content (post_text, post_subject)
6) Go back to the "Search Index" page in the admin panel click "Create Index" and it will give you the pop-up window and will immediately say

Code: Select all

Information

Successfully indexed all posts in the board database.

« Back to previous page

And that's how it worked for me.

You may, instead of doing this just create a phpBB Native Index instead.
Thank you !! That worked perfectly :D Thanks for taking the time to post that solution up :mrgreen:
marvin-miller
Registered User
Posts: 56
Joined: Wed Mar 12, 2014 1:52 am

Re: SQL error on Full search MySQL

Post by marvin-miller »

Just a question....what causes this? It seems I need to run those two statements each time I drop/re-create the index :roll:
User avatar
Mick
Support Team Member
Support Team Member
Posts: 22834
Joined: Fri Aug 29, 2008 9:49 am
Location: Watching cricket probably.

Re: SQL error on Full search MySQL

Post by Mick »

Oyabun1 wrote:I think that is the result of a bug in MySQL. For certain operations it drops all the indexes and then recreates them with a single statement and that triggers the error. That was supposed to be fixed in MySQL 5.6.8.
"The more connected we get the more alone we become" - Kyle Broflovski©
BioLogIn
Registered User
Posts: 172
Joined: Sun Jan 06, 2008 10:51 am

Re: SQL error on Full search MySQL

Post by BioLogIn »

Alternative workaround for this should be something like:

Code: Select all

//workaround for https://bugs.mysql.com/bug.php?id=67004 
 		if (sizeof($alter))
		{
			$this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter));
		}
		$alter = array();
in phpbb/search/fulltext_mysql.php around line 560.
Locked

Return to “[3.1.x] Support Forum”