Solved

Character set Mismatch in VB.NET and mysql on linux

Posted on 2007-12-06
10
611 Views
Last Modified: 2008-02-01
Hi Experts,
I am facing one starnge problem  for the following ,
I have created one small connctivity application in VB.NET. This application fetch the data from the mysql server which is running  on the red hat linux machine .
The connection seems to be getting well but when the query executes for the follwoing statement,

Select * from user_groups where limitversion = 'Y';
 it gives me the following exception
Message      "[MySQL][ODBC 5.1 Driver][mysqld-4.1.7-max-log]Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"      

I also tried the same query without any condition as
Select * from user_groups
 I t worked excellently . But I want former to work.

 Then  I tried to chang the character set on both the machines but same problem persists.

Please adivce where should I investigate to get through the problem,

Regards,
Sham.






0
Comment
Question by:Aloha_Technology
  • 4
  • 4
10 Comments
 
LVL 3

Expert Comment

by:JeffvClayton
Comment Utility
Found this ay MySQL.com

10.1.5.5. Collations Must Be for the Right Character Set
Each character set has one or more collations, but each collation is associated with one and only one character set. Therefore, the following statement causes an error message because the latin2_bin collation is not legal with the latin1 character set:
mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'
In some cases, expressions that worked before MySQL 4.1 fail in early versions of MySQL 4.1 if you do not take character set and collation into account. For example, before 4.1, this statement works as is:
mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
+-------------------------------+
| SUBSTRING_INDEX(USER(),'@',1) |
+-------------------------------+
| root                          |
+-------------------------------+
The statement also works as is in MySQL 4.1 as of 4.1.8: In MySQL 4.1, usernames are stored using the utf8 character set (see Section 10.1.8, UTF-8 for Metadata). The literal string '@' has the server character set (latin1 by default). Although the character sets are different, MySQL can coerce the latin1 string to the character set (and collation) of USER() without data loss. It does so, performs the substring operation, and returns a result that has a character set of utf8.
However, in versions of MySQL 4.1 before 4.1.8, the statement fails:
mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
ERROR 1267 (HY000): Illegal mix of collations
(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
for operation 'substr_index'
This happens because the automatic character set conversion of '@' does not occur and the string operands have different character sets (and thus different collations):
mysql> SELECT COLLATION(USER()), COLLATION('@');
+-------------------+-------------------+
| COLLATION(USER()) | COLLATION('@')    |
+-------------------+-------------------+
| utf8_general_ci   | latin1_swedish_ci |
+-------------------+-------------------+
One way to deal with this is to upgrade to MySQL 4.1.8 or later. If that is not possible, you can tell MySQL to interpret the literal string as utf8:
mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
+------------------------------------+
| SUBSTRING_INDEX(USER(),_utf8'@',1) |
+------------------------------------+
| root                               |
+------------------------------------+
Another way is to change the connection character set and collation to utf8. You can do that with SET NAMES 'utf8' or by setting the character_set_connection and collation_connection system variables directly
0
 

Author Comment

by:Aloha_Technology
Comment Utility
I did the changes for character set and the collation  then following command shows like,

show variables like '%character%';show variables like '%collation%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
7 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
agian I restarted the mysql  but the scinario was changed now as
show variables like '%character%';show variables like '%collation%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| 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       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
7 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+

I could not get it the change permanently still with the temperory cahnges (first case ) I run the application still  the prolem persists.

any further suggesions will help .

Thanks,
Sham.








0
 
LVL 3

Expert Comment

by:JeffvClayton
Comment Utility
http://www.informit.com/articles/article.aspx?p=328641&seqNum=3&rl=1

This link also has a lot of relevant info which may help to narrow down the problem
0
 

Author Comment

by:Aloha_Technology
Comment Utility
I have already followed this link and updated all the character set fields for mysql.

The changes described here to chane the charater set seems to have temperary effect on the data base because when I restarted the mysql all the changes were gone .

I tried all the changes  described over here but could not manage to get the error solved .

Can It be the version problem for the mysql.

Because On one of the simliler machine  where the mysql version 4.1.22 is ruuning the application is ruunning quite well.

I would also like to try for the permanent changes of the charater set and also for swapping of the version of the mysql.
Please advice ,

Regards,
Sham.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Expert Comment

by:JeffvClayton
Comment Utility
The issue you have does seem to be a bug in earlier version of MySQL 4.1. If you have no problems with 4.1.22 and the configuration then upgrading the plder version would seem like a good idea to try
0
 

Author Comment

by:Aloha_Technology
Comment Utility
Hi,

This seems to be ultimate solutin to the problem.

I am currently installing the new mysql 4.1.22 on my linux machine.

After following all the proccess for installation of the mysql 4.1.22  when I trieid to run the
bin/mysql_install_db --user=mysql

it gives me the following error and I m totally stuck with that.

./mysql_install_db: line 217: /usr/local/mysql/libexec/mysqld: cannot execute
binary file
Installation of system tables failed!

Examine the logs in /usr/local/mysql/var for more information.
You can also try to start the mysqld daemon with:
/usr/local/mysql/libexec/mysqld --skip-grant &
You can use the command line tool
/usr/local/mysql/bin/mysql to connect to the mysql
database and look at the grant tables:

shell> /usr/local/mysql/bin/mysql -u root mysql
mysql> show tables

Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /usr/local/mysql/var that may be helpful.

any suggession would really help.
0
 

Author Comment

by:Aloha_Technology
Comment Utility


and the error log is showing the following
071213 04:35:51  mysqld started
Unknown suffix '@' used for variable 'port' (value '@MYSQL_TCP_PORT@')
071213  4:35:51 [ERROR] /usr/local/mysql/libexec/mysqld: Error while setting
value '@MYSQL_TCP_PORT@' to 'port'


Please advice.
0
 
LVL 3

Accepted Solution

by:
JeffvClayton earned 250 total points
Comment Utility
The only info I can find on the above is this:

http://www.jsw4.net/info/listserv_archives/mysql/03-wk25/msg00010.html
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sorting efficency of sorting algorithm 30 78
passing a value with stream reader AFTER a ";" 3 41
Vb.Net Menu 1 21
Create XML 5 30
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now