GeoNames Forum

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?

Hi Alexey

It is not necessarily a problem with the import. It could be a problem with your display encoding.

Cheers

Marc

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.

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

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.

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.

Solved.

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

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.