How can we convert an existing database in latin1 to utf8 ?

Parisson / Telemeta

Collaborative content management system for musicology

http://telemeta.org

GNU Affero General Public License v3.0

118 stars 38 forks source link

How can we convert an existing database in latin1 to utf8 ? #159

Closed AnasGhrab closed 4 years ago

AnasGhrab commented 7 years ago

To continue with this issue : https://github.com/Parisson/Telemeta/issues/156

One could apply this method : http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion.

thomasfillon commented 7 years ago

These links may also provide a solution:

http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 ---> it seems relevant
- http://www.alphadevx.com/a/420-Converting-a-MySQL-database-from-latin1-to-utf8
- How to support full Unicode in MySQL databases

thomasfillon commented 7 years ago

This section of the Django-MySQL documentation may also be interesting : django_mysql.W003: utf8mb4

Note this does not transform the database, tables, and columns that already exist. Follow the examples in the ‘How to’ blog post link above to fix your database, tables, and character set. It’s planned to add a command to Django-MySQL to help you do this, see Issue 216.

thomasfillon commented 7 years ago

@AnasGhrab Don't you get any issue with CharField length ?

https://docs.djangoproject.com/en/1.11/ref/contrib/auth/#user-model

If you use MySQL with the utf8mb4 encoding (recommended for proper Unicode support), specify at most max_length=191 because MySQL can only create unique indexes with 191 characters in that case by default.

It deals with User but it seems to be the same for CharField as it is also mention in a open ticket here:

"InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters.

AnasGhrab commented 7 years ago

@thomasfillon still didn't notice something like that... But I generally do no use CharField with such length... What are the longuest CharFields used in Telemeta ? The Title CharField ?

thomasfillon commented 7 years ago

max_length are usually set to 250 in Telemeta I've seen max_length of 255 for mimetype

thomasfillon commented 7 years ago

This may also be very interesting: https://vinta.ws/code/create-a-custom-migration-in-django.html

AnasGhrab commented 4 years ago

An import of the database on a fresh install resolves the problem.