Warning: include_once(/home/nullvoid/blog.mikezhang.com/wp-content/plugins/wordpress-support/wordpress-support.php): failed to open stream: Permission denied in /home/nullvoid/blog.mikezhang.com/wp-settings.php on line 217

Warning: include_once(): Failed opening '/home/nullvoid/blog.mikezhang.com/wp-content/plugins/wordpress-support/wordpress-support.php' for inclusion (include_path='.:/usr/local/lib/php:/usr/local/php5/lib/pear') in /home/nullvoid/blog.mikezhang.com/wp-settings.php on line 217
NullVoid » phpmyadmin display utf8 NullVoid » Blog Archive » phpmyadmin display utf8

phpmyadmin display utf8


Took me a whole afternoon to figure it out.

MySQL used non-utf-8 encoding, so when I use phpmyadmin to interpret it, the returned result is messed up.

mysql> show variables like ‘character_set%’;
| Variable_name | Value |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | \mysql\share\charsets\ |
8 rows in set (0.00 sec)

mysql> show variables like ‘collation%’;
| Variable_name | Value |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
3 rows in set (0.00 sec)

I wrote a program to query the DB and used the following code to check if the characters are indeed utf-8:

use strict;
use Encode;
my $iftest=1;
my $ifpause=1;
use DBI;
my  $conn = DBI->connect     ("DBI:mysql:ki","","")     or die("Cannot connect: $DBI::errstr");
$conn->{RaiseError} = 0;
my $sql;
my $query;
my $row;
my $id=1;
$sql=qq(select * from categorylinks );
$query = $conn->prepare($sql);
$query->execute 		or print("n[Select ID]Error executing SQL statement! $DBI::errstrn");
while ($row = $query->fetchrow_arrayref){ 		my $line=@$row[1];
print "$linen";
}  $conn->disconnect;

Turned out that the output are correct.

By phpmyadmin still outputs messy code for Chinese, so I modified the following file


Under “// MySQL charsets ma”
Changed from
‘utf-8? => ‘utf8?,
‘utf-8? => ‘latin1?,
Voila, it worked

====Update 2009-09-09======

The above solution worked on Windows and Mac, but did not work on Ubuntu. Here is what I need to do on Ubuntu:

This is a hack to make phpmyadmin show correct multibyte characters (Russian, Japanese, etc) in browse and edit view.
Courtesy: Oyama @ irc.freenode.net##php

cd /usr/share/phpmyadmin/libraries/dbi/      (or wherever is your phpmyadmin directory)

edit “mysql.dbi.lib.php”
locate the end of the function “function PMA_DBI_connect($user, $password, $is_controluser = FALSE)”     (around line 88 for me)
insert after “PMA_DBI_postConnect($link, $is_controluser);” and before “return $link;” the following two lines:
“mysql_query(“SET SESSION CHARACTER_SET_RESULTS =latin1;”,$link);”
“mysql_query(“SET SESSION CHARACTER_SET_CLIENT =latin1;”,$link);”
save and quit

edit “mysqli.dbi.lib.php”
locate the end of the function “function PMA_DBI_connect($user, $password, $is_controluser = false)”     (around line 115 for me)
insert after “PMA_DBI_postConnect($link, $is_controluser);” and before “return $link;” the following two lines:
“mysqli_query($link, “SET SESSION CHARACTER_SET_RESULTS =latin1;”);”
“mysqli_query($link, “SET SESSION CHARACTER_SET_CLIENT =latin1;”);”
save and quit

That’s it!
Still, i don’t really consider it as a real solution, since it just “avoids” the real problem and doesn’t correct it.
Anyway, it works, and that’s all i need ;)

33 Responses to “phpmyadmin display utf8”

  1. cory Says:

    This worked for me. Great find, thanks.

  2. ET Says:

    Cool. :-)

  3. Markus Says:

    Thanks, this was a solution for my problem too!

  4. aw tan Says:

    if the purpose is just for phpmyadmin to display chinese utf8 chars correctly, you can set charset to utf8 either at db, table, or column level, such as

    create table foo (name varchar(255) set charset utf-8); OR

    create table foo (name varchar(255)) set charset utf-8

    I have tried it. phpmyadmin does display the chinese characters correctly.

    I have a different problem — those utf8 chars don’t show up correctly in php applications.

  5. Piotras Says:

    Thank you. It works for lithuanian language too.

  6. HP Says:

    Many thanks, this solved my problem!

  7. a man Says:

    wow it work! Thanks a lot.
    but I am confused.

    ‘utf-8′ => ‘utf8′, It’s correct,but messed up the display result.
    ‘utf-8′ => ‘latin1′ It’s is uncorrect,but fixed the problem.


  8. ET Says:

    It has to do with the internal storage of the characters. I guess this works as a quick fix, it does not mean the database is configured properly.

  9. Antonio Says:

    Thank you very much. I was going crazy with this. I spent hours trying to fix this. Thank you very much.

  10. Ioannis Says:

    WoW! It does work indeed!!!

    All the encodings (on the php, database, table and collations level) are correct (utf8) but the result in the phpmyadmin (phpMyAdmin – 2.11.4) are not displayed correctly. When ‘utf-8? => ‘latin1? it fixed the display problem (Note: the web page encoding still correctly is utf-8 – Strange).
    Does anyone knows why is this happening ? Should we report it as a bug ?

    Thanx all

  11. Mulle Says:

    This worked for me too. It’s really tricky to locate the problem when there are so many parts involved. When I realized that it was phpMyAdmin that messed up I found this quick fix.
    Thanks a lot!

  12. Sergey Says:

    Much thanks!

  13. dingle Says:

    I found a fix somewhere else. Suppose all your tables are utf8, in your php code, right after mysql_connect(), you need to add mysql_query(“set names utf8″), which basically means all future incoming queries are encoded in utf8. Right after this command in mysql command line, i can see those chinese chars are encoded wrongly. This means you have to change all your data to utf8 (i suppose phpmyadmin is using utf8 for queries). YMMV. ref: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

  14. dingle Says:

    A more “correct” fix. After connecting to database, “SET NAMES utf8″ to set utf8 encoding for all incoming queries. Unfortunately, it means that all your previous data are encoded incorrectly (the default “NAMES” must be latin1). Phpmyadmin is correctly using utf8 to query myql database.
    It works for me, YMMV. More details plz see Ref:

  15. simsik Says:

    thanks, it works, i was going crazy with russian and estonian

  16. Herman Santos Says:

    You Rock!

    I always use it to export DBs

  17. When you thought the DB was UTF-8 but it wasn’t « OmegaDelta Says:

    [...] In the mysqli.dbi.lib.php and mysql.dbi.lib.php (two files), in the PMA_DBI_connect function, add the code (thanks to Mike Zhang for the code): [...]

  18. William Denniss Says:

    I had this same problem.

    My data was not being stored correctly (though my PHP app could use it through a latin1 connection correctly).

    How I fixed my data was to apply your hack to phpMyAdmin (thanks!), then export my data (so it’s now correct UTF-8), remove the hack, and re-import the data. Then set my connection in PHP to utf-8.

    Full writeup on my blog: http://omegadelta.net/2010/11/23/when-you-thought-the-db-was-utf-8-but-it-wasnt/

  19. duc Says:

    Thanks for the tip. I have been searching for a quick solution and this seems to work.

  20. development Says:


    [...]NullVoid » phpmyadmin display utf8[...]…

  21. appliance repair Newport Beach Says:

    Some really interesting details you have written on blog.mikezhang.com .Aided me a lot, just what I was searching for :D .

  22. http://tinyurl.com/emptlong29531 Says:

    I actually desired to show this unique blog, “NullVoid

  23. magickey Says:

    1- Edit file:
    my.ini (MsWindos)
    my.cnf (GNU/linux)

    2- Look for [mysqld] entry and append:
    character-set-server = utf8

    The whole view should look like:
    character-set-server = utf8

    3- Restart MySQL service!

  24. buy dvds online Says:

    Very interesting info !Perfect just what I was searching for! “Energy is the power that drives every human being. It is not lost by exertion by maintained by it.” by Germaine Greer.

  25. NothingCtrl Says:

    Latest version of PhpMyAdmin i don’t see the file mysql.dbi.lib.php :(

  26. Doctor Watts Says:

    Worked for me. Thank you!

  27. Social Media Firesale Program Says:

    The last owner – Cindy Battye teamed up with John & Soren in early 2009 – IM Wealth BuildersCovert Conversation Review.

  28. android game Says:

    The objective is to unlock a genetic puzzle by combining various fish
    breeds and only then can you unlock these
    mythical fish species. I think that’s just an excuse to the problem, a massive use of drones in the future can surely decrease the casualty
    of war, but they bombs civilians more often rexently years, so let’s just regard them more sensible, we need more ime to update
    AI of ouur drones or the accuracy of our weapons.

    s mouth ass well as damage the professional reputation of your company.

  29. axbo.ru Says:

    Hiya very cool website!! Man .. Beautiful ..
    Superb .. I will bookmark your website and take the feeds also?
    I am glad to seek out so many helpful info here within the publish, we’d like develop more strategies in this regard, thank you for sharing.
    . . . . .

  30. comprar drones baratos Says:

    I don’t even know the way I stopped up right here, but I assumed this publish was good. I do not understand who you’re however definitely you are going to a well-known blogger for those who aren’t already. Cheers!

  31. UTF8 Says:

    What’s up, after reading this remarkable piece of writing
    i am also delighted to share my knowledge here with friends.

  32. vintage country wedding invitations Says:

    Wow, Thank You! I watched it many times on VHS as a kid and loved Its realy nice to find it again today, 2 days before

  33. sweet 15 invitation ideas Says:

    I suggest apple or blackberry filling if you’ll be making

Leave a Reply

You are visitor number several since September 1, 2001

Copyright Xiaoquan (Michael) Zhang, 2004-2020. All rights reserved.
All trademarks property of their owners.