Illegal Mix Of Collations

[ronjeremy_69]

After upgrading to SqlYog 6.0 beta 4, I cannot execute any queries to a server running mysqld 4.1.7. Each time it returns:

Error Code : 1267

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

This is over an SSH connection, if that makes any difference.

I can execute queries to a local server running mysqld 5.0.27 just fine, so it must be either something to do with the mysqld version, or the fact its over SSH.

Another annoyance that is occuring is that if mysqld gets restarted, I have to also restart SqlYog because it keeps saying “Error Code : 2006 MySQL server has gone away”.

I have downgraded back to SqlYog 5.29 and both of the above issues go away.

[peterlaursen]

1) It looks very much like this issue that I reported.

http://bugs.mysql.com/bug.php?id=26905

It is not SSH related.

Please execute

SHOW VARIABLES LIKE '%character%'

on both the remote and the local 5.0 server

Can you paste the complete HISTORY in here.  It will tell us what succeds and what does not!

2) the 'gone away' thing.  We have a few more similar reports. We are working on it.

[ronjeremy_69]

peterlaursen wrote on Apr 30 2007, 06:20 AM:

Please execute

SHOW VARIABLES LIKE '%character%'

on both the remote and the local 5.0 server

remote:

Variable_name Value

---

---

character_set_client latin1

character_set_connection latin1

character_set_database latin1

character_set_results latin1

character_set_server latin1

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

local (5.0):

Variable_name Value

---

---

character_set_client latin1

character_set_connection latin1

character_set_database latin1

character_set_filesystem binary

character_set_results latin1

character_set_server latin1

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

peterlaursen wrote on Apr 30 2007, 06:20 AM:

Can you paste the complete HISTORY in here. It will tell us what succeds and what does not!

no query succeds at all.

[peterlaursen]

I now notice this

Quote:

a server running mysqld 4.1.7

4.1.7 was the first version in the 4.1.x tree that had the 'stable' predicate.  But the charset concept and unicode implementations were new.  And still very buggy.

I will have to say that we cannot support server versions antique as this one!

What OS is this server running?  There is a lot of red hat's running 4.1.10 in the world (the one that it shipped with originally).  But 4.1.7 is a rare version! (and the before mentioned red hat's should have 4.1.20 installed.  Official RH RPMs are available at least for 4.1.20.  We did this on this server itself about 9 months ago and that solved several issues!

[ronjeremy_69]

peterlaursen wrote on May 2 2007, 03:06 PM:

I now notice this

4.1.7 was the first version in the 4.1.x tree that had the 'stable' predicate.  But the charset concept and unicode implementations were new.  And still very buggy.

I will have to say that we cannot support server versions antique as this one!

What OS is this server running?  There is a lot of red hat's running 4.1.10 in the world (the one that it shipped with originally).  But 4.1.7 is a rare version! (and the before mentioned red hat's should have 4.1.20 installed.  Official RH RPMs are available at least for 4.1.20.  We did this on this server itself about 9 months ago and that solved several issues!

Its running redhat 8, and both the application and server are production and considered stable so i dare not mess with it.

I guess ill just have to stay with the current stable version of SqlYog that works as it always did. Not a big deal, unless you were to finally come out with the “Show Table Data In Text” feature for which I have been waiting 2 years. Then, I'd consider going to the trouble of upgrading from 4.1.7 to 4.1.20 🙂

[peterlaursen]

the one thing I do not fully understand is how it happens that NOTHING is written to HISTORY.  There must be at least one statement sent if the server returns an error.  Not even 'show variables …' or 'set names —' in history?

Is there a 'general log' on this server?

However upgrading RPM-based MySQL is very simple actually and I would recommend.  It was given the predicate 'stable' when released, but check yourself the changelogs for bugfixes between 4.1.7 and 4.1.22!

Please confirm this:  this error message appears at the very moment you connect with SQLyog 6 beta? Do you have an option to use other connection methods?-

I do no say that this is proven to be a bug with MySQL. But if it is, we may be able to do some 'workaround'. But with that old server version there are limits to how much effort we will spend on that!

is it possible to get temporary access to the server?

Did you try from different client machines?

[peterlaursen]

please also explain “I cannot execute any queries to a server running mysqld 4.1.7” — it it only queries that you write yourself in the SQL pane?  What about the queries that are generated by SQLyog?

Please explain in details what happens from the very moment you (try to) connect .. what you are doing and what response you get on everything?

[peterlaursen]

we installed a 4.1.7 mySQL server on Windows.  the issue that you report is not reproducable.

Please explain very much in detail as I requested before!

[ronjeremy_69]

peterlaursen wrote on May 3 2007, 09:02 AM:

we installed a 4.1.7 mySQL server on Windows.  the issue that you report is not reproducable.

Please explain very much in detail as I requested before!

sorry, i had to reinstall it to play. i can connect just fine, and while i previously thought no queries were running at all, its just when i have a 'where' clause in a query.

here is the complete history which reproduces the issue for me:

/*[5:17:20 PM][ 0 ms]*/ Set names 'utf8'

/*[5:17:20 PM][ 0 ms]*/ show databases

/*[5:17:23 PM][ 0 ms]*/ use `test`

