Problem with pma charset configuration

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Problem with pma charset configuration

Postby trejder » 13. April 2008 07:02

Hello there,

Hope, someone can help me, because I'm going to get mad! :)

Either there is something wrong with phpMyAdmin configuration under XAMPP Lite for Windows (because I can't reproduce this problem in any on-line pma, I use, only in this one) or I don't understand something.

I have a HORRIBLE problems with configuring pma (and MySQL) charsets to other than default, that comes with XAMPP Lite installation.

I've opened config.inc.php in phpMyAdmin dir and added these two:

Code: Select all
$cfg['DefaultConnectionCollation'] = 'latin2_general_ci';
$cfg['DefaultCharset'] = 'iso-8859-2';


I've opened my.cnf i mysql/bin dir and added these two:

Code: Select all
character-set-server = latin2
collation-server = latin2_general_ci


And even so, after loggin into pma I STILL see this anying (for me) text: "MySQL charset: UTF-8 Unicode (utf8)".

I have no idea how to change it into ISO-Latin-2 (ISO 8859-2), which I use and how to grant that all my mysql transactions will be performed with correct (for me) character coding.

I have NO server- or user-specific configuration neither for mysql nor for pmy. Everywhere I use global settings but either I don't understand them or something is wrong. I really would like to get rid of that utf-8 encoding because all my on-line server uses iso-latin-2 encoding and each time I migrate from local to on-line version of my pages I have to update my databases - i.e re-encode their characters.

And speaking about languages and charsets. Can someone tell me what is wrong with this line of pma configuration:

Code: Select all
$cfg['Lang'] = 'polish-iso-8859-1';


or even

Code: Select all
$cfg['Lang'] = 'polish-utf-8';


Because if I include it in my pma config I always get "Language not found" error message when logging into pma. This is another xampp-lite-pma related problem for me because I have no such a problems when using pma on Apache server configured by my on-line server administrator. OF course all files that I try to point to (i.e. polish-iso-8859-1 and polish-utf-8) are EXISTING in my lang folder. It is only that I can't change language (by configuration! - because through combo box in pma works well) to any other than default one.

Best regards and thanks for all help!
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 13. April 2008 08:04

And even so, after loggin into pma I STILL see this anying (for me) text: "MySQL charset: UTF-8 Unicode (utf8)".

That's IMHO an old bug in phpMyAdmin. phpMyAdmin shows you the value of "character_set_system" and not "character_set_server".
--> Go to "Show MySQL system variables" and verify your settings.

Code: Select all
$cfg['DefaultCharset'] = 'iso-8859-2';

Don't forgett to enable "AllowAnywhereRecoding".

Code: Select all
$cfg['Lang'] = 'polish-iso-8859-1';

Of course, with "-1" this can't work. But I think "pl-iso-8859-2" is the best choice.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 13. April 2008 09:52

Wiedmann wrote:That's IMHO an old bug in phpMyAdmin. phpMyAdmin shows you the value of "character_set_system" and not "character_set_server". --> Go to "Show MySQL system variables" and verify your settings.

Oh, I'm uncertain, if you are right? And if it is only a bug. Look at my current system variables about charsets:

Image

I also made export from database and found out that generated .sql file is coded in utf-8. In my opinion it tells strightly that pma still uses utf-8 enconding. Someting like it would ignore my settings?

What is also strange for me is that pma handles Polish national letters correctly, but my website that aquires the same data from MySQL - don't. After exporting a example table (as mentioned above) I recoded it into iso-latin-2 and saved in another file. Then I imported both files into pma - first encoded in iso, then in utf. In both cases pma showed national letters correctly and in the same time, in both cases my webpage showed them coded incorrectly.

All my PHP scripts are codded in iso-latin-2, which is official standard for Polish national letter. And my only guess is that no matter how imported file is coded (utf-8 or iso) pma still handles it (and puts to database) in utf-8. That is why (in my opionion) in both cases my webpage (coded in iso) was unable to display national characters correctly.

But even if my guessing is correct, I still have no idea what to do with it?

Wiedmann wrote:Don't forgett to enable "AllowAnywhereRecoding".

R U sure I have to do this? In documentation (config.default.php in libraries in pma folder) I found out (around $cfg['DefaultCharset']) this:

Default character set to use for recoding of MySQL queries, does not take any effect when character sets recoding is switched off by $cfg['AllowAnywhereRecoding'] or in language file

Which I understand that turning $cfg['AllowAnywhereRecoding'] on causes setting set in $cfg['DefaultCharset'] to be ignored. But of course I run after your suggestion and finally set $cfg['AllowAnywhereRecoding'] to True, only to find out that it brought no effect. Or effect not desirabled by me - national letters are still incorrectly coded in database / displayed on website.

Wiedmann wrote:Of course, with "-1" this can't work. But I think "pl-iso-8859-2" is the best choice.

My typo. But in my case it does not change anything. See for yourself:

Image

File exists, pma refuses to load it claiming that language does not exists. But this is lesser problem for me. I can always select language when logging in and pma remembers my selection. But above described problem with encodding national characters is a real nightmare for me. :/

Thanks for any attepmts of helping me!
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby trejder » 13. April 2008 13:08

Hello again,

After all, simple:

Code: Select all
mysql_query('SET NAMES latin2');

placed at the end of function used for connecting to database has solved the entaire problem.

I think that my pma and mysql still works with transactions and queries encoded in utf-8 but after using SET NAMES all data aquired from database and displayed on my website has now correct encoding. That solves my problem and I don't care any more how to change pma configuration. So, I think - EoT?

Best regards,
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 13. April 2008 17:48

Look at my current system variables about charsets:

Exactly what you want: The necessary global values are "latin2" and thus this is the default now.


Which I understand that turning $cfg['AllowAnywhereRecoding'] on causes setting set in $cfg['DefaultCharset'] to be ignored.

Exacly the opposite. "DefaultCharset" is only used, if you also enable "AllowAnywhereRecoding".

BTW:
I also made export from database and found out that generated .sql file is coded in utf-8.

With "AllowAnywhereRecoding" you can choose the charset in the export window. Of course: Only if you download the *.sql file. A copy 'n paste from browser is allways utf-8.


File exists, pma refuses to load it claiming that language does not exists.

Wiedmann wrote:But I think "pl-iso-8859-2" is the best choice.


What is also strange for me is that pma handles Polish national letters correctly, but my website ... don't.

Also clear: PHP always use latin1, if you don't change it with "SET NAME" or better mysql(i)_set_charset.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 14. April 2008 09:29

Wiedmann wrote:Exactly what you want: The necessary global values are "latin2" and thus this is the default now.

Your post clearfilles everything and many thanks for that. It seems that my problems were not because wrong configuration, but only from misunderstanding some things. With SET NAMES problem has been solved and I think, that we can end this topic. Thanks again!

Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby trejder » 14. April 2008 11:20

trejder wrote:With SET NAMES problem has been solved and I think, that we can end this topic.

Well, it turned out that I was too optimistic and after a few hours after writing previous post, phpMyAdmin (I use 2.11.4, which comes with latest edition of xampp light) surprised me again.

I'm doing multilingual webpage right know. Most of the text must be available in English, Polish and French. With English there are no problems as this language have no specific letter. Polish national characters problem is solved with SET NAMES. Now, I have a big problem with French.

The general question is, what encoding and collation I have to use to store French text with all French national letters properly stored? Right know I'm getting question marks (?) in place of some specific letters.

I have tested it with latin2_general_ci and latin1_general_ci collation. In first example text was stored with no problems but some specific French national letters were lost and changed to ? signs. When I used latin1_general_ci collation I was even unable to store (add, update) text in a row, because each time I tried, I got this error message: "Warning: #1366 Incorrect string value: '\xB3a. Sp...' for column 'fr' at row 1".

I have tested also cp1250 (I got HTML text in this encoding and in FF or IE they are displayed correctly), utf8_general_ci, utf8_bin utf8_unicode_ci, ucs2_unicode_ci, ucs2_bin and ucs2_general_ci. All with no effects. If even unicode set for collation does not work, then what will be? :/

After testing that many collations I run myself into thinking that maybe not collation is a problem, but pma itself? Maybe some French characters are changed to ? signs when transmited from pma form to database?

What is extremally strange for me (and surprises me the most) is that when I used latin2_general_ci (and text was stored without warnings, but some letter were lost) then on result page pma showed stored text, but in one part of a page specific letters were displayed correctly and in another part... not. Look at below image:

Image

I was shocked! WTF? Page generated by pma has TWO different encodings? In the begining the encoding is correct and specific French letters are displayed correctly and near the bottom of a page encoding changes and the same letters are displayed incorrectly?

With many thanks for any kind of help and with best regards!
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 14. April 2008 12:45

Maybe some French characters are changed to ? signs when transmited from pma form to database?

This can happen, if your phpMyAdmin is setup to use "latin2" as defaultlang (and/or connection collataion) and you want enter chars which are not availible in "latin2".

I'm doing multilingual webpage right know.

Why don't you use utf-8 for all languages?
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 14. April 2008 12:50

Wiedmann wrote:Why don't you use utf-8 for all languages?

Oh, I did! If you look up, you'll see that I tested also setting utf8_general_ci, utf8_bin and utf8_unicode_ci as collation for table, where I store my French text. And it resulted with the same - nothing! After adding (or updating) text do database through pma, the resulting text has some characters converted to ? signs.

On some Polish page I found out a text suggesting that it is a bug inside pma, which causes it to display ? signs and data actually stored in database is stored with proper character, if utf8 collation is selected and UTF8 is used in transactions with MySQL server. I'm going to check out now, if this suggestion is true?

Regards,
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 14. April 2008 12:59

Oh, I did!

I'm talking about the charset PMA is using, and not the table in MySQL.

Just look at the MySQL variables charset_connection/client/results. If this is e.g. "latin2" and in the db is a char from another charset, the char can't be transfered (from the db) or displayed correctly.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby trejder » 14. April 2008 13:03

Wiedmann wrote:I'm talking about the charset PMA is using, and not the table in MySQL. Just look at the MySQL variables charset_connection/client/results. If this is e.g. "latin2" and in the db is a char from another charset, the char can't be transfered (from the db) or displayed correctly.


OK, now I understand. I give it a try. Thanks!
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby sari42 » 14. April 2008 14:02

make *everything* utf8 also on your web pages and use "set names UTF-8" after each connect.

maybe http://wiki.cihar.com/pma/garbled_data helps...
sari42
 
Posts: 800
Joined: 27. November 2005 18:28

Postby trejder » 14. April 2008 15:38

sari42 wrote:make *everything* utf8 also on your web pages and use "set names UTF-8" after each connect.

Thanks! It finally solved my problems! :] It now works all fine on local server - with xampp lite. Now, I only have to figure out / consult with admin, how to change configuration of mysql ran at production server, to make it work also. Currently it does not. But thanks a lot for everything!

