Transition MySQL 4.1.14 cp1251->MySQL 5.0.27 utf-8 - how?

[JWalker]

Hi,

I have problems when moving my blog to newer php/mysql/apache.

I ran my blog on PHP 5.0.2 / MySQL 4.1.14 in cp1251 page before. This is a Bulgarian codepage with cyrillic letters. I reinstalled my system and have PHP 5.2.1 / MySQL 5.0.27 now.

When was on MySQL 4.1.14 I exported the database in sql file, which is written in utf-8 (I checked in my editor). All cyrillic texts was able to be seen. I imported the database in mysql 5.0.27, and saw '???????' where should be cyrillic texts from database. The menus, and other stuff that comes from serendipity is in cyrillic and can be read.

I did a second try: In the SQL file, I changed all CREATE TABLE .... DEFAULT CHARSET=cp1251; by CREATE TABLE .... CHARSET=utf8 COLLATE=utf8_unicode_ci;. Then imported this file (previously dropped the database from the first try). And again I saw '??????????????' instead of cyrillic letters. I switched to utf8 in serendipity config, but the same result.

Then I tried ... CHARSET=utf8; but missed COLLATE=utf8_unicode_ci. The same result.

Where should I dig to resolve this ? May be somwhere in php-mysql link ? I'm sorry but the server is not public and I cannot provide url to it. Any help would be welcome.

[garvinhicking]

Hi!

Did you also try to change the s9y config setting for "Use DBNames"? That can help in some cases.

Sadly charset problems like these drive me nuts as well, there are so many places where things can go wrong: file encoding, file upload encoding, sql import encoding, phpmysql encoding, mysql client connection charset, mysql server charset, serendipity encoding, serendipipty charset and apache transfer encoding.

Best regards,
Garvin

[JWalker]

Where to set Use DBNames ?

It seems that this is mysql problem -- outside S9Y, because I looked in the database by MySQL QueryBrowser. Initially I saw there ??????? there, but changed

/*!40101 SET NAMES utf8 */;
to
/*!40101 SET NAMES cp1251 */;

and converted the sql file into cp1251. After I imported it, I saw these ciryllic letters in Query Browser, but still not in s9y... I little step forward....
Will dig ahead.

[garvinhicking]

Hi!

Where to set Use DBNames ?

Global s9y configuration, database section, last option there. Only available since Serendipity 1.0 or 1.1, I think.

HTH,
Garvin

[JWalker]

Hi,
Use DBNames works , there is still more that I have to do, but really DBNames helped.

Thanx !

[JWalker]

Hi,
Now all works fine and I am just posting what I did (may help someone else):

1. Converted the SQL file into ANSI (it was in UTF-8 ).
2. Changed
/*!40101 SET NAMES utf8 */;
to
/*!40101 SET NAMES cp1251 */;
3. Changed
CREATE DATABASE IF NOT EXISTS serendipity CHARACTER SET utf8;
to
CREATE DATABASE IF NOT EXISTS serendipity CHARACTER SET cp1251 collate cp1251_bulgarian_ci;
4. Changed for all tables
CREATE TABLE .... ENGINE=InnoDB DEFAULT CHARSET=utf8;
to
CREATE TABLE .... ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE cp1251_bulgarian_ci;
5. Imported this file. Saw inreadables.
6. Enabled SET NAMES in the database configuration.
7. Refreshed several times the browser.
8. .