Solved

Character set Mismatch in VB.NET and mysql on linux

Posted on 2007-12-06
10
634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 3

Expert Comment

by:JeffvClayton
ID: 20426307
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
ID: 20426511
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
ID: 20426707
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Aloha_Technology
ID: 20439832
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
 
LVL 3

Expert Comment

by:JeffvClayton
ID: 20440312
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
ID: 20447753
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
ID: 20447798


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
ID: 20448236
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
ID: 20632929
Forced accept.

Computer101
EE Admin
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

690 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