Solved

Character set Mismatch in VB.NET and mysql on linux

Posted on 2007-12-06
10
616 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
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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

25 Experts available now in Live!

Get 1:1 Help Now