Shantanu's Blog

It is very important to use the same character set across all tables and acorss all databases.

The default character set used per database are listed using this query...

mysql> select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | emailplatform      | latin1                     | latin1_swedish_ci      | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
| def          | test1              | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
6 rows in set (0.01 sec)

If some of the tables from a database does not match with with other tables, we need to alter those tables. for e.g. if 1 or 2 tables from test database are using utf8 encoding, then the best choice is to drop and recreate those tables as latin1
There are 2 points to note here...
1) The table in question should not be part of foregin key relations.
2) The table should not actually contain unicode characters. Because once we convert it to latin1, there will be no way to store unicode.

The easiest way to check if the tables from a given database are using different character set is to use mysqldump command as shown here...
root@ip-10-86-106-75:/home/ubuntu# mysqldump test --no-data | grep ENGINE
) ENGINE=TokuDB DEFAULT CHARSET=latin1;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=TokuDB DEFAULT CHARSET=latin1;

This means there are a few tables with utf8 character while others are latin1.
You will have issues while joining a latin1 table with utf8 table. The query will not use indexes if the columns are of different character sets.
_____

The following query run on "information_schema" database shows that there is 1 table in test database that has utf8 collation. All other tables in test database are latin1. Therefore I need to change that single utf8 table to latin1

# create a new table in test database
create table test.tables select * from information_schema.tables;

# check for table_collations

mysql> select table_schema, table_collation, count(*) as cnt from test.tables group by table_schema, table_collation;
+--------------------+-------------------+-----+
| table_schema       | table_collation   | cnt |
+--------------------+-------------------+-----+
| emailplatform      | latin1_swedish_ci |  64 |
| information_schema | utf8_general_ci   |  45 |
| mysql              | latin1_swedish_ci |   1 |
| mysql              | utf8_bin          |   8 |
| mysql              | utf8_general_ci   |  15 |
| performance_schema | utf8_general_ci   |  17 |
| test               | latin1_swedish_ci |  18 |
| test               | utf8_general_ci   |   1 |
| test1              | latin1_swedish_ci |   1 |
+--------------------+-------------------+-----+
9 rows in set (0.00 sec)

To find the name of the table I use this query:

mysql> select table_name from tables where table_schema = 'test' and table_collation like 'utf8%';
+------------+
| table_name |
+------------+
| cdr_master |
+------------+
1 row in set (0.01 sec)

And here is the count:

mysql> select count(*) from test.cdr_master;
+----------+
| count(*) |
+----------+
|   186166 |
+----------+
1 row in set (0.04 sec)

This is relatively small table so we can quickly change the character set.
But we need to check that there is no other table linked to this using foreign key relations.

mysql> select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA='TEST' AND TABLE_NAME = 'cdr_master' LIMIT 1\G
Empty set (0.00 sec)

mysql> select * from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA='TEST' AND REFERENCED_TABLE_NAME = 'cdr_master' LIMIT 1\G
Empty set (0.06 sec)

_____

To change the default character set we need to drop and recreate the table.

Take the backup of the table.

# mysqldump --tab=/tmp/ test cdr_master

Change the character set:

# sed -i.bak 's/CHARSET=utf8/CHARSET=latin1/' /tmp/cdr_master.sql

Recreate the new table after dropping the old one:

# mysql test < /tmp/cdr_master.sql

# restore data:

mysql> load data infile '/tmp/cdr_master.txt' into table test.cdr_master;
Query OK, 186166 rows affected (25.06 sec)
Records: 186166  Deleted: 0  Skipped: 0  Warnings: 0

Now log-out and log back in to find that all the tables in test database are having the same table_collation.

mysql> select table_collation, count(*) as cnt from information_schema.tables where table_schema = 'test' group by table_collation;
+-------------------+-----+
| table_collation   | cnt |
+-------------------+-----+
| latin1_swedish_ci |  19 |
+-------------------+-----+
1 row in set (0.00 sec)

_____

In order to check if the utf8 encoded table really has any unicode characters, you need to take the backup of the table.

mysqldump test tbl_name > todel.txt

And then run this python script. If the script processes all the lines without any problem then the data is compatible with latin1.

import codecs
f = codecs.open("/tmp/todel.txt", "r", "utf-8")
for line in f.readlines():
    todel=line.decode('latin1')

You have unicode data in your table if you get an unicode error like this...

UnicodeEncodeError: 'ascii' codec can't encode characters in position 31-35: ordinal not in range(128)

In such case, you can not continue with the task of table re-creation unless you decide to ignore the unicode characters being
_____

If you want to check which lines contain unicode characters, you need another type of dump (skip extended insert option will generate a line per record in the table)

# mysqldump test todel --skip-extended-insert > todel.txt

And the following python code will display all the records where unicode characters are used.

import codecs
f = codecs.open("/tmp/todel.txt", "r", "utf-8")
for line in f.readlines():
    try: 
        todel=line.decode('latin1')
    except:
        print line

_____

If recreating the entire table is not an option, then simply change the single column to latin1.

