Illegal Mix Of Collations

[Regiane]

Good morning,

Sorry the english, but I am Brazilian and I am using an online translator.

I'm having a problem in sync version of SQLyog Enterprise 6.13. When the sync, the system is returning me the following error:

`Invoice` Error 3426 3424 No. 1267

Illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_general_ci, coercible) for operation '='

Error No. 1267

Illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_general_ci, coercible) for operation '='

1st Phase: 0 0

Already changed the collation of the table of the source and target, but the problem still persists.

The my.ini is configured as follows:

default_character_set=latin1

Mysql and variables like this:

character_set_client utf8

character_set_connection utf8

character_set_database latin1

CHARACTER_SET_RESULTS utf8

character_set_server latin1

character_set_system utf8

In total I have 100 tables and only one is in the problem.

The other is to sync with no problem.

I do not know what else to try to resolve the problem.

I hope someone can pass me a solution.

[peterlaursen]

Please try a recent program version.  You can use the 8.2 TRIAL. What can we do if there is a bug in 6.13? Nothing really as we can only fix 'forwards' not 'backwards'.

If problem persists with 8.2 then please 

1) tell versions of bothand server. The 'illegal mix of collation' error could very well be a server bug if you are using an early 4.1 or 5.0 server.

2) Attach two small dumps to reproduce with. At least we will need *table structure* – but even better would be a dump of a single table with a few rows where this is reproducible (please zip into one file)

[Regiane]

Hi,

I just have try with a 8.2 version, and the problem persist.

I send two files and one image, with structs for you reproduce.

I sendo too the log of server. The two structs are the same MySQL server.

[attachment=1299:Error Forum.zip][attachment=1299:Error Forum.zip]

If problem persists with 8.2 then please 

1) tell versions of bothand server. The 'illegal mix of collation' error could very well be a server bug if you are using an early 4.1 or 5.0 server.

2) Attach two small dumps to reproduce with. At least we will need *table structure* – but even better would be a dump of a single table with a few rows where this is reproducible (please zip into one file)

[/quote]

[Regiane]

Hi Peter,

Do you have somenthing about the problem?

The files I sent were enough?

I expect a reply

[peterlaursen]

oops .. I think we overlooked your last post.  We will look at the files now (what will probably be tomorrow morning).  You did not tell the server version(s).  Please tell.

[peterlaursen]

I see that your dump says “MySQL – 4.1.7-nt”

Please read: 

http://www.webyog.co…-supported.html

Quote:

“for 4.1 specifically we recommend 4.1.20 or higher. Some Linux distributions (most important RHEL4) originally shipped with 4.1.10. This version has serious bugs in charset handling (of which most were fixed in 4.1.12). Official RHEL 4.1.20 builds are available for upgrade by RHEL4 users.”

.. and I can add that you can download 4.1.22 from any MySQL FTP-mirror like here: ftp://mirrors.dotsrc…oads/MySQL-4.1/  .. but note that MySQL do not support 4.1 anymore and also 5.0 will go out of support by end of this month (unless they change their plans).

Without have tried your dumps I am convinced it is a server issue. Refer to: http://dev.mysql.com…news-4-1-x.html . 4.1.7 was released as 'production quality' but it is buggy as regards charset handling (COLLATIONS were completely new to MySQL in 4.1 – earlier MySQL versions did not have)!  Why was that server not upgraded long ago?

[peterlaursen]

works fine with recent servers:

Code:

SQLyog Job Agent Version 8.21 test1 Copyright(c) Webyog Softworks Pvt. Ltd. All Rights Reserved.

Sync started at Tue Dec 15 16:50:52 2009

Table SrcRows TgtRows Inserted Updated Deleted
========================= ======= ======= ======== ======= =======
`notafiscal` 7 4 3 0 0

Total time taken – 0 sec(s)

We neither will nor can solve this. It is a bug with the server you are using.  

[Regiane]

Hi Peter,

Tks for the reply and I will try the solution.

Tks again

[Author]

Posts