Skip navigation links
9.7 MySQL Server Time Zone Support »
Section Navigation      [Toggle]

9.6. Problems With Character Sets

If you try to use a character set that is not compiled into your binary, you might run into the following problems:

  • Your program uses an incorrect path to determine where the character sets are stored (which is typically the share/mysql/charsets or share/charsets directory under the MySQL installation directory). This can be fixed by using the --character-sets-dir option when you run the program in question. For example, to specify a directory to be used by MySQL client programs, list it in the [client] group of your option file. The examples given here show what the setting might look like for Unix or Windows, respectively: 

    [client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets

[client]
character-sets-dir="C:/Program Files/MySQL/MySQL Server 5.1/share/charsets"

  • The character set is a complex character set that cannot be loaded dynamically. In this case, you must recompile the program with support for the character set.

    For Unicode character sets, you can define collations without recompiling by using LDML notation. See Section 9.5.4, “Adding a UCA Collation to a Unicode Character Set”.

  • The character set is a dynamic character set, but you do not have a configuration file for it. In this case, you should install the configuration file for the character set from a new MySQL distribution.

  • If your character set index file does not contain the name for the character set, your program displays an error message. The file is named Index.xml and the message is: 

    Character set 'charset_name' is not a compiled character set and is not
specified in the '/usr/share/mysql/charsets/Index.xml' file

    To solve this problem, you should either get a new index file or manually add the name of any missing character sets to the current file.

For MyISAM tables, you can check the character set name and number for a table with myisamchk -dvv tbl_name.


User Comments

Posted by Eugene on February 25 2004 11:24pm[Delete] [Edit]

Recently I was working on c++ prog (VisualStudio 7.0) that should be ran as a service on Win2kSP3 station stuffing some data to a remote mysql-3.23.53 server via ODBC 3.51. It worked fine in user mode, but if i tried to run it as a service it instantly died with an error:

File 'C:\mysql\\share\charsets\?.conf' not found (Errcode: 2)
Character set '#14' is not a compiled character set and is not specified in the 'C:\mysql\\share\charsets\Index' file.

Web search brought no success, as most people had this trouble with mysql server service, not ODBC-client. It was a great luck indeed when i ran into a russian USENET thread with a very strange but effective solution:
I just copied all contents of "/usr/local/mysql/share/mysql/charsets" directory from my remote FreeBSD 4.4 box to "C:\mysql\share\charsets\" on my Win2kSP3 station. And it worked!
I would really appreciate any explanation from anybody with deeper understanding of MySQL and MySQL ODBC 3.51 on such strange behaviour.

Posted by Alex Smith on October 22 2004 10:28pm[Delete] [Edit]

I also got all those pretty stuff with cp1251:
1) created empty database
2) restored data into it from sql file
3) run myisamchk - ALL tables with varchar (i.e. russian strings) are reported having errors! in clean database!!!
I was really confused... :(((
Then I found some good advice somewhere - I wrote "character-sets-dir=" also into [client] and [myisamchk] sections of my.cnf (before it was only in [mysqld]) and "default-character-set=cp1251" in [client] (dont know if it's really needed) - run myisamchk with "--set-character-set=cp1251" on all tables - MIRACLE! all damn things were fixed! ...
still I dont understand why did myisamchk not understand command line key "--character-sets-dir" and only modifying of my.cnf helped. I wonder...

Posted by Alexandre Koriakine on November 11 2004 10:46am[Delete] [Edit]

File 'C:\mysql\\share\charsets\?.conf' not found (Errcode: 22)
Character set '#51' is not a compiled character set and is not specified in the 'C:\mysql\\share\charsets\Index' file

I had the same error in WINXP. Even I had copied from both Linux and BSD installations - this didn't help me. I have even installation in c:\ptorgam files\mysql.

My Solution: I just created c:\mysql\share\charsets and copied from original folder all files, and added into "Index" file new row (with encoding I use):
cp1251 51

Now I don't get any errors. In all my linux/bsd machines I ve never seen this error.

Posted by K. Swartz on January 1 2005 3:39am[Delete] [Edit]

The C:\mysql directory appears to only be hardcoded when trying to look for information on character sets that are not compiled with the installation. Given that, here is a better way to workaround the problem:

First, go into mysql for your database and do "show collation;" and look for character sets with "Yes" under the Compiled column. Then, edit your my.cnf file and set collation-server (under [mysqld]) to one of these character sets -- preferably one that is closest to what your data uses. Bounce the mysqld server, and the errors should go away.

Posted by Andrew Smyk on January 16 2005 10:16am[Delete] [Edit]

I had this problem when my MySQL was using utf-8.. File utf-8.xml is not present where he has to -> c:\mysql\share\charsets.. I changed default coding to cp1250 and now it is working ..

Posted by Alexander Amelkin on January 21 2005 4:01pm[Delete] [Edit]

The "?.conf''" problem may also be caused by an outdated DBD::mysql perl package.

If you used an old version of MySQL (say, 3.23) and then upgraded your system to MySQL 4.1, you may encounter such a problem with your perl scripts.

Just run cpan and issue an 'install DBD::mysql' command.
After you update your DBD::mysql package the charset problem should be gone.

At least it worked for me.

Posted by Alexander Splinter on February 10 2005 12:41pm[Delete] [Edit]

Hi guys!
Here You are OVERALL solution for 4.1 that makes Your php (for example) code "crosshosted" i.e. it will not depend on MySQL server charset options (it's very useful when host admin is not available :) ) :

After connecting (@mysql_connect) and selecting (@mysql_select_db) a database You should make the following:
$set = @mysql_query ('SET NAMES CP1251');
$set = @mysql_query ('SET COLLATION_CONNECTION=CP1251_GENERAL_CI');

may be just first SET is enough even.

Remove ANY stuff concerning charsets from my.ini (my.cnf) just forget it and set connection properties as above. Some stupid strings from errors.log will gone as bonus :)

Posted by Brad Rathbun on March 12 2005 5:57pm[Delete] [Edit]

The comments by Gotz helped me out a lot. I thought I would also add the following comments to expand on his line of thought:

This is a quick hack to generate the necessary Index file:
#!/bin/bash
mv Index.xml Index.xml.tmp
i="1"
for filename in $( ls *.xml | sort )
do
echo "$filename $i" >> Index
i=`expr $i + 1`
done
mv Index.xml.tmp Index.xml

I would also like to mention that I got the errors when I configured using "--with-charset=utf8". When I removed that, problem went away. The docs say that latin1 is the default, which is good enough for me. Easy fix is to avoid specifying the charset if you can live with the defaults.

Posted by B!rd Feathery on March 17 2005 7:04pm[Delete] [Edit]

hello. i am using ActivePerl 5.8.6.811 and MySQL 4.1.9 (in "D:\web\mysql") on Win2kSP4.
i got error
"File 'C:\mysql\\share\charsets\?.conf' not found (Errcode: 2)Character set '#51' is not a compiled character set and is not specified in the 'C:\mysql\\share\charsets\Index' file"
connecting using DBI:mysql.

copying to C:\mysql\share... did not solved this problem.
So i have just installed DBI:mysqlPP and use "DBI->connect('DBI:mysqlPP...". It Works!

Posted by bossk on November 10 2005 4:28pm[Delete] [Edit]

I had the same problem.
I did a check with myisamchk -dvv and found out that one of the tables had a problem with the charset. The type was not in the /usr/local/mysql/charset/Index. So I edited this file and put the characterset in there. The next I did was to set the right charset myisamchk --set-character-set=<your_character>.

Another thing you could do is find out the table with the character problem and backup its content and create a new one and put the content back.

Posted by Andrei Bastun on November 14 2005 10:20am[Delete] [Edit]

I had problem with inserting cyrillic data into mysql table. I added
default-character-set=cp1251
character_set_client=cp1251

in my.ini file, and now it works fine.

Add your own comment.