Howto add US ASCII charset to Mysql

[system]

Hello,

For one of our database we need to use the characters set US ASCII and the collate ASCII-BIN.

The problem is we don^t have this option in the interclassment list. It begins at BIG5.

When we look in phpmyadmin the list of Characters sets for this MySQL server when can see
ascii (US ASCII)
ascii_bin Europe de l’ouest (multilingue), Binaire
ascii_general_ci Europe de l’ouest (multilingue), insensible ? la casse

but it is grised

When we look at /usr/share/mysql/charsets/ there is an ascii.xml file and in Index.xml asci is defined.

So why US Ascii is not listed in all the charsets available in phpmyadmin ? how could we change our database to use US ASCII (ASCII-BIN) ?

Thanks a ton for your help

Pascal

[system]

Lol nobody has an idea

It’s so strange, I thought that US ASCII char sets was set up by default ?

Well if I found I’ll let you know.

Sometimes we have to use this because some scripts use some a specific collate table (in our case ASCII-BIN)

Pascal

[system]

I’m just a little lost.

Every where in the Mysql doc they say that in the charsets dir you should have :
*.conf files --> define the charsets and collate
Index file --> define all charsets used by this server

In my charsets dir I DO NOT HAVE .conf files. ALL FILES are *.XML !

I looked at an other box with an mysql 3.x and in his charsets dir it has *.conf files and one Index file.

So maybe with 4.1.12 there is a difference, but it is not documented.

Do you know if there is a script to transform the xml files to conf files ?
Is it normal to have .xml files rather than .conf files ?
Does somebody who also have a mysqld 4.1.12 could tell me if in /usr/share/mysql/charsets/ there is *.conf file or *.xml files ?

It’s not really clear

Thanks

Pascal

[system]

@pascal

I’m just a little lost.

Every where in the Mysql doc they say that in the charsets dir you should have :
*.conf files –> define the charsets and collate
Index file –> define all charsets used by this server

In my charsets dir I DO NOT HAVE .conf files. ALL FILES are *.XML !

I looked at an other box with an mysql 3.x and in his charsets dir it has *.conf files and one Index file.

So maybe with 4.1.12 there is a difference, but it is not documented.

Do you know if there is a script to transform the xml files to conf files ?
Is it normal to have .xml files rather than .conf files ?
Does somebody who also have a mysqld 4.1.12 could tell me if in /usr/share/mysql/charsets/ there is *.conf file or *.xml files ?

It’s not really clear

Thanks

Pascal

Mine are all xml as well, Pascal

[root@iworx ~]# rpm -qa | grep mysql
php-mysql-4.3.11-100.rhe4x.iworx
mysqlclient10-3.23.58-4.RHEL4.1
mysql-shared-compat-4.0.21-100.iworx
mysql-4.1.12-3.RHEL4.1
mysql-iworx-4.0.21-3.rhe4x.iworx
mysql-server-4.1.12-3.RHEL4.1
mysql-bench-4.1.12-3.RHEL4.1
freeradius-mysql-1.0.1-3.RHEL4.3
mysql-devel-4.1.12-3.RHEL4.1
libdbi-dbd-mysql-0.6.5-10.RHEL4.1
mysqlclient10-devel-3.23.58-4.RHEL4.1
[root@iworx ~]# ls /usr/share/mysql/charsets/
armscii8.xml  cp1257.xml  geostd8.xml  keybcs2.xml  latin5.xml    swe7.xml
ascii.xml     cp850.xml   greek.xml    koi8r.xml    latin7.xml
cp1250.xml    cp852.xml   hebrew.xml   koi8u.xml    macce.xml
cp1251.xml    cp866.xml   hp8.xml      latin1.xml   macroman.xml
cp1256.xml    dec8.xml    Index.xml    latin2.xml   README
[root@iworx ~]#

[system]

Do you think it could simply be phpmyadmin that doesn’t show the good list of interclassment ?

Indeed I do not have the same var sets between phpmyadmin and mysqladmin variables.
for example :
from mysqladmin beetween session and global

character set client utf8 latin1
character set connection latin1 latin1
character set database latin1 latin1
character set results utf8 latin1
character set server latin1 latin1
character set system utf8 utf8
character sets dir /usr/share/mysql/charsets/ /usr/share/mysql/charsets/
collation connection latin1_swedish_ci latin1_swedish_ci
collation database latin1_swedish_ci latin1_swedish_ci
collation server latin1_swedish_ci latin1_swedish_ci

The character set client is set to utf8 for this session rather than latin1 ?
Also in phpmyadmin / Character Sets and Collations

ascii (US ASCII)
ascii_bin West European (multilingual), Binary
ascii_general_ci West European (multilingual), case-insensitive

