757565 - Wrong charsets on several tables

Status: VERIFIED FIXED

(Participation Infrastructure :: Phonebook, defect)

Description

[James Socol [:jsocol, :james]]

This may be a duplicate of a traceback bug, but we've got a couple tables with latin1 charsets.

* groups_skill (column: name)
* profile_skills (no text columns)
* thumbnail_kvstore (column: value)

Comment 1

[James Socol [:jsocol, :james]]

See also bug 757567.

Comment 2

[Sheeri Cabral [:sheeri]]

The database itself should be set to utf8 when it's created, for example:

CREATE DATABASE db_name
DEFAULT CHARACTER SET utf8
DEFAULT COLLATION utf8_ci;

(if that's what you want, a case-insensitive utf8 collation. I think there's also a case sensitive utf8-collation (utf8-cs) and I know there's a case-sensitive binary collation (utf8-bin). A binary collation is one that will preserve everything, even trailing spaces in a varchar or text field.

Comment 3

[Sheeri Cabral [:sheeri]]

mysql> show create database mozillians_dev_allizom_org;
+----------------------------+---------------------------------------------------------------------------------------+
| Database                   | Create Database                                                                       |
+----------------------------+---------------------------------------------------------------------------------------+
| mozillians_dev_allizom_org | CREATE DATABASE `mozillians_dev_allizom_org` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------------------------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER DATABASE mozillians_dev_allizom_org DEFAULT CHARACTER SET utf8 ;
Query OK, 1 row affected (0.03 sec)

mysql> show create database mozillians_dev_allizom_org;
+----------------------------+-------------------------------------------------------------------------------------+
| Database                   | Create Database                                                                     |
+----------------------------+-------------------------------------------------------------------------------------+
| mozillians_dev_allizom_org | CREATE DATABASE `mozillians_dev_allizom_org` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now any new tables created in this database will be utf8 by default.

Comment 4

[Sheeri Cabral [:sheeri]]

[[e-mail]1 ~]# time mysqldump --no-data mozillians_dev_allizom_org > schema.sql

real    0m0.069s
user    0m0.007s
sys     0m0.005s
[[e-mail]1 ~]# time mysqldump --no-create-info --default-character-set=latin1 mozillians_dev_allizom_org > data.sql

real    0m0.278s
user    0m0.113s
sys     0m0.024s
[[e-mail]1 ~]# vi schema.sql 

-- here is where I edited the table to have the proper charset


[[e-mail]1 ~]# mysql mozillians_dev_allizom_org < schema.sql 
[[e-mail]1 ~]# mysql mozillians_dev_allizom_org < data.sql

Comment 5

[Sheeri Cabral [:sheeri]]

Working on stage now:

[[e-mail]1 ~]# time mysqldump --no-data mozillians_stage > schema.sql 

real    0m0.067s
user    0m0.005s
sys     0m0.006s
[[e-mail]1 ~]# vi schema.sql 
[[e-mail]1 ~]# time mysqldump --no-create-info --default-character-set=latin1 mozillians_stage > data.sql 

real    0m0.113s
user    0m0.029s
sys     0m0.013s

[[e-mail]1 ~]# mysql mozillians_stage < schema.sql 
[[e-mail]1 ~]# time mysql mozillians_stage < data.sql 

real    0m2.385s
user    0m0.026s
sys     0m0.007s

Comment 6

[Sheeri Cabral [:sheeri]]

Left to do: production. This should take about 5 mins or less.

Comment 7

[James Socol [:jsocol, :james]]

-dev is down. Let's investigate tomorrow before we greenlight production.

Production target is Monday the 28th. It's a holiday so traffic should be low. Sheeri and I will find a time.

Comment 8

[Sheeri Cabral [:sheeri]]

James - what do you mean by "down"?

Comment 9

[James Socol [:jsocol, :james]]

(In reply to Sheeri Cabral [:sheeri] from comment #8)
> James - what do you mean by "down"?

When I made that comment, the site wasn't loading. Now it is. Must've been a restart or other transient effect.

Comment 10

[Sheeri Cabral [:sheeri]]

phew!

Comment 11

[Sheeri Cabral [:sheeri]]

[[e-mail]1 ~]# mysqldump --no-data mozillians_org > schema.sql
[[e-mail]1 ~]# mysqldump --no-create-info --default-character-set=latin1 mozillians_org > data.sql
[[e-mail]1 ~]# vi schema.sql 

#### changed latin1 to utf8

[[e-mail]1 ~]# mysql mozillians_org < schema.sql 
[[e-mail]1 ~]# time mysql mozillians_org < data.sql

real    0m9.248s
user    0m0.187s
sys     0m0.015s

Comment 12

[Matt Brandt [:mbrandt]]

Bumpiing to verified as [qa-]