1201087 - Change default collation on treeherder databases

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Mauro Doglio [:mdoglio]]

Hi,
In the treeherder databases most of the tables have a utf_bin collation. We would love to not have to specify it every time we want to add a new table or a new varchar column.
Could you please:
  - Set the db default collation to utf8_bin.
  - Set the existing tables default collation to utf8_bin.

We don't need to apply this change to the databases created by the application (they all look like *_jobs_1) but only the ones called treeherder_[stage|prod].

Thanks!

Comment 1

[Ed Morley [:emorley]]

Please can you CC the treeherder watch users when creating bugs outside of the treeherder component (unless they block a bug already in a treeherder component). It makes sure if any stage/prod issues occur due to these changes, then people component watching know about them to put 2+2 together :-) That said, I'm guessing this is for bug 1194221 - so marking this blocking that too.

(In reply to Mauro Doglio [:mdoglio] from comment #0)
> We don't need to apply this change to the databases created by the
> application (they all look like *_jobs_1) but only the ones called
> treeherder_[stage|prod].

The database on treeherder stage (treeherder[12].stage.db.scl3.mozilla.com) is called `treeherder_stage`, however for prod (treeherder[12].db.scl3.mozilla.com) it's called `treeherder` (no suffix).

Comment 2

[Sheeri Cabral [:sheeri]]

I'm confused because bug 1194221 seems to have the treeherder folks making scripts to do this (well, to go from utf8_bin to utf8_general_ci, which failed, and then it was decided to change the utf8_general_ci tables to utf8_bin). 

Are you saying you want us to do this in stage first, then prod? Just trying to make sense of it all, and be explicit.

Comment 3

[Sheeri Cabral [:sheeri]]

OH wait, I get it now, just the default for the server/db. Will do.

Comment 4

[Sheeri Cabral [:sheeri]]

updated puppet module to handle custom collations - r107786.

Comment 5

[Sheeri Cabral [:sheeri]]

updated puppet module so backups can handle custom collations - r107790.

updated puppet configs so treeherder servers have custom collation of utf8_bin - r107791.

Comment 6

[Sheeri Cabral [:sheeri]]

After puppet run, /etc/my.cnf is changed:

[[e-mail]3 ~]# pt-config-diff /etc/my.cnf localhost
1 config difference
Variable                  /etc/my.cnf treeherder1.stage.db.scl3.mozilla.com
========================= =========== =====================================
collation_server          utf8_bin    utf8_general_ci

Changing on db server:
mysql> set global collation_server='utf8_bin';
Query OK, 0 rows affected (0.00 sec)

Verifying it works:

[[e-mail]3 ~]# pt-config-diff /etc/my.cnf localhost
[[e-mail]3 ~]# 

Done the same on treeherder2.stage.

Will have to go through each database and change it, e.g.

alter database dbname COLLATE='utf8_bin';

Did you want this on all dbs?

After that's done, will have to repeat on production.

Comment 7

[Sheeri Cabral [:sheeri]]

updating, bug 1194221 has an ALTER DATABASE so I'll leave that to you unless you say otherwise.

Comment 8

[Mauro Doglio [:mdoglio]]

I altered both treeherder and treeherder_stage to set the default collation to utf8_bin. We are done here.
Thanks :sheeri!