is greyed.

Somebody has Ascii-bin available ?

Do you know why it uses utf-8 ?

Apparently it should be ok to have .xml in charsets dir with mysql 4.1.12, so… It might be a phpmyadmin pbm.

Pascal

[system]

Mine are all xml as well, Pascal

cool.

And do you have ascii-bin available as collation option in phpmyadmin ?
Home page of phpmyadmin, below MySQL, the listbox after Create new database ?

Below phpmyadmin (still home page of phpmyadmin) What are your
MySQL charset:
MySQL connection collation :

Thanks tim for your infos

Pascal

ps

[root@obiwan charsets]# rpm -qa | grep mysql
mysql-server-4.1.12-3.RHEL4.1
mysql-4.1.12-3.RHEL4.1
mysql-iworx-4.0.21-4.rhe4x.iworx
mysql-devel-4.1.12-3.RHEL4.1
mysql-4.1.12-3.RHEL4.1
php-mysql-4.3.11-100.rhe4x.iworx

I think your mysqlclient10 is your usage’s specific

[system]

In the current doc’s phpMyadmin version installed on my server I found this

$cfg[‘AvailableCharsets’] array
Available character sets for MySQL conversion. You can add your own (any of supported by recode/iconv) or remove these which you don’t use. Character sets will be shown in same order as here listed, so if you frequently use some of these move them to the top.

Maybe it is because in /usr/local/interworx/lib/phpMyAdmin/config.inc.php there is not the ascii charset. I’ll try this

Pascal

[system]

not seems to be this. But not sure it isn’t

The problem become more important as one of our customers told he use Wordpress and he has to choose ascii-bin when he creates the wordpress database. He also told us, one year ago we had this option (??? we were on an other interworx server. is there a link ?)

I don’t want to spam this forum, so I’ll search and if some of you are interesting by this, I’ll let you know the issue (If I found it)

Take care

pascal

[system]

haha sorry…

I’m now pretty sure it is not a mysql server problem but rather a phpmyadmin problem.

Indeed if I do SHOW xx, in mysql, I have this

mysql> SHOW CHARACTER SET ;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | ISO 8859-1 West European    | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
+----------+-----------------------------+---------------------+--------+

There is ASCII char set

