[Erp5-dev] Using another character set and collation

[Łukasz Nowak]

Hello,

By default MySQL's default-character-set is latin1 and collation-server
= default-collation = latin1_swedish_ci.

But I'd like to use:
character set latin2
collation latin2_general_ci

So I've modified my.cnf to have:

[mysqld]
character-set-server = "latin2"
character-set-client = "latin2"
collation-server = "latin2_general_ci"


After this created database for ERP5 installation (eg. erp5localtest),
made sure, that its collation is latin2_general_ci (checked twice).

Installed erp5_base Business Template.

Created organisation, which title has some national characters.

Such script:
for q in context.portal_catalog(
portal_type = 'Organisation',
title = '?%' # any national character - for empty or w/o national
charcter all is working
):
print q.getRelativeUrl()

return printed

Produces attached traceback.

What else shall I do, to use latin2 character set and collation?

Revision: 20709
Products: CMFActivity ERP5 ERP5Catalog ERP5OOo ERP5Subversion
ERP5Type ZMySQLDA ZSQLCatalog CMFCategory ERP5Banking ERP5Form
ERP5Security ERP5SyncML ERP5Wizard TimerService ZMySQLDDA
Zope: (Zope 2.8.9.1-final, python 2.4.4, linux2)

Regards,
Luke

[Boris Kocherov]

Witaj ?ukasz.

I have the same problems and myerp5.com too.

As i have tested two things should solve this problem.
1) run this sql query
"ALTER DATABASE db_name CHARACTER SET utf8"

2) use attached patch

After all do not forget to clean and reindex catalog.

I want to commit this patch in trunk. What should i do for that?

I think that change default charset is not a good idea - the different
applications which are using mysql may need different default charset.

Regards
Boris Kocherov

Post by Łukasz Nowak
Hello,
By default MySQL's default-character-set is latin1 and collation-server
= default-collation = latin1_swedish_ci.
character set latin2
collation latin2_general_ci
[mysqld]
character-set-server = "latin2"
character-set-client = "latin2"
collation-server = "latin2_general_ci"
After this created database for ERP5 installation (eg. erp5localtest),
made sure, that its collation is latin2_general_ci (checked twice).
Installed erp5_base Business Template.
Created organisation, which title has some national characters.
for q in context.portal_catalog(
portal_type = 'Organisation',
title = '?%' # any national character - for empty or w/o national
charcter all is working
print q.getRelativeUrl()
return printed
Produces attached traceback.
What else shall I do, to use latin2 character set and collation?
Revision: 20709
Products: CMFActivity ERP5 ERP5Catalog ERP5OOo ERP5Subversion
ERP5Type ZMySQLDA ZSQLCatalog CMFCategory ERP5Banking ERP5Form
ERP5Security ERP5SyncML ERP5Wizard TimerService ZMySQLDDA
Zope: (Zope 2.8.9.1-final, python 2.4.4, linux2)
Regards,
Luke
------------------------------------------------------------------------
_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://mail.nexedi.com/mailman/listinfo/erp5-dev

-------------- next part --------------
A non-text attachment was scrubbed...
Name: zmysqlda-default-utf8.patch
Type: text/x-diff
Size: 1113 bytes
Desc: not available
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20080620/27bb450a/attachment.patch>

[Boris Kocherov]

Dear developers.

Could you please give any comments on my patch.

I think this patch is very important for erp5 expansion.
May be i have done something wrong?
May be patch format is not applicable for you?
May be you have bug tracking tool?

Best regards.
Boris Kocherov

Post by Boris Kocherov
Witaj ?ukasz.
I have the same problems and myerp5.com too.
As i have tested two things should solve this problem.
1) run this sql query
"ALTER DATABASE db_name CHARACTER SET utf8"
2) use attached patch
After all do not forget to clean and reindex catalog.
I want to commit this patch in trunk. What should i do for that?
I think that change default charset is not a good idea - the different
applications which are using mysql may need different default charset.

[Jean-Paul Smets]

Hi,

Thanks for your patch. You are right, it is important. Be sure that it
will be reviewed. We are currently preparing a big upgrade of
myerp5.com. We will reply after that.

Regards,

JPS.

Post by Boris Kocherov
Dear developers.
Could you please give any comments on my patch.
I think this patch is very important for erp5 expansion.
May be i have done something wrong?
May be patch format is not applicable for you?
May be you have bug tracking tool?
Best regards.
Boris Kocherov

Post by Boris Kocherov
Witaj ?ukasz.
I have the same problems and myerp5.com too.
As i have tested two things should solve this problem.
1) run this sql query
"ALTER DATABASE db_name CHARACTER SET utf8"
2) use attached patch
After all do not forget to clean and reindex catalog.
I want to commit this patch in trunk. What should i do for that?
I think that change default charset is not a good idea - the different
applications which are using mysql may need different default charset.

_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://mail.nexedi.com/mailman/listinfo/erp5-dev

[Kazuhiko Shiozaki]

Hi Boris,
I'm sorry for late reply.

