mysql4tomysql5 (UTF8)

from mysql
http://dev.mysql.com/doc/refman/4.1/en/localization.html

from thomas

mysql4tomysql5 (UTF8)

以下是我剛好有需要 mysql4 dump出來的資料 import到 mysql5 的過程

先備份資料庫

mysqldump -u root -p --default-character-set=latin1 Koha >output.sql

piconv -f utf8 -t big5 output.sql > big5.sql

piconv -f big5 -t utf8 big5.sql >utf8.sql



升級mysql

apt-get install mysql-server-5.0 mysql-common mysql-client-5.0
MySQL的my.cnf設定檔內要加入以下設定

[client]
default-character-set=utf8
[mysqld]
init_connect= 'SET NAMES utf8'
default-character-set=utf8
default-collation=utf8_general_ci

建立Koha資料庫

CREATE DATABASE `Koha` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改sql檔，在最前面加上

SET NAMES utf8;
SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_RESULTS=utf8;

再來把每個資料表後面的

TYPE=MyISAM;

改成

ENGINE=MyISAM DEFAULT CHARSET=utf8;

都改好後就可以把他import進去了

#mysql -u帳號 -p 資料庫 < utf8.sql



可參考http://blog.leolo.cc/2006/02/06/134/



以上完成之後用phpmyadmin已經可以看到中文！

但目前koha這邊還沒有修改程式成適用utf8

所以my.cnf 加入了 init_connect= 'SET NAMES utf8'這樣會影響其他舊有的程式

此外

KOHA 在 mysql5 使用 left join 的語法似乎有問題

無法辨識LEFT JOIN table1 ON TABLE1.column=TABLE2.column 造成error

[Tue Jul 04 19:10:35 2006] [error] [client 127.0.0.1] DBD::mysql::st fetchrow failed: fetch() without execute() at /opt/koha/intranet/modules/C4/SearchMarc.pm line 334., referer: http://127.0.0.1:8080/cgi-bin/koha/members/member.pl

原因是

SELECT biblio.biblionumber as bn,biblioitems.*,biblio.*, marc_biblio.bibid,itemtypes.notforloan,itemtypes.description
FROM biblio, marc_biblio
LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
LEFT JOIN itemtypes on itemtypes.itemtype = biblioitems.itemtype
WHERE biblio.biblionumber = marc_biblio.biblionumber AND bibid = ?



無法辨識 biblio.biblionumber 但是改成 'biblio.biblionumber' 就可以了！

但是用到left join 或是類似的join有好幾隻程式~~這樣有需要改嗎？？？

且應該還有更多mysql5 utf8的問題有帶發掘與解決

ps
已經解決尚未測試FROM Kochin Chang
Dear Thomas,

I just installed Chinese Koha on Ubuntu 6.06 which comes with MySQL
5.0.x. Your earlier message about your experiences with installing Koha
with MySQL 5.0.x helped a lot. In your message you mentioned

但目前koha這邊還沒有修改程式成適用utf8
所以my.cnf 加入了 init_connect= 'SET NAMES
utf8'這樣會影響其他舊有的程式

To get around this problem, I modified intranet/modules/C4/Context.pm
so that the procedure sub dbh now becomes

sub dbh
{
my $self = shift;
my $sth;

if (defined($context->{"dbh"})) {
$sth=$context->{"dbh"}-
>prepare("select 1");
return $context->{"dbh"} if (defined($sth->execute));
}

# No database handle or it died . Create one.
$context->{"dbh"} = &_new_dbh();
# Make sure UTF-8 charset is used for connection and results.
if (defined($context->{'dbh'})) {
$sth = $context->{'dbh'}->prepare('SET NAMES utf8');
$sth->execute;
}

return $context->{"dbh"};
}

The idea is to send the 'SET NAMES utf8' string over the newly created
connection to MySQL server. With this you don't have to put 'SET NAMES
utf8' in the my.cnf. I have tested my modification on my Koha
installation. So far it works like a charm.

Regards,
Kochin Chang