Plots2: Encoding issues with migrating old DrupalUser accounts to User records

[jywarren]

We're seeing an issue with a recent migration https://github.com/publiclab/plots2/pull/2157 which tries to mitigate User vs DrupalUser status records. I tried to fix it here: https://github.com/publiclab/plots2/pull/2208 but we have a new problem.

I believe it's due to this error from the Jenkins build log:

ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT  `rusers`.* FROM `rusers`  WHERE `rusers`.`username` = 'トリーバーチ クラッチ'  ORDER BY `rusers`.`id` ASC LIMIT 1

The username in question is: トリーバーチ クラッチ

Can we convert the string or otherwise mitigate when a username is ... not utf-8?

[jywarren]

I think i can get past this by not matching User and DrupalUser by name/username, but instead by id/uid (did this in https://github.com/publiclab/plots2/pull/2210). But we will have to solve this if we ever want to convert /all/ DrupalUsers into Users, with the drupal_user.migrate command:

https://github.com/publiclab/plots2/blob/b73d598262f2132a57d4cd3f49f5331edafc8265/app/models/drupal_user.rb#L180-L193

migrate will break due to the above encoding problem. Once we fix that issue, we could ensure there are no "orphaned" DrupalUsers left with:

DrupalUser.all.each do |du|
  du.migrate
end

https://stackoverflow.com/questions/17022394/how-to-convert-a-string-to-utf8-in-ruby

[jywarren]

That'll get us one step closer to deprecating DrupalUser! #956

[ViditChitkara]

Hmm, didn't anticipate this problem. Will submit a patch for this!

[jywarren]

I went ahead and fixed the issue for the purposes of your migration -- no
problem! i should've seen this too. But I do think the future desire to
stop using DrupalUsers entirely, for which we'll need this "migrate"
method. Interested in working on that a bit? Thanks!!

On Sat, Feb 3, 2018 at 4:54 AM, Vidit [e-mail] wrote:

Hmm, didn't anticipate this problem. will submit a patch for this!

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/publiclab/plots2/issues/2209#issuecomment-362794719,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABfJ_6BAdUsmVbM76cmFjiCLZV14x2rks5tRCzagaJpZM4R31Rw
.

[ViditChitkara]

Yes, interested! The following needs to be done:-
1) Modify the migrate method to accept トリーバーチ クラッチ this type of a string
2) Write a migration to ensure we have a user for every drupal user.

If this is good, I'll be happy to give it a go.

[jywarren]

yes, fantastic!!! :-)

On Sun, Feb 4, 2018 at 7:10 AM, Vidit [e-mail] wrote:

Yes, interested! Finally now the following needs to be done:-
[ ] Modify the migrate method to accept トリーバーチ クラッチ this type of a string
[ ] Write a migration to ensure we have a user for every drupal user.
If this is good, I'll be happy to give it a go.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/publiclab/plots2/issues/2209#issuecomment-362902101,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABfJ-tO1WclqqsLse0jHcB2Tl9-c5PXks5tRZ49gaJpZM4R31Rw
.

[icarito]

Here's some info on the utf8 situation in MariaDB / MySQL - we should actually switch to utf8mb4

[jywarren]

Let's make a unique issue for that - this fix should work for now!

[jywarren]

OK - we ended up with an error as documented here: https://github.com/publiclab/plots2/issues/2846

So, should give this another try! Thanks though Vidit! We'll get it i'm sure.

[jywarren]

OK, given that we had trouble, let's go forward with @icarito's suggestion of migrate character sets to utf8mb4 -- we'll have to do that in a migration too!

[jywarren]

This was solved!