search indexing - "Illegal mix of collations for operation"

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
mates85
Registered User
Posts: 84
Joined: Sat Nov 12, 2005 4:05 am
Location: sydney aus
Contact:

search indexing - "Illegal mix of collations for operation"

Post by mates85 »

Your board's URL: www.downundertruckin.com/phpBB2/
Version of phpBB3: 3.0.2
Was this a fresh install or a(n) update/upgrade/conversion (please be specific)? - upgrade from phpbb2
Did you use an automated wizard provided by your host to install phpBB? at first phpbb3 was an auto isntall then once running "Dicky" modified it and cleaned it up as it was a mess due to the auto install
MODs you have installed: Global announcements , Image resize
When the problem started: tonight during a continuation of a search index , at around 100 000 of 190 000 posts
Your level of expertise (be honest): newbie , can install mods but not to switched on with php.

this is the following error i have recieved whilst creating a search index and it keeps happening from now on i need to build my search index because i believe it is causing max users error because people are searching and hitting the same no results page cause it cant search the board untill the index is created.

please help !!!!

General Error
SQL ERROR [ mysql4 ]

Illegal mix of collations for operation ' IN ' [1271]

SQL

SELECT word_id, word_text FROM phpbb_search_wordlist WHERE word_text IN ('rat', 'ˈraeti', 'pronunciation', 'key', 'show', 'spelled', 'ipa', 'adjective', 'est', 'full', 'rats', 'characteristic', 'wretched', 'shabby', 'ratty', 'old', 'overcoat', 'truck', 'not', 'rod', 'its', 'pre', '1948', 'isnt', 'ghetto', 'opinion', 'ruff', 'dilapidated', 'untidy', 'even', 'defining', 'what', 'hard', 'mebbe', 'easiest', 'say', 'that', 'essentially', 'the', 'mentality', 'about', 'making', 'with', 'whatever', 'around', 'overall', 'finish', 'high', 'priority', 'but', 'driving', 'and', 'enjoying', 'usually', 'personally', 'would', 'get', 'any', 'more', 'enjoyment', 'from', 'had', '5000', 'worth', 'paint', 'another', 'billets', 'fact', 'nervous', 'scratches', 'chips', 'theft', 'tyre', 'prices', 'etc', 'suck', 'joy', 'out', 'cruising', 'altogether', 'own', 'other', 'way', 'judging', 'peoples', 'faces', 'drive', 'doesnt', 'matter', 'wheels', 'are', 'stock', 'cost', 'know', 'everyones', 'taste', 'thats', 'cool', 'dig')

BACKTRACE

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

FILE: includes/search/fulltext_native.php
LINE: 1121
CALL: dbal_mysql->sql_query()

FILE: includes/acp/acp_search.php
LINE: 399
CALL: fulltext_native->index()

FILE: includes/acp/acp_search.php
LINE: 46
CALL: acp_search->index()

FILE: includes/functions_module.php
LINE: 471
CALL: acp_search->main()

FILE: adm/index.php
LINE: 74
CALL: p_master->load_active()
mates85
Registered User
Posts: 84
Joined: Sat Nov 12, 2005 4:05 am
Location: sydney aus
Contact:

Re: search indexing - "Illegal mix of collations for operation"

Post by mates85 »

anyone have any ideas ? i just need to build this search index in hope it fixes the max users error im recieivng if its not one error its another thats all ive had with phpbb3, just want stable running forum like every other board i visit..
mates85
Registered User
Posts: 84
Joined: Sat Nov 12, 2005 4:05 am
Location: sydney aus
Contact:

Re: search indexing - "Illegal mix of collations for operation"

Post by mates85 »

anyone have any idea whats going on here i havent had a search index for months i want my users to be able to searchhhhhhh
kenerly
Registered User
Posts: 79
Joined: Fri May 23, 2003 6:25 am

Re: search indexing - "Illegal mix of collations for operation"

Post by kenerly »

I have same problem any fix?
User avatar
Noxwizard
Support Team Leader
Support Team Leader
Posts: 10423
Joined: Mon Jun 27, 2005 8:41 pm
Location: Texas, USA
Name: Patrick Webster
Contact:

Re: search indexing - "Illegal mix of collations for operation"

Post by Noxwizard »

Make sure that the collation on the "word_text" field of the phpbb_search_wordlist table is set to one of the utf8_* variants.
[Support Template] - [Read Before Posting] - [phpBB Knowledge Base]
Do not contact me for private support, please share the question in our forums.
kenerly
Registered User
Posts: 79
Joined: Fri May 23, 2003 6:25 am

Re: search indexing - "Illegal mix of collations for operation"

Post by kenerly »

I changed phpbb_search_wordlist to utf8_unicode_ci and it fixed it.

Is there a script I could run that would update my entire phpbb3 mysql database (all Collation fields) from latin1_swedish_ci to utf8_unicode_ci
User avatar
Noxwizard
Support Team Leader
Support Team Leader
Posts: 10423
Joined: Mon Jun 27, 2005 8:41 pm
Location: Texas, USA
Name: Patrick Webster
Contact:

Re: search indexing - "Illegal mix of collations for operation"

Post by Noxwizard »

[Support Template] - [Read Before Posting] - [phpBB Knowledge Base]
Do not contact me for private support, please share the question in our forums.
kenerly
Registered User
Posts: 79
Joined: Fri May 23, 2003 6:25 am

Re: search indexing - "Illegal mix of collations for operation"

Post by kenerly »

Thanks nox, worked perfect.
Locked

Return to “[3.0.x] Support Forum”