ara
ara copied to clipboard
ansible-community
error 500 when using MySQL: django.db.utils.OperationalError: (1366, "Incorrect string value
What is the issue ?
I noticed this issue when running Ansible's integration tests here. Strangely, I didn't see this occur when testing kolla-ansible or openstack-ansible:
2020-09-19 21:11:40,083 ERROR django.request: Internal Server Error: /api/v1/playbooks
Traceback (most recent call last):
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1366, "Incorrect string value: '\\xC5\\x9A\\xC3\\x8C\\xCE\\xB2...' for column `ara`.`playbooks`.`path` at row 1")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner
response = get_response(request)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/core/handlers/base.py", line 115, in _get_response
response = self.process_exception_by_middleware(e, request)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/core/handlers/base.py", line 113, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
return view_func(*args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/viewsets.py", line 114, in view
return self.dispatch(request, *args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/views.py", line 505, in dispatch
response = self.handle_exception(exc)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/views.py", line 465, in handle_exception
self.raise_uncaught_exception(exc)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
raise exc
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/views.py", line 502, in dispatch
response = handler(request, *args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/mixins.py", line 19, in create
self.perform_create(serializer)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/mixins.py", line 24, in perform_create
serializer.save()
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/serializers.py", line 213, in save
self.instance = self.create(validated_data)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/rest_framework/serializers.py", line 932, in create
instance = ModelClass._default_manager.create(**validated_data)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/query.py", line 422, in create
obj.save(force_insert=True, using=self.db)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/ara/api/models.py", line 51, in save
return super(Duration, self).save(*args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/base.py", line 741, in save
force_update=force_update, update_fields=update_fields)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/base.py", line 779, in save_base
force_update, using, update_fields,
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/base.py", line 870, in _save_table
result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/base.py", line 908, in _do_insert
using=using, raw=raw)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/query.py", line 1186, in _insert
return query.get_compiler(using=using).execute_sql(return_id)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/models/sql/compiler.py", line 1335, in execute_sql
cursor.execute(sql, params)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/home/fedora/.ara/virtualenv/lib64/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xC5\\x9A\\xC3\\x8C\\xCE\\xB2...' for column `ara`.`playbooks`.`path` at row 1")
127.0.0.1 - - [19/Sep/2020:21:11:40 +0000] "POST /api/v1/playbooks HTTP/1.0" 500 27 "-" "ara-http-client_1.5.0.0rc1"
This could be a manifestation of the kind of issues we see with paths and funny characters in them: https://github.com/ansible-community/ara/issues/48
What should be happening ?
It should work.
Yeah... paths with funny characters :(
From the perspective of the callback:
I guess we should be able to reproduce the issue with an integration test and the -ÅÑŚÌβŁÈ/string.
Ran a playbook with that path and it worked ¯\(ツ)/¯
Need to investigate some more but I'm not considering this a blocker for 1.5.0.
Running a playbook from /tmp/-Ã
ÃÅÃβÅÃ/playbook.yml worked fine with sqlite and postgresql but raised the exception in the original bug report when using mysql.
There seems to be a certain amount of literature on this exception with MySQL including but not limited to:
- https://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django
- https://stackoverflow.com/questions/41918241/django-unable-to-save-unicode-string-in-mysql-operationalerror-1366-incorre
- https://stackoverflow.com/questions/63141740/django-db-utils-operationalerror-1366-incorrect-string-value-django-mysql
From the looks of it, we'd need to set:
# [...]
DATABASE_ENGINE: django.db.backends.mysql
DATABASE_OPTIONS:
charset: "utf8mb4"
# [...]
And then make sure the database and tables are in the right charset.
In the case of our existing mysql database (for api.demo.recordsansible.org) it was created with:
MariaDB [(none)]> show variables like 'char%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]> show create database ara;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| ara | CREATE DATABASE `ara` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [ara]> show create table playbooks;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| playbooks | CREATE TABLE `playbooks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created` datetime(6) NOT NULL,
`updated` datetime(6) NOT NULL,
`started` datetime(6) NOT NULL,
`ended` datetime(6) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`ansible_version` varchar(255) NOT NULL,
`status` varchar(25) NOT NULL,
`arguments` longblob NOT NULL,
`path` varchar(255) NOT NULL,
`duration` bigint(20) DEFAULT NULL,
`controller` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1703 DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
I'll try changing the charset when I have a chance -- otherwise it sounds like a workaround would be to use postgresql or sqlite for now.
With mariadb-server-10.3.27 on centos8 I'm able to reproduce the issue using charset latin1 and collation latin1_swedish_ci but it works fine using utf8 as charset and utf8_general_ci as collation without needing to fiddle with DATABASE_OPTIONS or using utf8mb4.
I wasn't able to figure out the right way to change the charset/collation on the database as well as the tables without causing a range of issues.
What seems to work is to use django to dump the data (instead of mysqldump) and then re-import it:
- run
ara-manage dumpdata -o dump.jsonfrom the app where the database is in latin1 - create a new database with utf8/utf8_general_ci, run
ara-manage migrate - run
ara-manage loaddata dump.json