convert gerrit's mysql tables to utf8

I feel like this might have been sorted, or a solution for it?

A partial solution at least. Script attached was used to test and perform the switch over to utf8 within work. I can't confirm whether there may be issues with other services accessing the same DB subsequently, but it did work as expected within Gerrit.

Only place I noticed any charset conversion issues, is within the patch_comments table. However I suspect depending on usage, the following is a more complete list of table.fields that need to be checked for conversion issues:
account_agreements.review_comments
account_group_agreements.review_comments
account_groups.description
change_messages.message
patch_comments.message

Those are the only text fields which are in any way free form. All the remaining varchar fields are restricted to what can be accepted into them due to usage.

Hi Darragh,

just wanted to say that you saved me a bit of headache before my end of workday. Works smoothly at my end. Thanks for your good work.

Regards,

Armin

Maybe we can tack this onto the Gerrit upgrade to 2.8 or 2.9. Since there will already be DB migrations.

I've run Darragh's script to convert tables on review-dev.o.o and everything seems to be ok. I ran the script after upgrading to gerrit 2.8

fungi suggested we should convert to tables on review.o.o to UTF8 before upgrading to gerrit 2.8. jeblair says we should ask mordred as well.

I tested the utf8 conversion from a mysql dump of the review.o.o data. I found that the existing tables are in 'latin1_general_cs' collation. This is a case sensitive collation which would convert well to utf8_general_cs collation however utf8_general_cs collation is NOT available for mysql (http://bugs.mysql.com/bug.php?id=65830). Converting to utf8_general_ci will not work because there are tables that contain duplicate (case insensitive) data so when attempting to insert the data back into the database duplicate entry errors occur. The other option is to convert to utf8_bin collation which will convert just fine. However the order by clause for utf8_bin returns an unnatural order ( https://stackoverflow.com/questions/5526334/what-effects-does-using-a-binary-collation-have) so I'm not sure whether it's safe to use utf8_bin collation.

We discussion this topic and have agreed to convert to utf8_bin collation. log of the discussion at http://eavesdrop.openstack.org/meetings/infra/2014/infra.2014-03-04-19.01.log.html

postpone fix until after gerrit upgrade bug #1082781

Neither I nor pelix can reproduce the problem reported in upstream issue 1101 on review.o.o which runs gerrit 2.8. I think it's probably something to do with the new lucene search backend that's now in ver 2.8. I do not think we need to go thru the trouble of converting to utf8 until we can identify a real problem with the current collation.

Ignore the previously attached db conversion scripts. I have modified the script further, the latest version is on my github account [1]. I have used it to test db migration to utf8 using openstack's production data. It was tested with MySQL ver 5.5.41-0ubuntu0.12.04.1

[1] https://github.com/zaro0508/gerrit-db-migration