moving gallery to new server - database problems

[yacenty]

Hi guys,
long time without any troubles, but finally big issue to solve.
My current ISP decided to cancel our contract and I need to move to new machine. I decided to choose dedicated server, and have problem now.
I've installed mysql 5.0 by default latin1 coding is set.
My previous database engine was mysql 4.1 with latin2 coding, but data was stored as UTF8.
Now I can't do anything, I've tried some conversions, some set names in gallery, but nothing is working
My previous address is www.agrofoto.pl/gallery - new (temporary) is boleslawice.info/gallery.
What is interesting I am using SMF 1.1 with UTF8 coding and after movement SMF is working ok but CPG not:(
Do you have any ideas how should I set up mysql server? how database? and how to finally convert data?

Thanks in advance

Best regards,

YacentY

[Nibbler]

Import the dump into a db set to utf-8. Set Coppermine to use utf-8 in config.

[yacenty]

I've tried it, but maybe with some mistake
i've set up new database and after that import data with:

Code Select Expand

mysql> drop database agrofoto;
Query OK, 90 rows affected (0.21 sec)

mysql> create database agrofoto character set utf8 collate utf8_polish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
baza:~# mysql -u root -p agrofoto < /root/agrofoto-utf8.sql


is it ok?
In cpg utf8 is set in settings.

and strange characters on the site

[Nibbler]

Can you provide a dump of some non-important data we can test with? (categories table probably a good choice).

[yacenty]

edited with vim
saved and compressed under windows to zip file

as I mentioned before database was coded latin2, data from website was coded in UTF8
but I don't want how to manage it now

[yacenty]

i didn't find how to edit previous post

Code Select Expand

baza:~# file /root/agrofoto-utf8.sql
/root/agrofoto-utf8.sql: UTF-8 Unicode text, with very long lines, with LF, NEL line terminators

it may be usefull

regards,
YacentY

[Nibbler]

Why did you use SET NAMES utf8? If the data is already utf-8 then that will corrupt the encoding.

[yacenty]

it was for short time because my database were on different engine, different ISP, but it was and is working, but when I want to move it to this new server don't want.
What should I do now?

Best regards,
YacentY

[Nibbler]

The dump itself has bad encoding. You need to get a correct dump first before worrying about how to import it. This is not related to Coppermine so my assistance will be limited.

[Nibbler]

The table is being created as latin2 in the dump, that should be utf-8.

[yacenty]

the big problem is that in current hosting I don't have shell so I can only request a dump, it's no possible to do it by myself, other possibilty is to use phpmyadmin but I've hard that this tool can easily destroy encoding.

What do You suggest?

[yacenty]

I've tried to change latin2 to utf8 statements during creation of tables in the sql scrip but the same happend

[Nibbler]

Try phpmyadmin.

[yacenty]

done, when I open it in Dreamweaver on Windows it seems ok, but when vim on server - bulshit, imported - bulshit
maybe now I should convert it somehow?
any idea?

[yacenty]

it's crazy because when I input some special POLISH characters as a comment, there are shown correctly, so I think that the communication is set correctly but how to get correct data?

regards

YacentY

[Nibbler]

The dump in your latest attachment looks fine to me. If you import that data into a utf-8 table in a utf-8 db it should work.

[yacenty]

I;ve put this file on server changed latin2 to utf8 in type of table and saved in vim
unfortunatelly  something changed but we have different strange characters.

maybe there is something wrong with my settings of mysql?

Code Select Expand

| character_set_client            | latin1                       |
| character_set_connection        | latin1                       |
| character_set_database          | utf8                         |
| character_set_filesystem        | binary                       |
| character_set_results           | latin1                       |
| character_set_server            | latin1                       |
| character_set_system            | utf8                         |
| character_sets_dir              | /usr/share/mysql/charsets/   |
| collation_connection            | latin1_swedish_ci            |
| collation_database              | utf8_polish_ci               |
| collation_server                | latin1_swedish_ci            |

Now I have totally no idea

Best regards,

I;ve tried to open this file that looks perfect in Dreamweaver and copy paste to new file in different encodings - I was very close with Central europe (iso), maybe two or three characters was wrong, but wrong

Crazy things

[yacenty]

I think I have to give up, and my job manually.
What is the best, most universal encoding? I know that this will be unprofessional but I will set up database with strange charcters and ask customers to correct their pictures and comments manually.

What do You think?
Best regards,
YacentY