Regards
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby trejder » 15. April 2008 12:27

Hi, again,

Unfortunatelly - my production server does not work well in the way, you described. It is because it is badly configured and very old (MySQL 4.1.22). I wasted whole day on arguing with it's admin and finally got nothing.

I'm unable to change everything to utf8. System variables collation_connection and character_set_connection remains set to latin2. I can't change them permanently because I work remotely and have no access to MySQL configuration files.

I can't change these two variables in code, because when connecting to MySQL, server ignorses following two:

Code: Select all
$dbh = mysql_query('SET collation_connection = utf8_general_ci');
$dbh = mysql_query('SET character_set_connection = utf8');

Ignores - I mean that function returns True, but send data are still wrongly encoded.

My full function for connecting to database looks this way:

Code: Select all
function connect_to_database()
{
     $dbh = mysql_connect("localhost", "[LOGIN]", "[PASS]") or die('MySQL Error: '.mysql_error());
     $dbh = mysql_select_db("szymbud_www") or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET NAMES utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET CHARACTER SET utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET collation_connection = utf8_general_ci') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET character_set_connection = utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET character_set_client = utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET character_set_database = utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET character_set_results = utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET character_set_server = utf8') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET collation_database = utf8_general_ci') or die('MySQL Error: '.mysql_error());
     $dbh = mysql_query('SET collation_server = utf8_general_ci') or die('Błąd MySQL: '.mysql_error());
}

