Forums

Incorrect String Value in MySQL database

I am switching from a sqlite databse to pythonanywhere's mysql database. Many of the fields in the sqlite database have emojis or chinese characters. When loading the fixture, I get the following error: "Could not load podcast_feed.PodcastFeed(pk=8): (1366, "Incorrect string value: '\xF0\x9F\x94\xB1U n...' for column 'title' at row 1")"

I have tried to switch from utf8 to utfmb4 with the following:

ALTER DATABASE <databasename> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Why am I getting the error?

deleted-user-5506869 | 4 posts | Oct. 6, 2019, 6:39 a.m. | permalink

Could you send us a full traceback? What python version are you running it with?

Staff fjl | 4599 posts | PythonAnywhere staff | Oct. 6, 2019, 4:28 p.m. | permalink

I'm using Python 3.7. Here's the traceback: 

Traceback (most recent call last):
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x94\\xB1Un...' for column 'title' at row 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/commands/loaddata.py", line 72, in handle
    self.loaddata(fixture_labels)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/commands/loaddata.py", line 114, in loaddata
    self.load_label(fixture_label)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/management/commands/loaddata.py", line 181, in load_label
    obj.save(using=self.using)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/core/serializers/base.py", line 223, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/models/base.py", line 779, in save_base
    force_update, using, update_fields,
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/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/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/models/base.py", line 908, in _do_insert
    using=using, raw=raw)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/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/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1335, in execute_sql
    cursor.execute(sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/sentry_sdk/integrations/django/__init__.py", line 468, in execute
    output = self.handle(*args, **options)
    return real_execute(self, sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in e
xecute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _
execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _
execute
    return self.cursor.execute(sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _
execute
    return self.cursor.execute(sql, params)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71,
 in execute
    return self.cursor.execute(query, args)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/home/jkstudios/.virtualenvs/podmatch-virtualenv/lib/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: Problem installing fixture '/home/jkstudios/podmatch/datadump.json': Could not load podcast_fe
ed.PodcastFeed(pk=8): (1366, "Incorrect string value: '\\xF0\\x9F\\x94\\xB1Un...' for column 'title' at row 1")

[edit by admin: formatting]

deleted-user-5506869 | 4 posts | Oct. 6, 2019, 7:02 p.m. | permalink

When you updated the character set in the database, did you also change your connection options to match in your settings.py? This Stack Overflow answer has the correct setting.

Staff giles | 12074 posts | PythonAnywhere staff | Oct. 7, 2019, 11 a.m. | permalink

I changed the options setting to:

        'OPTIONS': {
        'charset': 'utf8mb4',
        'sql_mode': 'traditional',
    }

This stopped the Incorrect String Value error. However, I am now seeing this error: django.db.utils.DataError: Problem installing fixture '/home/jkstudios/podmatch/datadump.json': Could not load podcast_feed .PodcastFeed(pk=14486): (1406, "Data too long for column 'producer' at row 1").

I've tried changing the column type to VARCHAR(191), TEXT and LONGTEXT. None have worked.

deleted-user-5506869 | 4 posts | Oct. 7, 2019, 3:55 p.m. | permalink

The utf8mb4 character set can take up to 3 bytes per character and the length of an entry is defined in bytes, so you may be setting the varchar too small. I'm a bit surprised that the LONGTEXT didn't fix it. Are you sure you applied it to the correct database and to the correct column (in this case it would be the column called producer)? Also, if you're applying the column change directly without using django migrations, then it's possible that Django adds constraints that are separate from the column type definition.

Staff glenn | 9718 posts | PythonAnywhere staff | Oct. 7, 2019, 6:11 p.m. | permalink

You're right, I just changed the image column and the producer column. It worked after changing the producer column to LONGTEXT. Thanks!

deleted-user-5506869 | 4 posts | Oct. 8, 2019, 11:38 p.m. | permalink

Excellent -- glad we could get to the bottom of this!

Staff giles | 12074 posts | PythonAnywhere staff | Oct. 9, 2019, 1:28 p.m. | permalink