Preserve collation when changing column in MySQL

[fatkodima]

Fixes #45742.

[byroot]

LGTM. CI is super spotty today though, I restarted it.

[simi]

💪

[byroot]

Backported to 7-0-stable as d60d058

[ahoglund]

I'm getting this error:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'

When the change_column is converting from a string type to binary type:

def up
  change_column :column_name, :path, :binary, limit: 1024
end

def down
  change_column :column_name, :path, :string
end

This is because the collation is being preserved when in this case it shouldn't be. According to the MySQL docs the binary type has only one collation also named binary.

I am not sure if there are other special cases that need to be considered like this for column changes. But it seems to me this should be accounted for in code so that migrations don't need to be updated to pass in the collation: "binary" arg to get this working.