Problem in MySQL

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)
Locked
Cypek
Registered User
Posts: 14
Joined: Sat May 29, 2010 8:41 pm
Location: Poland

Problem in MySQL

Post by Cypek »

Welcome, my English is not very well so i used to translator. Sorry for errors. I conducted many operations on the database. Convert from mybb, then combining the two bases, at the end of everything hooked into a completely different files forum. I fixed all the bugs, updated the database with the phpbb.com application for this purpose. I removed some minor bugs, and was the only one which can not cope.

When i go to viewtopic i see:

Code: Select all

Błąd ogólny
SQL ERROR [ mysql4 ]

Can't find FULLTEXT index matching the column list [1191]

SQL

SELECT f.forum_id, f.forum_name, t.topic_id, t.topic_title, u.user_id, u.username, u.user_colour, t.topic_replies FROM (phpbb_topics t) LEFT JOIN phpbb_users u ON (u.user_id = t.topic_poster) LEFT JOIN phpbb_forums f ON (f.forum_id = t.forum_id) WHERE MATCH (t.topic_title) AGAINST ('Gruzja' ) >= 0.5 AND t.topic_status <> 2 AND t.topic_id <> 63033 GROUP BY t.topic_id ORDER BY t.topic_last_post_time DESC LIMIT 5

BACKTRACE

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

FILE: includes/db/mysql.php
LINE: 221
CALL: dbal_mysql->sql_query()

FILE: includes/db/dbal.php
LINE: 170
CALL: dbal_mysql->_sql_query_limit()

FILE: viewtopic.php
LINE: 1942
CALL: dbal->sql_query_limit()
The updater received a code to add in the database and when I have the following error:

Code: Select all

#1283 - Column 'post_text' cannot be part of FULLTEXT index
My code from updater:

Code: Select all

ALTER TABLE phpbb_posts 
MODIFY post_id mediumint(8) UNSIGNED NOT NULL auto_increment,
MODIFY topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY poster_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY icon_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY poster_ip varchar(40) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY post_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY post_approved tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY post_reported tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY enable_bbcode tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY enable_smilies tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY enable_magic_url tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY enable_sig tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY post_username varchar(255) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY post_subject varchar(255) DEFAULT '' NOT NULL COLLATE utf8_unicode_ci,
MODIFY post_text mediumtext NOT NULL COLLATE utf8_bin,
MODIFY post_checksum varchar(32) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY post_attachment tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY bbcode_bitfield varchar(255) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY bbcode_uid varchar(8) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY post_postcount tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
MODIFY post_edit_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY post_edit_reason varchar(255) DEFAULT '' NOT NULL COLLATE utf8_bin,
MODIFY post_edit_user mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY post_edit_count smallint(4) UNSIGNED DEFAULT '0' NOT NULL,
MODIFY post_edit_locked tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
In the ACP I wanted to add the search index and an error pops up:

Code: Select all

Błąd ogólny
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()
I'm really need a help.
User avatar
AmigoJack
Registered User
Posts: 5799
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Problem in MySQL

Post by AmigoJack »

All columns used for a FULLTEXT index must have the same collation. Execute this and post back the results - it should show different collations in use:

Code: Select all

SHOW CREATE TABLE phpbb_posts
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
Cypek
Registered User
Posts: 14
Joined: Sat May 29, 2010 8:41 pm
Location: Poland

Re: Problem in MySQL

Post by Cypek »

Code: Select all

phpbb_posts	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) character set utf8 collate utf8_bin 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) character set utf8 collate utf8_bin NOT NULL default '',
 `post_subject` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
 `post_text` mediumtext character set utf8 collate utf8_bin NOT NULL,
 `post_checksum` varchar(32) character set utf8 collate utf8_bin NOT NULL,
 `post_attachment` tinyint(1) unsigned NOT NULL default '0',
 `bbcode_bitfield` varchar(255) character set utf8 collate utf8_bin NOT NULL,
 `bbcode_uid` varchar(8) character set utf8 collate utf8_bin 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) character set utf8 collate utf8_bin 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 `post_username` (`post_username`),
 KEY `tid_post_time` (`topic_id`,`post_time`)
) ENGINE=MyISAM AUTO_INCREMENT=141151 DEFAULT CHARSET=utf8
User avatar
AmigoJack
Registered User
Posts: 5799
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Problem in MySQL