mysql> alter table test.cdr_master modify column call_uuid varchar(50) charset latin1;

This will make the query very fast if the column "call_uuid" is used in the join query. The other table's column "call_uuid" should be also latin1.
_____

if you use extended explain then you will see what mysql is trying to do internally.

mysql> explain extended select * from a inner join b on a.column = b.column
mysql> show warnings;

The warning will show that mysql is trying to convert the b.column from utf8 to latin1 in order to match with a.column. This internal conversion will not allow mysql to use indexes.

The unicode characters are not allowed to be stored in latin1
You will get an error as shown below:

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;

insert into todel values ('हिदी' )
Error in query (1366): Incorrect string value: '\xE0\xA4\xB9\xE0\xA4\xBF...' for column 'name' at row 1

insert into todel values (convert ('हिदी' using binary));
select convert(convert(name using binary) using utf8) from todel;

The work-around is to store the record as binary and while selecting, use convert function twice as shown above.
_____

There is a better way though. Why not to use utf8 encoding for the entire table?

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=utf8;

insert into todel values ('हिदी' );
select * from todel;
_____

You think that altering the table to utf8 will solve this issue?

alter table todel default charset=utf8;

No. Because even if the default table type is now utf8, the columns are still latin1

 mysql> show create table todel;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| todel | CREATE TABLE `todel` (
  `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

alter table todel modify name varchar(100) character set utf8 ;

Now even if we have column as well as table type utf8, we still get junk characters instead of unicode.

mysql> select * from todel;
+---------------------------+
| name                      |
+---------------------------+
| हिदी              |
+---------------------------+
1 row in set (0.00 sec)

The bad news is that the convert query that was working fine earlier has now stopped working as expected.

mysql> select convert(convert(name using binary) using utf8) from todel;
+------------------------------------------------+
| convert(convert(name using binary) using utf8) |
+------------------------------------------------+
| हिदी                                   |
+------------------------------------------------+
1 row in set (0.00 sec)

The unicode data is lost in the conversion.
_____

So the correct solution would be to add a utf8 column to latin1 table and update that column data with correct unicode string.
Let's start all over again:

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;
insert into todel values (convert ('हिदी' using binary));
select convert(convert(name using binary) using utf8) from todel;

alter table todel add column hindi varchar(100) character set utf8;

update todel set hindi = convert(convert(name using binary) using utf8) ;

mysql> select convert(convert(name using binary) using utf8), hindi from todel;
+------------------------------------------------+--------------+
| convert(convert(name using binary) using utf8) | hindi        |
+------------------------------------------------+--------------+
| हिदी                                           | हिदी         |
+------------------------------------------------+--------------+
1 row in set (0.00 sec)

_____

Now the problem is that the application is not aware of this new column called "hindi" and it is still using column "name". We need an insert and update trigger to keep correcting the values in the hindi column. Something like this...

delimiter |
CREATE TRIGGER todel_bi after INSERT ON todel
  FOR EACH ROW
  BEGIN
    UPDATE todel SET hindi = convert(convert(name using binary) using utf8) WHERE id = NEW.id;
  END;
|

insert into todel (id, name) values (2, (convert ('मराठी' using binary)));

I thought the after insert trigger would solve this issue, but I get an error.

ERROR 1442 (HY000): Can't update table in trigger because it is already used by statement which invoked this stored function/trigger.

Now I need to write a stored procedure that inserts the record in the target table and change the code accordingly (that will call the procedure).
_____

So we are back where we started. Why not simply replace the name column with this new unicode aware column?

drop table todel;
create table todel (name varchar(100)) DEFAULT CHARSET=latin1;
insert into todel values (convert ('हिदी' using binary));

alter table todel add column hindi varchar(100) character set utf8;
update todel set hindi = convert(convert(name using binary) using utf8) ;
alter table todel drop column name;
alter table todel change column hindi name varchar(100) character set utf8;

Here is how you can have a Drupal site in 4 easy steps using cloud Formation service of Amazon Web Services.

1) Log in aws console and go to CloudFormation Management Console. Click on "Create Stack" button.






2) admin/ admin is the default DB user name and password. You can specify the instance type (for e.g. m1.small)




Within a few minutes the site is ready!

This however, does not give you shell access to the ec2 instance. You need to create the drupal site based on a template in order to gain the root access.

http://aws.amazon.com/cloudformation/aws-cloudformation-templates/

Use the template that says: "Single EC2 Instance with local MySQL database"
You will be allowed to specify the kye pair that can be used to connect to the ec2.

This applies to Drupal, Joomla, Wordpress etc. Amazon ec2 instances are a bit costlier than the shared hosting. But I think aws is a good choice considering the host of features offered, reliability, support and brand.

Python has excellent support for unicode.
Here is how we can declare the string to be utf8


#!/usr/bin/env python
# -*- coding: utf8 -*-
print unicode("私","UTF-8")
a = unicode("प्रभाकर", "UTF-8")
b = u"प्रभाकर"
print len(b)