Phpmyadmin Export And Collation Problems

[TavoxPeru 0]

I have a very strange problem with the Exported sql file generated by phpMyAdmin that never happen to me before, the problem is that my data contains some strange characters but when i see them on screen it shows correctly, instead of showing the spanish characters ĂĄ, ĂŠ, Ă­, Ăł, Ăş and Ăą it shows others, for example instead of ĂĄ shows ĂÂĄ, instead of Ăł shows ĂÂł, etc.

The other problem i have is that when i use Internet Explorer and want to download the generated sql file to my machine nothing happens, it seems that the export script works completely but it never shows the Save As... dialog box. This problem never occurs if i use Firefox.

The following are some data of the phpMyAdmin welcome page:

MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: utf8_unicode_ci
phpMyAdmin - 2.9.0.2
Server version: 4.1.21-standard
Protocol version: 10
MySQL client version: 4.1.21
Used PHP extensions: mysql

Best regards,

[yordan 10]

OK, the strange character set is a very common problem.it's due to the way you defined the national characterset when creating the database.Probably, when you created the database, you created it with a non-spanish character set, probably utf8_unicode_ci.So, you entered some spanish characters with your PC which has a spanish keyboard and a spanish-defined national language, and mysql translated them in order to be inserted in the non-spanishly-defined database tables. When reading the data from your application, the characters where oppositely correctly converted from non-spanish database characters to spanish displayable characters on your PC. However, when performing an export, the data in the exported sql file were the in the database-defined character set.This means that if you have to re-create the database (for instance for installing your program to another site) you will have to re-create the database with the same character set as in the current database.This is my recommendation in order to be able to use your current export file.This is not very dangerous, as long as everything remains the same way - spanish people entering data in a utf8_unicode_ci database. this will probably be different if greek people or french people try to read your data.Of course, for your next customer, if you star from the very beginning, on a completely empty database, I recommend to correctly define the database characterset, probably utf8_spanish_ci, or utf8_spanish2_ci. Then everything will be coherent, spanish characters intered in a spanish database, the export file will also have spanish characters.I hope that you were able to correctly understand my explanations concerning this characterset problem.So, now, I mus confess that I am also trapped everytime with this kind of problems, and I am able to explain the problem but I was never able to correctly solve it. Simply, I tell my customer "ask your dba to correctly define the national characterset". And hopefully, I am not the concerned dba, so this is not my task. Else I would have to confess that I am unable to solve it...

[SilverFox1405241541 0]

I believe you can convert it to another char_set when exporting.

[yordan 10]

I believe you can convert it to another char_set when exporting.

Probably yes. But I would like to know the way how to do it. I found nothing in the phpmyadmin menus, neither than in the phpbb menus. Seems that the import/export tools are for backup/restore purposes, the same way on the same system. They seem not to be intended as real migrational tools.Moreover, I would say that reading the sql file using notepad is not the correct way for dianosing these kinds of troubles. The best way should be to export one table, and import inside an empty table, and use phpmyadmin to browse the new table. And even then, the diagnostic could be false, you probably should also use your php program in order to display the new table the same way as the old one.

[TavoxPeru 0]

yes, yordan i'm able to correctly understand your explanations, thank you very much. Now, after some tests i made i discover that another reason for this issue happens is related with the browser and with which character set it uses, so, what i do to solve this, is by setting up the UTF-8 character set to my browser.

 

SilverFox I'm almost pretty sure that you can't convert the char_set when exporting, also i never see this option available to set up on the configuration file of phpMyAdmin, where i see something similar is in the import page, in this page you are been able to set the character set of the sql file that you want to import.

 

The import/export tools are for restore/backup purposes, i have the chance to use it a lot of times and i can say that it works very fine in almost all situations.

 

Best regards,