How to change the collation for all tables

haovn577 wrote on Friday, June 08, 2012:

Hi all,

As title, How to change the collation for all tables in phpmyadmin?
I want to convert ‘latin1_swedish_ci’ to ‘utf8_general_ci’ !
pleases help me.

Thank and Regards,
Hao Tran

yehster wrote on Friday, June 08, 2012:

MySQL doesn’t allow you to alter more than one table with a single “ALTER TABLE” query, so you are going to have to generate a separate query for each individual table.
However you can use “SHOW TABLES” to generate a list of all the tables, then “copy and paste” the appropriate ALTER TABLE stuff.
http://dev.mysql.com/doc/refman/5.0/en/show-tables.html
http://forums.mysql.com/read.php?103,246723,246978#msg-246978

sunsetsystems wrote on Friday, June 08, 2012:

There’s a tool for this that I have used before.  Would be nice to include an adaptation of it in OpenEMR, but its licensing seems self-contradictory.

http://www.phoca.cz/documents/38-tools/154-how-to-change-collation-in-database

Rod
www.sunsetsystems.com

bradymiller wrote on Friday, June 08, 2012:

Hi,

Can usually be done in two commands, but important to go through the following page to see which method is applicable to you: http://www.open-emr.org/wiki/index.php/OpenEMR_UTF-8_Upgrade_Howto
(also backup your database before doing anything)

haovn577 wrote on Tuesday, June 12, 2012:

Thank Brady,

Can you check your email to accept my request about translation openEMR into Vietnamese. I waiting for you your mail.

Thanks and Regards,
Hao Tran

bradymiller wrote on Tuesday, June 12, 2012:

Hi,
I just sent you an email with instructions. Let me know of you didn’t get it.
thanks,
-brady
OpenEMR Project

This has been a pain in my dairies.
I finally came up with a script to run and it converts 98% of the tables.
Drop this file into the interface folder and access it from the browser.
https://mysite.com/interface/upgrade_collation.php

upgrade_collation.php (728 Bytes)

The reason for the 98% is that there are some tables that won’t convert if they have illegal columns and such like. There are other that just won’t convert. No reason found yet.

UPDATE
@brady.miller
I updated the script to set the collation of the database also. When I checked back in the system we had upgraded. The collation was still set to latan1. Doing some reading on the Mysql documentation. Latan1 is the default collation if none is specified when creating the database.

Now our procedure for upgrading from v5 and below, will be to run this script before running the database upgrade script to set the collation for both the database and tables.

@juggernautsei ,
Check out this bash function for changing encoding/collation that are used in the docker dev environments:

bit more background on above, this was done so devs can test what happens when changing around encoding/collations via the following openemr-cmd command:
openemr-cmd ec <character-set> <collation>

from openemr-cmd help output: