Problem in MySQL

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

[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

[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

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

[Cypek]

When I did it in phpMyAdmin i had error:

Code: Select all

#1283 - Column 'post_text' cannot be part of FULLTEXT index

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

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

[Cypek]

Again:

Code: Select all

#1283 - Column 'post_text' cannot be part of FULLTEXT index

[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);

[Gardus]

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