Error in numRows(): Illegal mix of collations

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Error in numRows(): Illegal mix of collations

muyuubyou
When I try to create a Chinese or Japanese page I get this error:

 Error in numRows(): Illegal mix of collations (utf8_bin,IMPLICIT) and
(latin1_swedish_ci,COERCIBLE) for operation '='


Backtrace:

   - GlobalFunctions.php line 451 calls wfbacktrace()
   - Database.php line 543 calls wfdebugdiebacktrace()
   - WatchedItem.php line 58 calls databasemysql::numrows()
   - User.php line 1098 calls watcheditem::iswatched()
   - Title.php line 891 calls user::iswatched()
   - SkinTemplate.php line 646 calls title::useriswatching()
   - SkinTemplate.php line 386 calls
   skinmonobook::buildcontentactionurls()
   - OutputPage.php line 467 calls skinmonobook::outputpage()
   - OutputPage.php line 680 calls outputpage::output()
   - Database.php line 400 calls outputpage::databaseerror()
   - Database.php line 347 calls databasemysql::reportqueryerror()
   - Database.php line 723 calls databasemysql::query()
   - Database.php line 645 calls databasemysql::select()
   - LinkCache.php line 162 calls databasemysql::selectfield()
   - Title.php line 1150 calls linkcache::addlinkobj()
   - index.php line 86 calls title::getarticleid()

What can I do about it? I've checked my tables and they're utf8. I'm trying
to create a wiki about the Chinese language, so romanizing the titles is not
an option.

Regards,
  Muyuu
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

Brion Vibber
muyuubyou wrote:
> When I try to create a Chinese or Japanese page I get this error:
>
>  Error in numRows(): Illegal mix of collations (utf8_bin,IMPLICIT) and
> (latin1_swedish_ci,COERCIBLE) for operation '='

Did you change the character set configuration of MySQL or the tables? Don't do
that. MediaWiki uses UTF-8, but doesn't pay much attention to what MySQL thinks
the charset is; under the old "latin-1" default it'll run good clean binary
UTF-8 on top of that with few problems. Under the experimental MySQL 5 mode it
can use tables which are set up as UTF-8. Don't try to mix these modes.

-- brion vibber (brion @ pobox.com)


_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l

signature.asc (257 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

muyuubyou
No, I didn't manually touch that. I just checked it out. Thanks for the
swift reply anyway :)

On 2/4/06, Brion Vibber <[hidden email]> wrote:

>
> muyuubyou wrote:
> > When I try to create a Chinese or Japanese page I get this error:
> >
> >  Error in numRows(): Illegal mix of collations (utf8_bin,IMPLICIT) and
> > (latin1_swedish_ci,COERCIBLE) for operation '='
>
> Did you change the character set configuration of MySQL or the tables?
> Don't do
> that. MediaWiki uses UTF-8, but doesn't pay much attention to what MySQL
> thinks
> the charset is; under the old "latin-1" default it'll run good clean
> binary
> UTF-8 on top of that with few problems. Under the experimental MySQL 5
> mode it
> can use tables which are set up as UTF-8. Don't try to mix these modes.
>
> -- brion vibber (brion @ pobox.com)
>
>
>
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
>
>
>
>
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

muyuubyou
Oh BTW I'm using MySQL 4.1, I suspect the problem is there :(

Can you guys try to create a chinese page? try copy-pasting this: 漢語

On 2/4/06, muyuubyou <[hidden email]> wrote:

>
> No, I didn't manually touch that. I just checked it out. Thanks for the
> swift reply anyway :)
>
> On 2/4/06, Brion Vibber < [hidden email]> wrote:
>
> > muyuubyou wrote:
> > > When I try to create a Chinese or Japanese page I get this error:
> > >
> > >  Error in numRows(): Illegal mix of collations (utf8_bin,IMPLICIT) and
> > > (latin1_swedish_ci,COERCIBLE) for operation '='
> >
> > Did you change the character set configuration of MySQL or the tables?
> > Don't do
> > that. MediaWiki uses UTF-8, but doesn't pay much attention to what MySQL
> > thinks
> > the charset is; under the old "latin-1" default it'll run good clean
> > binary
> > UTF-8 on top of that with few problems. Under the experimental MySQL 5
> > mode it
> > can use tables which are set up as UTF-8. Don't try to mix these modes.
> >
> > -- brion vibber (brion @ pobox.com)
> >
> >
> >
> > _______________________________________________
> > MediaWiki-l mailing list
> > [hidden email]
> > http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
> >
> >
> >
> >
>
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

muyuubyou
Ok, after wasting my weekend and losing faith in mediawiki I think I've
found something.

