0

I am reading though this article: https://railsmachine.com/articles/2017/05/19/converting-a-rails-database-to-utf8mb4.html

... we have converted several large databases to utf8mb4 with no downtime and no data loss from truncation.

What does he mean by data loss truncation? What I want to do is to change the table to utf8mb4 and that inforce me to change the index limit to 191. So in my dev env what I did is:

  • removing the index (old varchar(255))
  • convert the table to use utf8mb4 charset
  • recreate the index with limit of 191

Does changing the index size will cause to losing any real data from my database?

MIA
  • 373
  • 3
  • 18
  • 3
    No, changing the index size will not change data in the table. Changing the table itself can when you convert to a different character set that is wider, however. – Ken White Apr 10 '19 at 23:14
  • Thanks! @KenWhite, I am converting charset from utf8 to utf8mb4? – MIA Apr 10 '19 at 23:18
  • Can remember the version numbers but I seem to remember that upgrading MySQL will allow larger key size to cope with multi byte char sets – RiggsFolly Apr 10 '19 at 23:20
  • 1
    https://stackoverflow.com/q/30074492/62576 explains the difference between the two character sets. utf8mb4 can be wider than utf8, which means if your columns are not wide enough to accomodate any extra space needed you can lose data (the content is wider than the allocated storage, so the extra is lost). – Ken White Apr 10 '19 at 23:20
  • 1
    Keep in mind a unique index with a 191 limit has a different meaning to a unique over the full column. In a lot of practical cases this won't matter. An alternative may be to set [innodb_large_prefix](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) – danblack Apr 10 '19 at 23:25
  • How should I know that my columns are wide enough before converting? Thanks aging. Really appreciate you help. – MIA Apr 10 '19 at 23:36
  • 1
    Newer versions of MySQL don't need this smaller index, they can index the whole thing. Up until MySQL 5.6 (ish?) there was a hard cap on how many characters could be indexed and `utf8mb4` was too much for a 255-length column. – tadman Apr 11 '19 at 01:24

0 Answers0