Latest posts for the topic "Get problem when importing alternateNames table into MySQL"

Get problem when importing alternateNames table into MySQL

Alexey — GMT

Hello, I've created table using UTF-8 character set: CREATE TABLE `alternatenames` ( .... ) CHARACTER SET utf8 ; Then I imported alternateNames.txt dump into this table. The problem is some data is corrupted now. For example, when I try to do this query: SELECT * FROM geo.alternatenames where geonameid = '2078025' AND isolanguage = 'ru' I get "ÐÐ´ÐµÐ»Ð°Ð¸Ð´Ð°" instead of "Аделаида". I wonder how can I fix it?

Re:Get problem when importing alternateNames table into MySQL

marc — GMT

Hi Alexey It is not necessarily a problem with the import. It could be a problem with your display encoding. Cheers Marc

Re:Get problem when importing alternateNames table into MySQL

Alexey — GMT

marc wrote:

Hi Alexey It is not necessarily a problem with the import. It could be a problem with your display encoding. Cheers Marc

I've tried to enter data into MySQL manually with MySQL Query Browser and russian letters display ok. I think it's the problem with MySQL or with import procedure.

Re:Get problem when importing alternateNames table into MySQL

Alexey — GMT

Still can't find solution. I tried to enter SET NAME... before importing, but still doesn't import correctly. Any ideas guys?

Re:Get problem when importing alternateNames table into MySQL

auxburger — GMT

Hi Alexey, just recently i had a similar problem and was able to solve it with the help of php's built in utf8decode() function before saving it to the mysql. You might look as well after mysql_real_escape_string(). Just an idea.

Re:Get problem when importing alternateNames table into MySQL

Alexey — GMT

auxburger wrote:

Hi Alexey, just recently i had a similar problem and was able to solve it with the help of php's built in utf8decode() function before saving it to the mysql. You might look as well after mysql_real_escape_string(). Just an idea.

Thanks. I do not use PHP here, PHP works fine with MySQL in UTF8 if I enter UTF8 data manually. The problem is I can't import Geoname Alternatenames dump into MySQL. I get corrupted data. :(

Re:Get problem when importing alternateNames table into MySQL

Alexey — GMT

Solved.

Re:Get problem when importing alternateNames table into MySQL

marc — GMT

Hi Alexey I am glad you could solve the problem. If you think other GeoNames users could run into the same problem then it would be great if you could post the solution. Cheers Marc

Re:Get problem when importing alternateNames table into MySQL

Alexey — GMT

marc wrote:

Hi Alexey I am glad you could solve the problem. If you think other GeoNames users could run into the same problem then it would be great if you could post the solution. Cheers Marc

Sure Marc. The problem was in MySQL settings. To make it work users should: 1. Create tables with UTF-8 character set and UTF-8 collation. 2. Set database setting to UTF-8 by this command:

ALTER DATABASE databasename charset=utf8;

3. Set MySQL settings to UTF-8 by adding these lines into MySQL configuration file (my.cnf for Linux, my.ini for Windows):

character_set_server=utf8 collation_server=utf8_general_ci

After this user should restart server. That's it. :) People can check settings by entering this command:

SHOW VARIABLES LIKE 'c%';

I really hope this will help, especially for people who doesn't know much about MySQL. ;)