As explained here:
http://groups.google.com/group/mailing.database.myodbc/browse_thread/thread/f9ac09eec70ed2c5/789159e62b8a1573%23789159e62b8a1573?sa=X&oi=groupsr&start=0&num=2

MySQL4 won't swallow many queries that would work with mySQL3 because it's
pickier about collations.

I found myself in this situation:

mysql> SHOW VARIABLES LIKE 'character_set_%';
+――――――――�C+―――――――――-+
| Variable_name | Value |
+――――――――�C+―――――――――-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | c:\mysql\share/charsets/ |
+――――――――�C+―――――――――-+
7 rows in set (0.00 sec)


mysql> SHOW VARIABLES LIKE 'collation_%';
+―――――――-+――――――-+
| Variable_name | Value |
+―――――――-+――――――-+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+―――――――-+――――――-+
3 rows in set (0.00 sec)

 To summarize...

In the file includes/Database.php , at the beginning of function doQuery(),
add:

mysql_query("SET NAMES 'utf8′;", $this->mConn );

Otherwise you just can't rely on the names of new pages being in the same
collation (usually latin1). This has given me several different errors and I
wouldn't rule out there might be possible injection attacks due to this.


Please somebody, warn about mySQL4 being not properly tested in bold font in
the requirements section.


On 2/4/06, muyuubyou <[hidden email]> wrote:

>
> Oh BTW I'm using MySQL 4.1, I suspect the problem is there :(
>
> Can you guys try to create a chinese page? try copy-pasting this: �h�Z
>
> On 2/4/06, muyuubyou <[hidden email] > wrote:
> >
> > No, I didn't manually touch that. I just checked it out. Thanks for the
> > swift reply anyway :)
> >
> > On 2/4/06, Brion Vibber < [hidden email]> wrote:
> >
> > > muyuubyou wrote:
> > > > When I try to create a Chinese or Japanese page I get this error:
> > > >
> > > >  Error in numRows(): Illegal mix of collations (utf8_bin,IMPLICIT)
> > > and
> > > > (latin1_swedish_ci,COERCIBLE) for operation '='
> > >
> > > Did you change the character set configuration of MySQL or the tables?
> > > Don't do
> > > that. MediaWiki uses UTF-8, but doesn't pay much attention to what
> > > MySQL thinks
> > > the charset is; under the old "latin-1" default it'll run good clean
> > > binary
> > > UTF-8 on top of that with few problems. Under the experimental MySQL 5
> > > mode it
> > > can use tables which are set up as UTF-8. Don't try to mix these
> > > modes.
> > >
> > > -- brion vibber (brion @ pobox.com)
> > >
> > >
> > >
> > > _______________________________________________
> > > MediaWiki-l mailing list
> > > [hidden email]
> > > http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
> > >
> > >
> > >
> > >
> >
>

_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

Brion Vibber
muyuubyou wrote:
> Please somebody, warn about mySQL4 being not properly tested in bold font in
> the requirements section.

We've used MySQL 4 in production for years.

-- brion vibber (brion @ pobox.com)


_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l

signature.asc (257 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

muyuubyou
In such case, either the chinese and japanese versions at wikipedia aren't
using mySQL4, or they're not using only the default installer. Out of the
box, it doesn't work. Plain and simple. I can know point you to several
sites having this same problem and I reckon some of them have just jumped to
alternative wiki packages.

I'm sure it works out of the box for latin_1 compatible languages (i.e. most
other than asian languages and a couple exotic others). But this may not be
enough.

Google tells me you are one of the programmers, so I kindly ask you to give
this a look. I would do it myself, but I'm new to mediawiki and it would be
best if someone with deep understanding of its code and dependencies did it.
Of course when you have the time.

I have another little issue: firefox doesn't post chinese/japanese/korean
name titles properly (mangles encoding) while Opera and IE does. I haven't
tried mac versions because the woman has the mac on a trip, but opera for
linux works too. Realistically (and sadly), most visitors will be using IE,
so maybe it's not too bad. Maybe there are people dedicated to i18n and
testing? Not sure what this is, but I might research it myself if there
isn't a known fix somewhere I don't know. For now, I'm just adding pages
with a compatible browser and mozilla users will be limited to browsing and
editing for now. Not that the site is public yet, we're just two people
using it now... it will be under testing for a week or so.

Please understand the not-so-polite (but also not too rude) tone in my
previous email, I was very frustrated... I expected to use that time adding
content instead. During weekdays I'll too darned busy :(

On 2/5/06, Brion Vibber <[hidden email]> wrote:

>
> muyuubyou wrote:
> > Please somebody, warn about mySQL4 being not properly tested in bold
> font in
> > the requirements section.
>
> We've used MySQL 4 in production for years.
>
> -- brion vibber (brion @ pobox.com)
>
>
>
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
>
>
>
>
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