I know that there is a lot of garbage in it and many functions repeats other ones. But I wanted to make sure that everything is set to utf8. All the functions passes with True and no error, but I still get garbage out of database.

It works on localhost, it stops working on remote server.

So, suppose I would have to encode French letters without using utf8? What encoding should I use in both MySQL and XHTML (in place of latin2 and iso 8859-2, which I use when coding Polish national letters)?

What encoding French web developers used years ago to encode their websites in HTML / XHTML and their MySQL databases before utf8 was ever developed?

Regards,
Tom
trejder
 
Posts: 42
Joined: 13. April 2008 06:45

Postby Wiedmann » 15. April 2008 12:49

I'm unable to change everything to utf8.

You must not change "everything".

- create your table with
Code: Select all
CREATE TABLE tbl_name (column_list) CHARACTER SET utf8


And if you store or retrieve data to/from the database with PHP, use this as first statement/query after the connection:
Code: Select all
SET NAMES 'utf8';


That's all.

but send data are still wrongly encoded.

Maybe there is allready wrong data in the server. And so this data can't retrieve correctly from the server (anymore).


What encoding French web developers used years ago to encode their websites in HTML / XHTML and their MySQL databases before utf8 was ever developed?

Well, but the easiest way is to use allways UTF-8, because you have now only 1 charset for all things and not many. And you can use every language you want with UTF-8.

BTW:
French have ISO-8859-1 or ISO-8859-15 (like German).
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 51 guests