mysql> Show COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin             | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci      | dec8     |   3 | Yes     |          |       0 |
| dec8_bin             | dec8     |  69 |         |          |       0 |
| cp850_general_ci     | cp850    |   4 | Yes     |          |       0 |
| cp850_bin            | cp850    |  80 |         |          |       0 |
| hp8_english_ci       | hp8      |   6 | Yes     |          |       0 |
| hp8_bin              | hp8      |  72 |         |          |       0 |
| koi8r_general_ci     | koi8r    |   7 | Yes     |          |       0 |
| koi8r_bin            | koi8r    |  74 |         |          |       0 |
| latin1_german1_ci    | latin1   |   5 |         |          |       0 |
| latin1_swedish_ci    | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci     | latin1   |  15 |         |          |       0 |
| latin1_german2_ci    | latin1   |  31 |         | Yes      |       2 |
| latin1_bin           | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci    | latin1   |  48 |         |          |       0 |
| latin1_general_cs    | latin1   |  49 |         |          |       0 |
| latin1_spanish_ci    | latin1   |  94 |         |          |       0 |
| latin2_czech_cs      | latin2   |   2 |         | Yes      |       4 |
| latin2_general_ci    | latin2   |   9 | Yes     |          |       0 |
| latin2_hungarian_ci  | latin2   |  21 |         |          |       0 |
| latin2_croatian_ci   | latin2   |  27 |         |          |       0 |
| latin2_bin           | latin2   |  77 |         |          |       0 |
| swe7_swedish_ci      | swe7     |  10 | Yes     |          |       0 |
| swe7_bin             | swe7     |  82 |         |          |       0 |
| ascii_general_ci     | ascii    |  11 | Yes     |          |       0 |
| ascii_bin            | ascii    |  65 |         |          |       0 |
| ujis_japanese_ci     | ujis     |  12 | Yes     | Yes      |       1 |
| ujis_bin             | ujis     |  91 |         | Yes      |       1 |
| sjis_japanese_ci     | sjis     |  13 | Yes     | Yes      |       1 |
| sjis_bin             | sjis     |  88 |         | Yes      |       1 |
| hebrew_general_ci    | hebrew   |  16 | Yes     |          |       0 |
| hebrew_bin           | hebrew   |  71 |         |          |       0 |
| tis620_thai_ci       | tis620   |  18 | Yes     | Yes      |       4 |
| tis620_bin           | tis620   |  89 |         | Yes      |       1 |
| euckr_korean_ci      | euckr    |  19 | Yes     | Yes      |       1 |
| euckr_bin            | euckr    |  85 |         | Yes      |       1 |
| koi8u_general_ci     | koi8u    |  22 | Yes     |          |       0 |
| koi8u_bin            | koi8u    |  75 |         |          |       0 |
| gb2312_chinese_ci    | gb2312   |  24 | Yes     | Yes      |       1 |
| gb2312_bin           | gb2312   |  86 |         | Yes      |       1 |
| greek_general_ci     | greek    |  25 | Yes     |          |       0 |
| greek_bin            | greek    |  70 |         |          |       0 |
| cp1250_general_ci    | cp1250   |  26 | Yes     |          |       0 |
| cp1250_czech_cs      | cp1250   |  34 |         | Yes      |       2 |
| cp1250_croatian_ci   | cp1250   |  44 |         |          |       0 |
| cp1250_bin           | cp1250   |  66 |         |          |       0 |
| gbk_chinese_ci       | gbk      |  28 | Yes     | Yes      |       1 |
| gbk_bin              | gbk      |  87 |         | Yes      |       1 |
| latin5_turkish_ci    | latin5   |  30 | Yes     |          |       0 |
| latin5_bin           | latin5   |  78 |         |          |       0 |
| armscii8_general_ci  | armscii8 |  32 | Yes     |          |       0 |
| armscii8_bin         | armscii8 |  64 |         |          |       0 |
| utf8_general_ci      | utf8     |  33 | Yes     | Yes      |       1 |
...
| ucs2_general_ci      | ucs2     |  35 | Yes     | Yes      |       1 |
| ucs2_bin             | ucs2     |  90 |         | Yes      |       1 |
| ucs2_unicode_ci      | ucs2     | 128 |         | Yes      |       8 |
| ucs2_icelandic_ci    | ucs2     | 129 |         | Yes      |       8 |
...
| cp866_general_ci     | cp866    |  36 | Yes     |          |       0 |
| cp866_bin            | cp866    |  68 |         |          |       0 |
| keybcs2_general_ci   | keybcs2  |  37 | Yes     |          |       0 |
| keybcs2_bin          | keybcs2  |  73 |         |          |       0 |
| macce_general_ci     | macce    |  38 | Yes     |          |       0 |
| macce_bin            | macce    |  43 |         |          |       0 |
| macroman_general_ci  | macroman |  39 | Yes     |          |       0 |
| macroman_bin         | macroman |  53 |         |          |       0 |
| cp852_general_ci     | cp852    |  40 | Yes     |          |       0 |
| cp852_bin            | cp852    |  81 |         |          |       0 |
| latin7_estonian_cs   | latin7   |  20 |         |          |       0 |
| latin7_general_ci    | latin7   |  41 | Yes     |          |       0 |
| latin7_general_cs    | latin7   |  42 |         |          |       0 |
| latin7_bin           | latin7   |  79 |         |          |       0 |
| cp1251_bulgarian_ci  | cp1251   |  14 |         |          |       0 |
| cp1251_ukrainian_ci  | cp1251   |  23 |         |          |       0 |
| cp1251_bin           | cp1251   |  50 |         |          |       0 |
| cp1251_general_ci    | cp1251   |  51 | Yes     |          |       0 |
| cp1251_general_cs    | cp1251   |  52 |         |          |       0 |
| cp1256_general_ci    | cp1256   |  57 | Yes     |          |       0 |
| cp1256_bin           | cp1256   |  67 |         |          |       0 |
| cp1257_lithuanian_ci | cp1257   |  29 |         |          |       0 |
| cp1257_bin           | cp1257   |  58 |         |          |       0 |
| cp1257_general_ci    | cp1257   |  59 | Yes     |          |       0 |
| binary               | binary   |  63 | Yes     | Yes      |       1 |
| geostd8_general_ci   | geostd8  |  92 | Yes     |          |       0 |
| geostd8_bin          | geostd8  |  93 |         |          |       0 |
| cp932_japanese_ci    | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin            | cp932    |  96 |         | Yes      |       1 |
+----------------------+----------+-----+---------+----------+---------+

There is Ascii and Ascii-bin

So why phpmyadmin don’t let me choose it, well I don’t know

Pascal

[system]

For updates…

If we do
ALTER DATABASE xxx
DEFAULT CHARSET ascii
DEFAULT COLLATE ascii_bin

Then the tables create under this database have ascii and ascii_bin. But in phpmyadmin it isn’t possible to choose these options (not available).

Basicly, mysql server has ascii’s char sets and collation but not phpmyadmin.

I’ve found nothing about this on the net.

Pascal