admin Yellowikis
It works fine for us - we have MW running in English with pages in
Japanese, Chinese and Korean. No problems from users and/or editors in
these languages either.
We are on:
    *  MediaWiki: 1.5.5
    * PHP: 4.3.10-16 (apache)
    * MySQL: 4.1.11-Debian_4sarge2-log

Paul

On 2/6/06, muyuubyou <[hidden email]> wrote:

> In such case, either the chinese and japanese versions at wikipedia aren't
> using mySQL4, or they're not using only the default installer. Out of the
> box, it doesn't work. Plain and simple. I can know point you to several
> sites having this same problem and I reckon some of them have just jumped to
> alternative wiki packages.
>
> I'm sure it works out of the box for latin_1 compatible languages (i.e. most
> other than asian languages and a couple exotic others). But this may not be
> enough.
>
> Google tells me you are one of the programmers, so I kindly ask you to give
> this a look. I would do it myself, but I'm new to mediawiki and it would be
> best if someone with deep understanding of its code and dependencies did it.
> Of course when you have the time.
>
> I have another little issue: firefox doesn't post chinese/japanese/korean
> name titles properly (mangles encoding) while Opera and IE does. I haven't
> tried mac versions because the woman has the mac on a trip, but opera for
> linux works too. Realistically (and sadly), most visitors will be using IE,
> so maybe it's not too bad. Maybe there are people dedicated to i18n and
> testing? Not sure what this is, but I might research it myself if there
> isn't a known fix somewhere I don't know. For now, I'm just adding pages
> with a compatible browser and mozilla users will be limited to browsing and
> editing for now. Not that the site is public yet, we're just two people
> using it now... it will be under testing for a week or so.
>
> Please understand the not-so-polite (but also not too rude) tone in my
> previous email, I was very frustrated... I expected to use that time adding
> content instead. During weekdays I'll too darned busy :(
>
> On 2/5/06, Brion Vibber <[hidden email]> wrote:
> >
> > muyuubyou wrote:
> > > Please somebody, warn about mySQL4 being not properly tested in bold
> > font in
> > > the requirements section.
> >
> > We've used MySQL 4 in production for years.
> >
> > -- brion vibber (brion @ pobox.com)
> >
> >
> >
> > _______________________________________________
> > MediaWiki-l mailing list
> > [hidden email]
> > http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
> >
> >
> >
> >
> _______________________________________________
> MediaWiki-l mailing list
> [hidden email]
> http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
>


--
Yellowikis is to Yellow Pages, as Wikipedia is to The Encyclopedia Britannica
_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Reply | Threaded
Open this post in threaded view
|

Re: Error in numRows(): Illegal mix of collations

Brion Vibber
In reply to this post by muyuubyou
muyuubyou wrote:
> In such case, either the chinese and japanese versions at wikipedia aren't
> using mySQL4,

They are all using MySQL 4.0.

Note that MySQL 4.1 and higher have some fancier and uglier character set
capabilities. I've never bothered to test 4.1 much (but have received no
complaints about it other than installer issues related to one of the password
upgrade library incompatibility problem or the key size on the categorylinks
table with InnoDB support missing), but on 5.0 everything seems to work fine
either running binary UTF-8 on top of the 8-bit latin1 charset, or using the
experimental raw UTF-8 mode on the UTF-8 charset (with the exception that the
rare 4-byte UTF-8 characters cause save failures, since MySQL is living in the
early 1990s and doesn't realize that Unicode is more than 16 bits.)

A default install of MySQL uses latin1 as the default, and the old MediaWiki
defaults (UTF-8 binary) work fine. If you want to explicitly try UTF-8 raw mode,
select that in the installer.

> or they're not using only the default installer. Out of the
> box, it doesn't work. Plain and simple. I can know point you to several
> sites having this same problem and I reckon some of them have just jumped to
> alternative wiki packages.

Point away, I'd love to hear specifics. But don't talk about MySQL 3; we don't
even support it anymore. :)

> I'm sure it works out of the box for latin_1 compatible languages (i.e. most
> other than asian languages and a couple exotic others). But this may not be
> enough.

All languages work fine; the language has no relevance since we *only* support
UTF-8, not Latin-1 at all anymore.

> I have another little issue: firefox doesn't post chinese/japanese/korean
> name titles properly (mangles encoding) while Opera and IE does.

Firefox works just fine. If you are having a problem you need to be specific
about it.

-- brion vibber (brion @ pobox.com)


_______________________________________________
MediaWiki-l mailing list
[hidden email]
http://mail.wikipedia.org/mailman/listinfo/mediawiki-l

signature.asc (257 bytes) Download Attachment