MyBB Community Forums

We recently upgraded the forum on this site from version 1.4.9 to 1.8.6.  Everything seems to be fine so far, except now we get the SQL error quoted below whenever we attempt to post a message that contains double spaces (i.e. 2 spaces in a row that occur when pressing the space bar twice in a row, or inserting a smilie before or after a space).

Quote:MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1267 - Illegal mix of collations (cp1257_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

Query:
SELECT p.pid FROM abc_posts p WHERE p.uid='3' AND p.fid='24' AND p.subject='Test Thread Subject' AND p.message='Test message.  Double spaced before this sentence.' AND p.dateline>1445619231

Please contact the MyBB Group for technical support.

(table prefix edited for security)

The forum is currently set to the default theme, and no plugins are activated.

How do we resolve this?  Thanks.

You need to make sure the tables have the same collation. Navigate to your website's PHPMyAdmin. Once there navigate to your forums database. The view that pops up should say what collation each table is. Each table that is not the same you'll need to click on and go to the Operations tab.

Thank you for your reply.

It appears the host doesn't allow the viewing/editing of collation the way you described.  I see where these collation options are on the phpMyAdmin of a different site on a different host, but the phpMyAdmin of this site doesn't show anything related to collation.

The only place I see any mention of collation is when I click "Show MySQL system variables" on the phpMyAdmin home page, which displays the following:

Server variables and settings

Variable                Value

collation connection    latin1_swedish_ci
collation database      latin1_swedish_ci 
collation server        latin1_swedish_ci

When viewing the database, it has these table headings:
[ Table | Action | Records | Type | Size | Overhead ]

When viewing a table's Structure tab, it has these table headings:
[ Field | Type | Attributes | Null | Default | Extra | Action ]

When viewing a table's Operations tab, it has nothing about collation in it (see attachmed image).

[attachment=35427]

I can't find anything in the host's help docs that pertains to this either.

Is there some other way of editing this, then?

First, drop any fulltext indexes you may have. Normally this is in mybb_threads, mybb_posts, and mybb_privatemessages.

Run this script:

<?php
define("IN_MYBB",  1);
require_once "global.php";
if(!$mybb->usergroup['cancp'])
{
error_no_permission();
}
$tablelist = $db->list_tables($config['database']['database'], TABLE_PREFIX);
$character_set = 'utf8';
$collation = 'utf8_general_ci';
foreach($tablelist as $table)
{
$db->write_query("ALTER TABLE ". $table . " DEFAULT CHARACTER SET {$character_set} COLLATE {$collation}");
echo "Converted table " . $table . " to $collation<br />";
}
echo "Table conversion complete.<br />";
?>

Then re-add any indexes you may have dropped.

Thank you for your continued assistance.

Could you please explain in more detail how/where to go about doing those things?

(2015-10-23, 10:53 PM)dragonexpert Wrote: [ -> ]First, drop any fulltext indexes you may have.  Normally this is in mybb_threads, mybb_posts, and mybb_privatemessages.

Run this script:

<?php
define("IN_MYBB",  1);
require_once "global.php";
if(!$mybb->usergroup['cancp'])
{
error_no_permission();
}
$tablelist = $db->list_tables($config['database']['database'], TABLE_PREFIX);
$character_set = 'utf8';
$collation = 'utf8_general_ci';
foreach($tablelist as $table)
{
$db->write_query("ALTER TABLE ". $table . " DEFAULT CHARACTER SET {$character_set} COLLATE {$collation}");
echo "Converted table " . $table . " to $collation<br />";
}
echo "Table conversion complete.<br />";
?>

Then re-add any indexes you may have dropped.

Is there anyone that can please walk us through how to perform these actions?  We've been searching elsewhere for help on how to do this, but still cannot figure it out.  We are stuck using phpMyAdmin 2.11.11.2 and the host will not upgrade it at this time.

If it helps, here is some more info we figured out how to look up by running queries in the SQL tab of the database on phpMyAdmin:

Running this query...

SHOW CREATE DATABASE db_name;

...outputs:

CREATE DATABASE `db_name` /*!40100 DEFAULT CHARACTER SET latin1 */

Running this query on every MyBB table...

SHOW TABLE STATUS WHERE name='tbl_name'

...shows that all tables are of collation "cp1257_general_ci", EXCEPT for the following tables which are of collation "latin1_swedish_ci":

• abc_buddyrequests
  
• abc_delayedmoderation
  
• abc_questions
  
• abc_questionsessions
  
• abc_spamlog
  
• abc_threadprefixes
  
• abc_upgrade_data
  

Running this query on each of the tables in the list above...

SHOW FULL COLUMNS FROM tbl_name;

...shows the collations as either "NULL" or "latin1_swedish_ci".

Running that query on the abc_threads, abc_posts, abc_privatemessages, and a few other random tables shows collations as either "NULL" or "cp1257_general_ci".

We didn't have time to check the columns of every single table one by one by the method above, so we tried running this query to check them all at once...

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_name LIKE 'abc\_%'
ORDER BY table_schema, table_name, ordinal_position;

...but 95% of the time we kept getting the following error after it ran for several minutes:  "The requested URL was rejected. If you think this is an error, please contact the webmaster. "  We re-ran it a bunch of times and when it finally worked a couple of times without giving us an error, it did not show any inconsistencies in collations within the first 30 entries it successfully showed (however, clicking to show the next 30 entries, or more entries at once, resulted in the error message again, so we could never see the whole set of tables/columns with this method).

We would greatly appreciate any help with this issue!