Menu
▾
▴
#1089 lack of connection charset (UTF-8) documentation
Please, see my comment on 887183.
In addition I can add, that the mySql version is 5.0.45 and the following config items are in my config.inc.php file:
$cfg['DefaultLang'] = 'en-utf-8';
$cfg['DefaultConnectionCollation'] = 'utf8_unicode_ci';
$cfg['FilterLanguages'] = '';
$cfg['DefaultCharset'] = 'utf-8';
$cfg['AllowAnywhereRecoding'] = TRUE;
Data enter the database as UTF-8 data perfectly OK when not using phpMyAdmin
Logged In: YES
user_id=210714
Originator: NO
Please set "Group" here to the phpMyAdmin version you are talking about.
Logged In: YES
user_id=1177594
Originator: YES
2.11.7-rc1
Logged In: YES
user_id=210714
Originator: NO
Does the problem happen if you set
$cfg['AllowAnywhereRecoding'] = false;
Also, please give a step-by-step to reproduce the problem.
Logged In: YES
user_id=210714
Originator: NO
Also, does this help?
http://wiki.cihar.com/pma/garbled_data
Logged In: YES
user_id=1177594
Originator: YES
After a thorough analysis it appears to be a MySQL issue and not a phpMyAdmin issue.
Even if you have utf-8 tables and inserts utf-8 data, the data will be utf-8 encoded i.e. a double byte utf-8 character will be utf-8 encoded and become a quadruple byte character.
Example in perl:
use DBI;
use Unicode::String qw(utf8 latin1);
$dbh = DBI->connect( 'dbi:mysql:dbase;mysql_socket=/tmp/mysql.sock', 'xxx', $dbpwd);
$sth = $dbh->prepare(q{ INSERT INTO tbl ( `data`) VALUES (?) });
$sth->execute(latin1( $data)->utf8);
will insert doubly encoded utf-8 data
However inserting this strange command just after connecting to the database will prevent the double encoding.
$sth = $dbh->prepare(q{SET NAMES 'utf8'});
$sth->execute();
I.e. every time a database connection is made the above command must be executed.
If this has been done, phpMyAdmin will be working with utf-8 data.
You do not have to use the SET NAMES 'utf8' command in phpMyAdmin.
Logged In: YES
user_id=1383652
Originator: NO
>I.e. every time a database connection is made the above command must be executed.
you can avoid it if you tell mysqld (in my.cnf/ini) to use utf8 for the client connections.
>You do not have to use the SET NAMES 'utf8' command in phpMyAdmin.
it is used internally ...
Logged In: YES
user_id=1177594
Originator: YES
It would be more helpful to explicitly list the lines needed in my.cnf
e.g.
To change the connection charset permanently to UTF-8, add the following line in the [mysqld] section:
[mysqld]
init-connect='SET NAMES utf8'
from
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
Logged In: YES
user_id=210714
Originator: NO
joergent,
I'm not sure I follow you. Because phpMyAdmin uses "SET NAMES utf8" internally, there is no need to ask users to change my.cnf (at least there is no need to do this to make phpMyAdmin work normally).
Logged In: YES
user_id=326580
Originator: NO
> After a thorough analysis it appears to be a MySQL issue
i do not think so ...
> Even if you have utf-8 tables and inserts utf-8 data, the data will be
> utf-8 encoded i.e. a double byte utf-8 character will be utf-8 encoded and
> become a quadruple byte character.
and you need to tell MySQL that you data is UTF-8 with SET NAMES, by default MySQL uses some latin1
you should read more carefully about connection charset in the MySQL manual
setting connection charset in the my.cnf would force ANY client to use this charset, this could lead to more trouble than it helps
YOU need to tell MySQL WITH 'EVERY CONNECTION what charset your content you send has, this is done with SET NAMES, before UTF8 most (or nearly all) all clinets sent his content as latin1 and this is default for mysql connections, so there was no need to use SET NAMES, but if you send content in other charset than laitn1 you need to tell this the MySQL server before sending data!
and this is what phpMyAdmin does and whyt MySQL awaits
Logged In: YES
user_id=326580
Originator: NO
> After a thorough analysis it appears to be a MySQL issue
i do not think so ...
> Even if you have utf-8 tables and inserts utf-8 data, the data will be
> utf-8 encoded i.e. a double byte utf-8 character will be utf-8 encoded and
> become a quadruple byte character.
and you need to tell MySQL that you data is UTF-8 with SET NAMES, by default MySQL uses some latin1
you should read more carefully about connection charset in the MySQL manual
setting connection charset in the my.cnf would force ANY client to use this charset, this could lead to more trouble than it helps
YOU need to tell MySQL WITH 'EVERY CONNECTION what charset your content you send has, this is done with SET NAMES, before UTF8 most (or nearly all) all clinets sent his content as latin1 and this is default for mysql connections, so there was no need to use SET NAMES, but if you send content in other charset than laitn1 you need to tell this the MySQL server before sending data!
and this is what phpMyAdmin does and whyt MySQL awaits
Logged In: YES
user_id=326580
Originator: NO
http://bugs.mysql.com/37367
Logged In: YES
user_id=1177594
Originator: YES
cybot_tm apparently has not read my comment carefully, but is only repeating it (twice)!
lem9: there is a world outside phpMyAdmin e.g. the applications actually entering data into the tables.
It is important that data entered into the tables are correct otherwise phpMyAdmin cannot display them correctly.
My intention is to help users to avoid the idiosyncrasies of MySQL causing incorrect data to be inserted.
Logged In: YES
user_id=210714
Originator: NO
joergent,
ok but a phpMyAdmin bug tracker is probably not the correct place to help users about this. Maybe a blog entry or better a MySQL bug report?
Logged In: YES
user_id=1177594
Originator: YES
Yes, it is!
I have for a couple of years considered this a phpMyAdmin bug and been waiting for it to be solved as all my other perl and php applications with utf-8 were working fine (allthough with doubly encoded data).
Logged In: YES
user_id=326580
Originator: NO
uh, this is confusing, if you, joergent, do not think this is a bug in phpMyAdmin why do you report it as a bug?
additionally this line: "Data enter the database as UTF-8 data perfectly OK when not using phpMyAdmin" makes me think you think phpMyAdmin is acting wrong or erroneous
additionally you comment on bug #887183
(https://sourceforge.net/support/tracker.php?aid=887183)
makes me think even more that you think this is a bug in phpMyAdmin
so, please, give me one more chance and explain what do you think is wrong and what do you think should get fixed and where!
the "strange command" (SET NAMES) makes not phpMyAdmin working with UTF8 data, it makes that UTF8 data gets correctly into the database, if not issue SEt NAMES UTF8 MySQL 'thinks' the data you sent is latin, so it will convert your UTF8 again to UTF8 (= double encoding), and insert this double encoded data into the DB, next time you read data from the DB (again with proper SET NAMES UTF8) MySQL will 'think' you want latin1 and convert the double encoded UTF8 data back and send it to you
Logged In: YES
user_id=326580
Originator: NO
the best way to check the data in the DB is to use the mysql client on database server itself
Logged In: YES
user_id=1177594
Originator: YES
cybot_tm: Do yourself a favour and read this thread from the beginning, including bug 887183, before commenting more.
The best way of checking the data is by a hex dump of the database!
Which I did and thus discovered this problem and getting punked because I just specify a simple solution for what I experienced being a phpMyAdmin bug, but turned out not to be.
Remember, whoever using phpMyAdmin is also using MySQL.
Logged In: YES
user_id=326580
Originator: NO
This is not a bug, whether in phpMyAdmin nor in MySQL.
The phpMyAdmin bug tracker is not the right place.
Changing my.cnf has nothing to do with phpMyAdmin.
There could be a couple of good reasons why init-connect='SET NAMES utf8' is not used in my.cnf. (not every client will send data in UTF8)
I have read this topic, up and down.
The problem is the lack of information about connection charset by many developers and possible DBAs.
and please don't harass on me, i am here to help!
Logged In: YES
user_id=1383652
Originator: NO
Although it is not a pma bug nor a mysql bug many people have difficulties to understand the characterset support from mysql. That's why i created that "garbled_data" page (later extended by cybot_tm). Please don't blame the pma developers for doing it the right way. Most people who develop (mysql/web-)applications just don't read the mysql manual carefully enough...
Logged In: YES
user_id=1177594
Originator: YES
removing the UTF-8 from the summary is not helpful to people, when they search for problems on converting to utf-8