OperationalError when using Unicode in text fields

[Vincent Leduc]

Hi Randle,

I get an error when I use a unicode character, for example σ, in a text field. I tried to put σ in a test description and got:

OperationalError at /admin/qa/test/add/

(1366, "Incorrect string value: '\\xCF\\x83' for column 'description' at row 1")

Request Method:	POST
Request URL:	http://qatracktest3.radonc.hmr/admin/qa/test/add/
---	---
Django Version:	1.11.15
---	---
Exception Type:	OperationalError
---	---
Exception Value:	(1366, "Incorrect string value: '\\xCF\\x83' for column 'description' at row 1")
---	---
Exception Location:	/opt/virtenv/lib/python3.6/site-packages/MySQLdb/connections.py in query, line 277
---	---
Python Executable:	/opt/virtenv/bin/python
---	---
Python Version:	3.6.5
---	---

And if I try this in the test name, I get:

OperationalError at /admin/qa/test/add/

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

Request Method:	POST
Request URL:	http://qatracktest3.radonc.hmr/admin/qa/test/add/
---	---
Django Version:	1.11.15
---	---
Exception Type:	OperationalError
---	---
Exception Value:	(1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")
---	---
Exception Location:	/opt/virtenv/lib/python3.6/site-packages/MySQLdb/connections.py in query, line 277
---	---
Python Executable:	/opt/virtenv/bin/python
---	---
Python Version:	3.6.5
---	---

Note we are using MySQL. The charset for the database is utf8 and the collation utf8_general_ci.

Thank you

[Randle Taylor]

Hi Vincent,

I was able to duplicate this bug. Thanks for pointing out that you were using mysql and your charset…made it very easy to track down as it is a mysql specific issue. After a bit of googling, I found the following query:

SELECT
  `tables`.`TABLE_NAME`,
  `collations`.`character_set_name`
FROM
  `information_schema`.`TABLES` AS `tables`,
  `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations`
WHERE
  `tables`.`table_schema` = DATABASE()
  AND `collations`.`collation_name` = `tables`.`table_collation`
;

which showed all my tables as using latin1 character_set.

(As always, I highly recommend backing up your database before attempting anything like this)

After running:

ALTER TABLE qa_test convert to character set utf8 collate utf8_general_ci;

I was able to enter the σ character in the description (but not the test name because the Django admin log table was still in latin1 and it records the test name in one of its columns).

Finally, I converted all tables to UTF-8 from a shell like so:

mysql --database=dbname -u USERNAME --password=PASSWORD -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=dbname -u USERNAME --password=PASSWORD

After that everything worked ok for me.

Hope that helps.

[Vincent Leduc]

Hi Randle,

Thanks a lot for this very thorough reply! I suspected this was mysql-related after some googling too. But being not too familiar with Django, I didn’t know if there was hope of getting it to work with QATrack+.

It’s strange, for me all “qa_” tables are using latin1, except qa_sublist and qa_unittestinfochange. I’ll backup the DB and try convert all my tables.

[Vincent Leduc]

Hi Randle,

Just letting you know the conversion from latin-1 to UTF8 worked for me too. Thanks again.