Picking Collation Confusion

First off... I've read chapter 9.1.3 on character sets and collations
and I'm still confused... :) (that could just be because today is
Friday)

Our application is installed at several different sites some running
on Mac OS, some Windows and a few Linux which I suspect is what led to
this situation.

To deploy our app we basically do the following...

1. "create and test"
2. (on test server) mysqldump > export.sql
3. (on deployment server) mysql < export.sql

Now I need to move a set of changes from the test server to the
deployment server and I'm using mysqldiff to find the differences.

I've noticed that 90% of the changes are simply to align the collation
of fields and default collations for tables. Usually it's bouncing
between utf8_general_ci and latin1_swedish_ci.

99.99% of the records in our various customers databases will be
"normal" U.S. names and addresses but I know of a few customers that
target their base fairly narrowly and might POTENTIALLY need to enter
"foreign" names with accents and the like.

Ultimately what it comes down to is... how worried should I be about
making collations "universal" across at least a given customers
instances of the application? (If not all copies of the application
for all customers)

I already have a routine that I call "normalize database" that makes
sure default indexes are applied, etc... so it would be "easy" to add
to that routine to check for and "correct" collations but then do I
need to worry about existing data?

Thanks for the advice!

Matt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Picking Collation Confusion

Hi Matt,

You need to worry about consistent collations if you want consistent behavi=
or for sorting and comparing fields. That sounds pretty important to me.

Note that latin1 can hold accented characters as well.

Regards,
Gavin Towey

-----Original Message-----
From: Matt Neimeyer [mailto:matt@neimeyer.org]
Sent: Friday, August 14, 2009 9:52 AM
To: mysql@lists.mysql.com
Subject: Picking Collation Confusion

First off... I've read chapter 9.1.3 on character sets and collations
and I'm still confused... :) (that could just be because today is
Friday)

Our application is installed at several different sites some running
on Mac OS, some Windows and a few Linux which I suspect is what led to
this situation.

To deploy our app we basically do the following...

1. "create and test"
2. (on test server) mysqldump > export.sql
3. (on deployment server) mysql < export.sql

Now I need to move a set of changes from the test server to the
deployment server and I'm using mysqldiff to find the differences.

I've noticed that 90% of the changes are simply to align the collation
of fields and default collations for tables. Usually it's bouncing
between utf8_general_ci and latin1_swedish_ci.

99.99% of the records in our various customers databases will be
"normal" U.S. names and addresses but I know of a few customers that
target their base fairly narrowly and might POTENTIALLY need to enter
"foreign" names with accents and the like.

Ultimately what it comes down to is... how worried should I be about
making collations "universal" across at least a given customers
instances of the application? (If not all copies of the application
for all customers)

I already have a routine that I call "normalize database" that makes
sure default indexes are applied, etc... so it would be "easy" to add
to that routine to check for and "correct" collations but then do I
need to worry about existing data?

Thanks for the advice!

Matt

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg