Page 1 of 1

SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 8:22 am
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

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 9:06 am
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.

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 10:01 am
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

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 12:33 pm
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.

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 3:17 pm
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()

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 7:21 pm
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.

Re: SQL error on Full search MySQL

Posted: Mon Mar 30, 2015 8:31 pm
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 :)

Re: SQL error on Full search MySQL

Posted: Tue May 19, 2015 8:09 am
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:

Re: SQL error on Full search MySQL

Posted: Tue May 19, 2015 8:11 am
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:

Re: SQL error on Full search MySQL

Posted: Tue May 19, 2015 9:29 am
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.

Re: SQL error on Full search MySQL

Posted: Mon Mar 20, 2017 12:02 pm
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.