Post by AmigoJack »

Code: Select all

`post_subject` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
`post_text` mediumtext character set utf8 collate utf8_bin NOT NULL,
See that? Collations differ.

Execute this:

Code: Select all

ALTER TABLE phpbb_posts ADD FULLTEXT (post_subject), MODIFY post_text mediumtext COLLATE utf8_bin NOT NULL, ADD FULLTEXT (post_text), ADD FULLTEXT post_content (post_subject, post_text)
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
Cypek
Registered User
Posts: 14
Joined: Sat May 29, 2010 8:41 pm
Location: Poland

Re: Problem in MySQL

Post by Cypek »

When I did it in phpMyAdmin i had error:

Code: Select all

#1283 - Column 'post_text' cannot be part of FULLTEXT index
Cypek
Registered User
Posts: 14
Joined: Sat May 29, 2010 8:41 pm
Location: Poland

Re: Problem in MySQL

Post by Cypek »

Now I have this error when I go to viewtopi:

Code: Select all

SQL ERROR [ mysql4 ]

Can't find FULLTEXT index matching the column list [1191]

SQL

SELECT f.forum_id, f.forum_name, t.topic_id, t.topic_title, u.user_id, u.username, u.user_colour, t.topic_replies FROM (phpbb_topics t) LEFT JOIN phpbb_users u ON (u.user_id = t.topic_poster) LEFT JOIN phpbb_forums f ON (f.forum_id = t.forum_id) WHERE MATCH (t.topic_title) AGAINST ('Telefony sÄ… TwojÄ… pasjÄ…? - Biuletyn fonik.eu' ) >= 0.5 AND t.topic_status <> 2 AND t.topic_id <> 30580 GROUP BY t.topic_id ORDER BY t.topic_last_post_time DESC LIMIT 5

BACKTRACE

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

FILE: includes/db/mysql.php
LINE: 221
CALL: dbal_mysql->sql_query()

FILE: includes/db/dbal.php
LINE: 170
CALL: dbal_mysql->_sql_query_limit()

FILE: viewtopic.php
LINE: 1942
CALL: dbal->sql_query_limit()
Error changed.
User avatar
AmigoJack
Registered User
Posts: 5799
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Problem in MySQL

Post by AmigoJack »

My mistake - post_text was already on utf8_bin. Do this:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject mediumtext COLLATE utf8_bin NOT NULL, ADD FULLTEXT (post_subject), ADD FULLTEXT (post_text), ADD FULLTEXT post_content (post_subject, post_text)
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
Cypek
Registered User
Posts: 14
Joined: Sat May 29, 2010 8:41 pm
Location: Poland

Re: Problem in MySQL

Post by Cypek »

Again:

Code: Select all

#1283 - Column 'post_text' cannot be part of FULLTEXT index
User avatar
AmigoJack
Registered User
Posts: 5799
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Problem in MySQL

Post by AmigoJack »

These are 5 separate commands, execute them, once and for all. Make sure to say which of them failed, if any:

Code: Select all

ALTER TABLE phpbb_posts MODIFY post_subject varchar(255) NOT NULL;

ALTER TABLE phpbb_posts MODIFY post_text mediumtext NOT NULL;

ALTER TABLE phpbb_posts ADD FULLTEXT (post_subject);

ALTER TABLE phpbb_posts ADD FULLTEXT (post_text);

ALTER TABLE phpbb_posts ADD FULLTEXT post_content (post_subject, post_text);
  • The worst thing about censorship is ███████████
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
Gardus
Registered User
Posts: 10
Joined: Sat Aug 31, 2013 5:56 pm

Re: Problem in MySQL

Post by Gardus »

Kudos to AmigoJack...I had the same indexing issue and I solved thanks to your imputs!
Locked

Return to “[3.0.x] Support Forum”