/*[5:17:26 PM][ 16 ms]*/ CREATE TABLE `requests` (`id` int(1) NOT NULL auto_increment,`sitename` varchar(200) default NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM

/*[5:17:27 PM][ 0 ms]*/ select * from requests where sitename = 'www.blah.com'

the last query produces: “Error Code : 1267

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

note that if i just do “select * from requests” the error does not appear.

i tried the above on a second machine which is also running 4.1.7

If you cannot reproduce with the above, Peter please pm me and ill see if i can setup a guest login for you.

[peterlaursen]

It is reproduced now.

It is a bug in early MySQL 4.1.x that was fixed in more rounds but only fixed ultimately in 4.1.12!  You can see details in the server changelogs.

We also know a workaround for this, but we do not like it, because it is very dirty and unhealthy SQL we will need to send!  We also need to be perfectly sure that that are no unwanted side-effedcts (for ALL locales and non-western users is the problem!).

We will need a few days for considering this, but anyway next 6.0 release will be in about 1 week only, so we have the time to think about it!

It is working with MySQL 4.1.10 (as shipped with RHEL4 and Fedora3).

[ronjeremy_69]

peterlaursen wrote on May 4 2007, 01:52 AM:

It is reproduced now.

It is a bug in early MySQL 4.1.x that was fixed in more rounds but only fixed ultimately in 4.1.12!  You can see details in the server changelogs.

We also know a workaround for this, but we do not like it, because it is very dirty and unhealthy SQL we will need to send!  We also need to be perfectly sure that that are no unwanted side-effedcts (for ALL locales and non-western users is the problem!).

We will need a few days for considering this, but anyway next 6.0 release will be in about 1 week only, so we have the time to think about it!

It is working with MySQL 4.1.10 (as shipped with RHEL4 and Fedora3).

Sounds good, though im pretty indifferent if it gets fixed or not, since I can just use 5.29. The new graphic query builder in 6.0 is somewhat neat, but nothing to be excited about. Honestly, Id rather just spend a day of my time testing and migrating to 4.1.10, ignore the collation bug, and for you guys to spend that time instead on “Show Table Data In Text”. And id pay you $100 just out of sheer joy and gratitude.

[andy b]

I use Set names 'charset' (e.g. Set names 'latin1') to solve this problem (for >6.0 version of sqlyog)

[Rohit]

@ronjeremy_69

You persistence is amazing!

I will speak to the SQLyog team to find out the effort required.

[peterlaursen]

@andy

You should absolutely not SET NAMES yourself when working with SQLyog and connected to MySQL 4.1 or higher!

Only English/ASCII characters will be treated correctly then.  You can destroy your data completely!

SQLyog 6.0 uses utf8 internally!  ONLY utf8.  No matter how data are stored!

You cannot compensate for a server bug like this!  Upgrade the server!

[ronjeremy_69]

Rohit wrote on Jun 14 2007, 10:27 AM:

@ronjeremy_69

You persistence is amazing!

I will speak to the SQLyog team to find out the effort required.

Thanks man. Sometimes I think they won't make the “Show Table Data In Text” option just so I'll continue to frequent the forum and give detailed bug reports 🙂

But seriously, I would have literally saved at least 3-4 days of development time over the last two years if this feature was available. If you're a developer/coder, which I'm sure most of the folks using SQLyog are, it's all about the text. Sure, the grid is nice if I want to change a cell value manually instead of my application doing it, but most of the time I am just wanting to browse the the database results produced by the application I am developing, and selectively highlight/copy some text as input for more testing or building another query. Very often I need to copy-n-paste the column names from the textual results tab into my application source, and to do that currently every time i have to “select * from …blah” (and possibly worry about a limit if the table is large), whereas it would be so nice to be able to just click on a table and have its results displayed in text, adhering to the SQLyog row limit configuration. I realize I can get a table definition in text by clicking the “Objects” tab but it's just not as efficient as having them displayed horizontally along with the table data.

One click. Table data displayed as text instead of in grid. Thats all I'm asking for here.

[Rohit]

@ronjeremy_69

I tried the following workaround and it worked great for me!

1. Enable “Show results in Text”

2. Select the table and press Ctrl+Shift+S

3. Press F5

This was quite fast for me!

Am I missing something?

[peterlaursen]

@Rohit

Ron wants this in DATA tab – not RESULT tab!

I think it is more than a year ago I told theat he could SELECT * FROM … and work from RESULT tab!

[Rohit]

I think Ron wants to avoid typing “SELECT * FROM..”

[ronjeremy_69]

Rohit wrote on Jun 17 2007, 11:07 PM:

@ronjeremy_69

I tried the following workaround and it worked great for me!

1. Enable “Show results in Text”

2. Select the table and press Ctrl+Shift+S

3. Press F5

This was quite fast for me!

Am I missing something?

Ctrl+Shift+S/F5 is an unacceptable alternative for a variety of reasons:

A.) The biggest reason, it requires me to not just click the table, but also make two additional keystrokes. Additional keystrokes = wasted time and less productivity, especially when this is something I need to do hundreds (or even thousands) of times per week.

B.) It clutters up the Query pane with a bunch of SQL statements I dont want, so i have to waste time deleteing them.

C.) In the normal case my cusror is not at the bottom of the Query editing pane, the “SELECT …” statement is inserted in the middle or between existing statements, so pressing F5 results in “You have an error in your SQL syntax;”.

D.) The table data needs to appear in the DATA tab, not the RESULTS tab as would occur with F5.

[Author]

Posts