Support Requests item #1221046, was opened at 06/15/05 02:33
Message generated for change (Comment added) made by sf-robot
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=377409&aid=1221046&group_id=23067
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
Priority: 5
Submitted By: k-dev4edv (k-dev4edv)
Assigned to: Michal Čihař (nijel)
Summary: charset utf-8 display problem with mysql 4.1.11
Initial Comment:
I have developed a website with multi language features
and I am using Apache 2, PHP 5.0.4 and MySQL
4.1.11-Debian_3-log.
All data in the database is utf-8 and is displayed
correctly on my website.
When I am trying to edit some text in the database with
phpMyAdmin 2.6.3-rc1, all utf-8 data is converted twice
into utf-8 and all special characters are shown very
strange. When I try to insert new text with phpMyAdmin
or modify old one and press the OK button, to save
changes, the text is stored in latin1 format in the
database and is displayed as ?-signs on my website,
which is utf-8. In PMA the new data is displayed
correctly in the browser and is encoded in utf-8, hmm ?!
My database is utf-8 and all collations are set to
utf8_general_ci.
I think the problem is that PMA is connecting to the
database using utf-8 and my website (PHP:
mysql_client_encoding) is using latin1 for the connection.
For example, when I enter the sql command SET CHARACTER
SET "latin1"; SELECT * FROM `multi_language` LIMIT 0,
30; in PMA, all characters are displayed correctly (ü,
ä, ö, ...), but after any click or action the
characters shown like üäö ...
So all data, even when its iso-8859-1, -2, ..., or
utf-8 is transfered from the database to my website
1:1, with no conversion. When the data is displayed in
PMA it is converted in utf-8, even when its still utf-8.
I tried to change everything, in the config file of PMA
and the settings of the database, even table
collations, etc. but every time I connect to the
database with PMA the problem occurs.
I saw under
http://sourceforge.net/tracker/index.php?func=detail&aid=1187742&group_id=23067&atid=377408
that there is the same problem, but the difference is,
that I was using mysql 4.1.11 from the beginning on.
This problem did not occur with mysql version 4.0.x.
----------------------------------------------------------------------
>Comment By: SourceForge Robot (sf-robot)
Date: 02/01/06 16:54
Message:
Logged In: YES
user_id=1312539
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/13/06 06:12
Message:
Logged In: YES
user_id=192186
Well I'm still convinced that problem is with your data and
not in phpMyAdmin as there are lots of people not having
problems with charsets including me and all people who had
probles found out that this issue was caused by storing bad
data in tables (and not using SET NAMES and/or SET CHARACTER
SET).
Setting different charset in MySQL than using in HTTP
headers is wrong solution and if this works for you it means
you have wrong data stored in database or you've found bug
in MySQL.
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 01/13/06 04:23
Message:
Logged In: YES
user_id=1297068
For example the command mysqldump ... on the command line
generates correct sql-files with correct data (characters)
in it. Generating a sql-dump with a non modified PMA-Version
makes utf-8 characters unreadable.
Another example: Creating a whole project (database, tables,
collations and data, etc.) with a non modified PMA-Version,
stores the data (text in the tables), even if everything is
set to utf-8, as latin1. This behavior I figured out, when I
tried to readout data from the database with simple php
script. After my modification of PMA - when I choose "MySQL
connection collation" -> "utf8_general_ci", data is stored
as utf-8 and when I choose "MySQL connection collation" ->
"latin1_german1_ci" data is stored as latin1.
There are other persons who had contacted me, because they
were have the same problem with PMA - and after they have
modified their "database_interface.lib.php"-file everthing
worked.
I also asked some students of informatics to test, if their
PMA versions did have the same behavior on their servers and
all reported the same problem using mysql server versions
greater than 4.1.
So excuse me and thank you for your time, efforts and
patiences, PMA is a very good and great tool and one of my
favorites, but I (and other people) have this problem with
PMA together with newer mysql servers (4.1, 5.0)
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/13/06 03:47
Message:
Logged In: YES
user_id=192186
But setting charset to latin1 you tell mysql to do following:
Data is stored in utf8 (as you tell table structure), MySQL
converts it to latin1 and because of content charset in
phpMyAdmin it is displayed as utf8.
This means you have utf8 data in your tables which were
written as latin1 and it looks like you do not set
connection charsets correctly in your application.
phpMyAdmin can not support so broken situation.
Does command line client or MySQL Query Browser work
correctly with your tables? If not there is nothing what
could help you except converting data in order to be stored
correctly or hack every tool that takes care of charset...
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 01/11/06 07:04
Message:
Logged In: YES
user_id=1297068
Ok, I've forgot to say that I even tried SET NAMES. But this
didn't help, too. I also tried to change the main settings
of my mysql server - settings like "character set client",
"character set connection", "character set database",
"character set results", "character set server", "character
set system", "collation connection", "collation database",
"collation server" with utf8, latin1, utf8_general_ci and
latin1_general_ci, always the same problem. Setting this
parameters with php made my content unreadable, because it
was encodeed to utf8 two times, three times, four times, etc.
My problem was and still is, that PMA ingores the settings
in the field "MySQL connection collation" until I change the
"libraries/database_interface.lib.php". After this, when I
choose for example "latin1_general_ci", all latin1 data is
shown and can be edited correctly and when I choose
utf8_general_ci, all utf8 data is shown and can be edited
correctly. This also works for all other languages. Another
problem is, when I try to backup my database with PMA and I
haven't modified "libraries/database_interface.lib.php", all
special characters in my sql dump are being corrupted,
because I couldn't select latin1 over the field "MySQL
connection collation".
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/11/06 05:03
Message:
Logged In: YES
user_id=192186
SET CHARACTER SET changes ONLY which charset does MySQL
return that's why I asked. SET NAMES changes what charset
client uses for sending commands, if you don't use this,
your data are inserted wrongly. Using only SET CHARACTER SET
is NOT enough. See
http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 01/10/06 08:18
Message:
Logged In: YES
user_id=1297068
On 2005-06-27 10:15 I wrote:
---
... I tested my
application, WordPress, Drupal and some test scripts, all
using UTF-8, sometimes telling the server "SET CHARACTER
SET
utf8" or "SET NAMES utf8", both together, or the
same with
latin1. To test the interfaces I was using Firefox, Opera,
MSIE and Konqueror - on Linux (SuSE 9.2 and 9.2), Windows
(XP, 2000 and ME) and MacOS 9 and X - running LAMP and WAMP
solutions with MySQL 4.1.11 and PHP 5.04 on SuSE, Debian and
WinXP.
Using "SET CHARACTER SET utf8" in my application makes all
data unreadable with PMA, "SET CHARACTER SET latin1" works
fine - when I modify PMA's database_interface.lib.php.
...
---
As you can see, I have tested this with different cms and
scripts, different clients and different servers.
By the way, I know that systems like wordpress and drupal
dont't use settings like CHARACTER SET for database
connections, so I have added these by my myself, to test
what's happening.
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/10/06 07:39
Message:
Logged In: YES
user_id=192186
And does your application set both CHARACTER SET and NAMES
to encoding it uses (UTF8) when it access the database?
(Sorry I wasn't able to find this in your previous posts).
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 01/10/06 02:07
Message:
Logged In: YES
user_id=1297068
My database structure is:
Table RecordsTip Type
Collation Size Overhead
groups 15 MyISAM
utf8_general_ci 2.8 KB -
languages 9 MyISAM
utf8_general_ci 2.2 KB -
multi_language 174 MyISAM
utf8_general_ci 44.6 KB -
users 0 MyISAM
utf8_general_ci 1.0 KB -
user_groups 46 MyISAM
utf8_general_ci 9.1 KB -
user_info 35 MyISAM
utf8_general_ci 3.3 KB 20 Bytes
__multi_language 125 MyISAM
latin1_swedish_ci 28.2 KB -
7 table(s) 404 MyISAM
utf8_general_ci 91.2 KB 20 Bytes
And my table structure looks like:
Field Type Collation Attributes
Null Default
var varchar(255) latin1_general_ci
No
Bereich set('Admin' ...) utf8_general_ci
No
d text utf8_general_ci
No
e text utf8_general_ci
No
pl text utf8_general_ci
No
f text utf8_general_ci
No
i text utf8_general_ci
No
CreationTime datetime
No 0000-00-00 00:00:00
My database collation is utf8_general_ci.
All data send from my website to the database is formated in
utf-8.
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/10/06 01:03
Message:
Logged In: YES
user_id=192186
How are defined tables where you store data? Can you post
here structure?
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 01/10/06 00:47
Message:
Logged In: YES
user_id=1297068
Yes, this still happens with the current version (phpMyAdmin
2.7.0-pl2) and the only way to solve this, is to change the
file "libraries/database_interface.lib.php".
----------------------------------------------------------------------
Comment By: Michal Čihař (nijel)
Date: 01/09/06 13:55
Message:
Logged In: YES
user_id=192186
Does this still happen with current version?
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 06/27/05 01:15
Message:
Logged In: YES
user_id=1297068
I know what you mean. I took the weekend to test all kinds
of configuration possibilities.
First to answer your question. Yes my application inserts
data that is already encoded in UTF-8 and does also report
this to the
MySQL server.
So to exclude any mistakes of my own, I tested my
application, WordPress, Drupal and some test scripts, all
using UTF-8, sometimes telling the server "SET CHARACTER SET
utf8" or "SET NAMES utf8", both together, or the same with
latin1. To test the interfaces I was using Firefox, Opera,
MSIE and Konqueror - on Linux (SuSE 9.2 and 9.2), Windows
(XP, 2000 and ME) and MacOS 9 and X - running LAMP and WAMP
solutions with MySQL 4.1.11 and PHP 5.04 on SuSE, Debian and
WinXP.
Using "SET CHARACTER SET utf8" in my application makes all
data unreadable with PMA, "SET CHARACTER SET latin1" works
fine - when I modify PMA's database_interface.lib.php. The
reason for this is, that my PMA installation ignores the
"MySQL connection collation" select box until I set in
database_interface.lib.php "SET CHARACTER SET" to latin1.
After this I can select the collation "latin1_german1_ci" to
edit UTF-8 data in the database, or I can select
"utf8_general_ci" to edit latin1 data in the database. This
also works for all other language and character types, I
have tested "ascii_general_ci", "cp1251_general_ci",
"greek_general_ci", "cp852_general_ci", "koi8r_general_ci"
and "utf8_polish_ci".
After my modification, there I had no problem displaying or
editing the content with PMA.
So please tell me what the problem is with my PMA-Version.
----------------------------------------------------------------------
Comment By: Alexander M. Turek (rabus)
Date: 06/24/05 06:53
Message:
Logged In: YES
user_id=418833
SET CHARACTER SET does not affect the connection charset,
but the client charset. You're suggesting to hardcode the
client charset to latin1 which would be fatal as phpMyAdmin
internally uses UTF-8 in order to be able to handle all
special characters that could be received from the MySQL server.
Please do not think about our code. Just answer the question
from my previous post, please.
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 06/24/05 05:08
Message:
Logged In: YES
user_id=1297068
This is not a problem with my client application.
I have tested the problem now on many configurations (Apache
2, PHP 5.0.4, MySQL 4.1.11), for example on WinXP and Linux
(RedHat, SuSE, Fedora, Debian) independent from my website
project.
In PMA you have the select box for the "MySQL connection
collation" and this menu only have an effect on the
connection, when you change "PMA_DBI_query('SET CHARACTER
SET ' .
$mysql_charset . ';', $link, PMA_DBI_QUERY_STORE);" into
"PMA_DBI_query('SET CHARACTER SET latin1;', $link,
PMA_DBI_QUERY_STORE);" on line 175 in
libraries/database_interface.lib.php.
I'm not the only one, who have got this problem. There are
many users that reported the same problem.
Just use google.com.
I figured out, that the problem is, that the content of the
variable "$mysql_charset" on line 175 in
libraries/database_interface.lib.php always is "utf8" and
the "MySQL connection collation"-Menu has no effect on it.
----------------------------------------------------------------------
Comment By: Alexander M. Turek (rabus)
Date: 06/24/05 04:34
Message:
Logged In: YES
user_id=418833
Moving to support requests.
I think, it's rather a problem with your own client application.
You metioned that your application inserts data that already
is encoded in UTF-8. But does it also report this to the
MySQL server? Does your client send "SET CHARACTER SET utf8"
or "SET NAMES utf8" to the MySQL server after having connected?
----------------------------------------------------------------------
Comment By: k-dev4edv (k-dev4edv)
Date: 06/16/05 06:59
Message:
Logged In: YES
user_id=1297068
Maybe I have found the answer to the problem.
After I have changed in libraries/database_interface.lib.php
on line 175 "PMA_DBI_query('SET CHARACTER SET ' .
$mysql_charset . ';', $link, PMA_DBI_QUERY_STORE);" into
"PMA_DBI_query('SET CHARACTER SET latin1;', $link,
PMA_DBI_QUERY_STORE);" the drop down menu on the start page
of my PMA "MySQL connection collation" works and all
characters are displayed correctly after selecting
"latin1_german1_ci". So it seems that $mysql_charset doesn't
work correctly.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=377409&aid=1221046&group_id=23067
|
