21

I'm seeing

OperationalError (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

It looks like some of my variables are UTF8 strings

'name': 'p\xc7\x9d\xca\x87\xc9\x9f\xc4\xb1\xc9\xa5s Badge'

Is this a configuration issue? If so, how can i solve it? I'd like to handle everything in Unicode (I think).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Paul Tarjan
  • 48,968
  • 59
  • 172
  • 213

3 Answers3

47

You can change the table encoding via the shell:

$ manage.py shell
>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute('SHOW TABLES')
>>> results=[]
>>> for row in cursor.fetchall(): results.append(row)
>>> for row in results: cursor.execute('ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE     utf8_general_ci;' % (row[0]))

https://mayan.readthedocs.org/en/v0.13/faq/index.html

Roberto Rosario
  • 1,818
  • 1
  • 17
  • 31
webjunkie
  • 6,891
  • 7
  • 46
  • 43
  • 7
    This worked great! But have in mind that your database charset will remain as it was, to change it also execute: `ALTER DATABASE databasename CHARACTER SET utf8;` – Caumons Apr 11 '12 at 09:06
  • You may also need to update each table's *columns* too, not just the database and its tables. – Nick Oct 12 '15 at 21:09
  • This works great! On caveat: the extra spaces in the last line somehow messed up the command. Removing them for proper execution. – digit plumber Jun 22 '19 at 21:10
12

It appears your database is defaulted to latin1_swedish_ci, and therefore cannot accept all utf8 characters. You need to change the configuration of the MySQL database tables to use utf8_general_ci. A good blogpost about this (with links to a tool) can be found at MySQL Performance Blog

Technical Bard
  • 4,395
  • 7
  • 31
  • 32
1

You will add OPTIONS in django settings file like below:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {'charset': 'utf8mb4'},
        'NAME': 'sarpanchDb',
        'USER': 'root',
        'PASSWORD': 'tiger',
        'HOST': 'localhost',
        'PORT': '',
    },
}

Also you will need to do change in /etc/mysql/my.cnf file 

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Then restart mysql service

sudo service mysql restart

Cross check it worked or not using following query

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR 
Variable_name LIKE 'collation%';

You should get following output 

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
Vitthal Sarode
  • 89
  • 4