Post by Boris Kocherov
As i have tested two things should solve this problem.
1) run this sql query
"ALTER DATABASE db_name CHARACTER SET utf8"
2) use attached patch
After all do not forget to clean and reindex catalog.
I want to commit this patch in trunk. What should i do for that?
I think that change default charset is not a good idea - the different
applications which are using mysql may need different default charset.

Since sorting and collation in ERP5 (w/ catalog) depends on MySQL's
behaviour, wrong communication between ERP5 and MySQL causes unexpected
results.

There are two ways to solve this character set problem.

A. force MySQL to use utf8 without character set handshaking
------------------------------------------------------------

This is easy. Just add the following into [mysqld] section in your my.cnf.

default-character-set=utf8
skip-character-set-client-handshake

As you mentioned, this configuration is too global and can affect other
applications that use the same MySQL.

B. modify ERP5 to specify character set explicitly
--------------------------------------------------

To take this way, your proposal 1) and 2) are not enough. It is also
needed to add 'DEFAULT CHARSET=utf8' at the end of *all* CREATE TABLE
statements (in portal_catalog/*/z_create_*).

With this way, you don't have to care MySQL configuration, but you have
to take care when you add new tables in ERP5.


BTW, if you are interested in multilingual fulltext search in ERP5, the
following uri might be interesting.
http://www.erp5.org/HowToUseTritonn

Regards,

[Boris Kocherov]

Post by Kazuhiko Shiozaki
B. modify ERP5 to specify character set explicitly
--------------------------------------------------
To take this way, your proposal 1) and 2) are not enough. It is also
needed to add 'DEFAULT CHARSET=utf8' at the end of *all* CREATE TABLE
statements (in portal_catalog/*/z_create_*).

?What is the reason to add ?at the end of *all* CREATE TABLE statements if
the default ?character set and collation for all tables in database is
the DATABASE character set and collation.
http://dev.mysql.com/doc/refman/5.0/en/charset-table.html

To avoid misunderstanding: i suggest to fulfill the both 1) and 2) one
after another because after execution of my patch ERP5 sends the data to
MySql in utf8.

[Kazuhiko Shiozaki]

Hi Boris,

Post by Boris Kocherov

Post by Kazuhiko Shiozaki
B. modify ERP5 to specify character set explicitly
--------------------------------------------------
To take this way, your proposal 1) and 2) are not enough. It is also
needed to add 'DEFAULT CHARSET=utf8' at the end of *all* CREATE TABLE
statements (in portal_catalog/*/z_create_*).

?What is the reason to add ?at the end of *all* CREATE TABLE statements if
the default ?character set and collation for all tables in database is
the DATABASE character set and collation.
http://dev.mysql.com/doc/refman/5.0/en/charset-table.html

"ALTER DATABASE db_name CHARACTER SET utf8" will not affect existing
tables in the database, and of course it is not a permanent solution. So
you have to invoke the SQL each time you set up a new ERP5 instance.

If we have 'DEFAULT CHARSET=utf8' in all CREATE TABLE statements, and we
apply your patch, we no longer have to care the character set
configuration of whole MySQL or each database. This should be a
permanent solution.

Post by Boris Kocherov
To avoid misunderstanding: i suggest to fulfill the both 1) and 2) one
after another because after execution of my patch ERP5 sends the data to
MySql in utf8.

Exactly, your patch guarantees that ERP5 sends the data in utf8. So if
ERP5 also guarantees that all tables are created in utf8, everything
should be fine.

[Boris Kocherov]

Post by Kazuhiko Shiozaki
Hi Boris,

Post by Boris Kocherov

Post by Kazuhiko Shiozaki
B. modify ERP5 to specify character set explicitly
--------------------------------------------------
To take this way, your proposal 1) and 2) are not enough. It is also
needed to add 'DEFAULT CHARSET=utf8' at the end of *all* CREATE TABLE
statements (in portal_catalog/*/z_create_*).

?What is the reason to add ?at the end of *all* CREATE TABLE statements if
the default ?character set and collation for all tables in database is
the DATABASE character set and collation.
http://dev.mysql.com/doc/refman/5.0/en/charset-table.html

"ALTER DATABASE db_name CHARACTER SET utf8" will not affect existing
tables in the database, and of course it is not a permanent solution.

that's why ?you have to clean and reindex catalog.

Post by Kazuhiko Shiozaki
So you have to invoke the SQL each time you set up a new ERP5 instance.
If we have 'DEFAULT CHARSET=utf8' in all CREATE TABLE statements, and we
apply your patch, we no longer have to care the character set
configuration of whole MySQL or each database. This should be a
permanent solution.

?Yes, we have ?to invoke the SQL query each time you set up a new ERP5
instance. But if i understand correctly you have a private patch for new
ERP5 instance creating so - it's easier to include the SQL query there
once than be afraid of forgetting to add text to each "Create table".

Post by Kazuhiko Shiozaki

Post by Boris Kocherov
To avoid misunderstanding: i suggest to fulfill the both 1) and 2) one
after another because after execution of my patch ERP5 sends the data to
MySql in utf8.

Exactly, your patch guarantees that ERP5 sends the data in utf8. So if
ERP5 also guarantees that all tables are created in utf8, everything
should be fine.