Recent Topics
  1. b2evolution CMS Support Forums
  2. b2evolution Development
  3. Bug reports
  4. GitHub & Preview releases
  5. [1.8] "Illegal mix of collations" SQL error

[1.8] "Illegal mix of collations" SQL error

1 Jul 05, 2006 15:33    

1) Subj

b2evolution 1.8-beta (win_XP,localhost, _locales.php($default_locale = 'en-US') )

2)backoffice: (bookmark Posts)

An unexpected error has occured!

If this error persits, please report it to the administrator.

Go back to home page
Additional information about this error:

MySQL error!

Illegal mix of collations for operation ' IN '(Errno=1271)

Your query:

        SELECT COUNT( DISTINCT post_ID)  
FROM b2_evo_posts INNER JOIN b2_evo_postcats ON post_ID = postcat_post_ID
                  INNER JOIN b2_evo_categories ON postcat_cat_ID = cat_ID  
WHERE (cat_blog_ID = 4) 
  AND ( (  ( post_status = "private" 
  AND post_creator_user_ID = 1 )  
  OR post_status IN ('published','protected','draft','deprecated') ) )


3) any blog:

An unexpected error has occured!

If this error persits, please report it to the administrator.

Go back to home page
Additional information about this error:

MySQL error!

Illegal mix of collations (cp1251_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '(Errno=1270)

Your query: Get ID list for Item List (Main|Lastpostdate) Query

SELECT DISTINCT post_ID 
FROM b2_evo_posts INNER JOIN b2_evo_postcats ON post_ID = postcat_post_ID
                  INNER JOIN b2_evo_categories ON postcat_cat_ID = cat_ID 
WHERE (cat_blog_ID = 2) 
  AND ( (  ( post_status = "private" 
  AND post_creator_user_ID = 1 )  
  OR post_status IN ('published','protected') ) ) 
  AND (post_datestart <= '2006-07-05 17:23:24') 
ORDER BY post_datestart DESC 
LIMIT 5

:(

Back to top

2 Jul 14, 2006 08:39

1) I switch ON debug, and see problem in "blogs\inc\_misc\_db.class.php"
2) Next I change locale an all blogs on Rissian.
3) I have no error :).

To developer: maybe "blogs\inc\_misc\_db.class.php"
static $mysql_charset_map = array(
...
'windows-1251' => 'cp1251', /*cp1251_bin, cp1251_general_ci*/
...
); ?

Back to top

3 Jul 14, 2006 17:54

So you mean that an incorrect "SET NAMES" has been the cause?

What "SET NAMES" got used? Which gets used now? (see debug output)

What is "evo_charset" on your install? (see debug output)

Have you set $db_config["connection_charset"]?

Back to top

4 Jul 15, 2006 14:45

1) I only think :)

2) In backoffice:
-----
locale
default_locale from conf: ru-RU
default_locale from DB: ru-RU
default_locale from HTTP_ACCEPT: ru-RU
Set DB connection charset: cp1251
evo_charset (_main.inc.php): windows-1251
io_charset (_main.inc.php): windows-1251
-----

3) In front-end if
$db_config['connection_charset'] = '';
$evo_charset = '';

I see the error and
-----
locale:
* default_locale from conf: ru-RU
* default_locale from DB: ru-RU
* default_locale from HTTP_ACCEPT: ru-RU
* Set DB connection charset: cp1251
* evo_charset (_main.inc.php): windows-1251
* io_charset (_main.inc.php): windows-1251
* Activating blog locale: en-EU
* $evo_charset differs from $io_charset, but mbstrings does not seem to be installed.
* Adjusted I/O charset for blog
* Set DB connection charset: latin1
* evo_charset: iso-8859-1
* io_charset: iso-8859-1
-----
If I set:
$db_config['connection_charset'] = 'cp1251';
I see the error in blog A(for example) and
-----
locale:
* Set DB connection charset: cp1251
* default_locale from conf: ru-RU
* default_locale from DB: ru-RU
* default_locale from HTTP_ACCEPT: ru-RU
* evo_charset (_main.inc.php): windows-1251
* io_charset (_main.inc.php): windows-1251
* Activating blog locale: en-EU
* $evo_charset differs from $io_charset, but mbstrings does not seem to be installed.
* Adjusted I/O charset for blog
* Set DB connection charset: latin1
* evo_charset: iso-8859-1
* io_charset: iso-8859-1
-----
4) If I set the Blog setting/Blog A/Main Locale(Russian windows-1251) I see no error and
-----
locale:
* default_locale from conf: ru-RU
* default_locale from DB: ru-RU
* default_locale from HTTP_ACCEPT: ru-RU
* default_locale from user profile: en-EU
* Set DB connection charset: latin1
* evo_charset (_main.inc.php): iso-8859-1
* io_charset (_main.inc.php): iso-8859-1
* Activating blog locale: ru-RU
* $evo_charset differs from $io_charset, but mbstrings does not seem to be installed.
* Adjusted I/O charset for blog
* Set DB connection charset: cp1251
* evo_charset: windows-1251
* io_charset: windows-1251
-----

Back to top

5 Jul 15, 2006 16:58

What MySQL server version are you using?

Back to top

6 Jul 15, 2006 18:19

blueyed wrote:

What MySQL server version are you using?

MySQL 4.1.8 on localhost.
This junk?

Back to top

7 Jul 15, 2006 18:41

Can you try updating it to the current 4.1 release (4.1.20)?
It may "just" be a MySQL bug/problem.

I don't really understand it, but

Illegal mix of collations (cp1251_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '(Errno=1270)

seems to refer to:


post_status IN ('published','protected')

The error does appear, if the DB connection charset is not set to cp1251 (what you do "through using your locale"; $db_config['connection_charsets'] gets overridden..!)

Please try upgrading and hopefully that will fix it.

Back to top

8 Jul 15, 2006 19:36

Many thanks I shall try to make it in Manday.
And I tell about result :)

Back to top


Form is loading...