Return of emoji / utf8mb4 error in Palm - /api/edx_proctoring/v1/user_onboarding/status issue?

ABC · July 14, 2024, 10:50am

After upgrading to Palm, I’ve fixed most of the issues, but a student just brought up that two classes out of 20 are not having their course outline expanding correctly. I can see all the content is still there when I view the class in studio, but for students they just see no sub-sections when expanding a class. (I tried just editing something in one of the classes and publishing, but that didn’t make a difference, the error persists.)

The following HTTP 404 errors appear in the logs on my tutor-based instance:

caddy-1              | {"level":"error","ts":1720953424.9592848,"logger":"http.log.access.log0","msg":"handled request","request":{"remote_ip":"123.123.123.123","remote_port":"58740","proto":"HTTP/2.0","method":"GET","host":"mysite.com","uri":"/api/edx_proctoring/v1/user_onboarding/status?is_learning_mfe=true&course_id=course-v1%3AORGNAME%2BCLASSNAME%2BV1&username=AdminUser1","tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"h2","server_name":"mysite.com"}},"user_id":"","duration":0.069601494,"size":67,"status":404}
lms-1                | [pid: 6|app: 0|req: 39/135] 172.18.0.3 () {62 vars in 3552 bytes} [Sun Jul 14 10:37:04 2024] GET /api/edx_proctoring/v1/user_onboarding/status?is_learning_mfe=true&course_id=course-v1%3AORGNAME%2BCLASSNAME%2BV1&username=AdminUser1 => generated 67 bytes in 69 msecs (HTTP/1.1 404) 9 headers in 442 bytes (1 switches on core 0)

So does anyone know what could cause 404 errors for /api/edx_proctoring/v1/user_onboarding/status?is_learning_mfe=true&course_id=course-v1%3AORGNAME%2BCLASSNAME%2BV1&username=AdminUser1?

Note: To eliminate the possibility that this is something to do with the indigo theme I’m using, I disabled the theme, but the error continues to happen.

ABC · July 14, 2024, 1:07pm

@Abdess this seems to apparently be the reintroduction of the MySQL emoji issue after I upgraded a Lilac instance to Palm. Which is really weird, since I think I did everything the same as a Nutmeg upgrade to Palm, which is working fine. Can you confirm these are the correct steps from here (since the steps seem to be spread across multiple threads):

Backup & edit database definition to find and replace things with utf8mb4:

docker exec tutor_local_mysql_1 /usr/bin/mysqldump -Q -d -uroot -p`echo $(tutor config printvalue MYSQL_ROOT_PASSWORD)` --default-character-set=utf8 --skip-set-charset openedx | sed 's/utf8/utf8mb4/gi' | sed 's/utf8mb4mb4/utf8mb4/gi' | sed 's/utf8mb4mb3/utf8mb4/gi' > openedx_schema.sql
docker exec tutor_local_mysql_1 /usr/bin/mysqldump -Q --insert-ignore -t -uroot -p`echo $(tutor config printvalue MYSQL_ROOT_PASSWORD)` --default-character-set=utf8 --skip-set-charset openedx > openedx_data.sql

(Note: One time I got an error on import about utf8mb4mb3 and I found only 4 instances of it in the openedx_schema.sql above. I’m not sure how that occurred, other than that perhaps some table has changed to being defined as utf8mb3 and the original regex didn’t account for it? So I added the extra sed 's/utf8mb4mb3/utf8mb4/gi' just in case.)

Drop existing database and recreate with new collation

tutor local stop
tutor local start mysql -d
tutor local run mysql bash
mysql -uroot -pPASSWORD --host "mysql" --port 3306
DROP DATABASE openedx;
create database openedx default charset utf8mb4 collate utf8mb4_0900_ai_ci;
exit
exit

Re-import data:

cat ~/openedx_schema.sql | docker exec -i tutor-local-mysql-1 /usr/bin/mysql -u root --password=`echo $(tutor config printvalue MYSQL_ROOT_PASSWORD)` openedx
cat ~/openedx_data.sql | docker exec -i tutor-local-mysql-1 /usr/bin/mysql -u root --password=`echo $(tutor config printvalue MYSQL_ROOT_PASSWORD)` openedx

Install & enable your tutor plugin to fix tutor launch of mysqld:

tutor plugins install https://gist.githubusercontent.com/Abdess/3ed9ed1d42821d00a5cf2481870df26f/raw/tutor-mysql8utf8mb4.yml
tutor plugins enable mysql8utf8mb4
tutor config save
tutor local launch

(Note: the post I’m citing mentioned something about changing .local/lib/python3.10/site-packages/tutor/templates/apps/openedx/config/partials/auth.yml to have DATABASES[‘default’][‘OPTIONS’][‘charset’] = ‘utf8mb4’, however I never did that on my Nutmeg → Palm upgrade and it seems to be working fine, so I don’t think that’s required?)

I did all that before I did the upgrade to Palm, but apparently I hadn’t tested my two emoji-in-title–using classes. I then re-did these steps on a copy of my Palm server, but they don’t fix things fully, and I still get errors like:

django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\xA5\\xB7CV...' for column 'display_name' at row 1")

So I know it’s still the 4-byte-encoding/emoji error . So I’m hoping you can spot a step I missed? (Or do you know how I can check the status of tables to confirm they got correctly migrated to using utf8mb4?)

ABC · July 21, 2024, 5:34pm

Based on the following error message which I can now reproduce reliably if I change something about a class’ Advanced Settings, I now think this has to do with Django not knowing how to connect to the db as utf8mb4. But I don’t know how to force Django to connect correctly on Palm, since there’s conflicting steps listed on other threads.

cms-worker-1         | [2024-07-21 16:22:15,710: INFO/ForkPoolWorker-1] Replacing CourseOutline for course-v1:MYORG+MYCLASS+V1 (version 669d34c1508444f20f6e1b84, 40 sequences)
cms-worker-1         | [2024-07-21 16:22:15,719: INFO/ForkPoolWorker-1] Found CourseContext for course-v1:MYORG+MYCLASS+V1, updating...
cms-worker-1         | [2024-07-21 16:22:15,901: ERROR/ForkPoolWorker-1] cms.djangoapps.contentstore.tasks.update_outline_from_modulestore_task[9dc63219-231f-4a2c-8ddd-8dbd889356f3]: Could not create course outline for course course-v1:MYORG+MYCLASS+V1
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 581, in get_or_create
cms-worker-1         |     return self.get(**kwargs), False
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get
cms-worker-1         |     raise self.model.DoesNotExist(
cms-worker-1         | openedx.core.djangoapps.content.learning_sequences.models.LearningSequence.DoesNotExist: LearningSequence matching query does not exist.
cms-worker-1         | 
cms-worker-1         | During handling of the above exception, another exception occurred:
cms-worker-1         | 
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
cms-worker-1         |     return self.cursor.execute(query, args)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
cms-worker-1         |     res = self._query(query)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
cms-worker-1         |     db.query(q)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
cms-worker-1         |     _mysql.connection.query(self, query)
cms-worker-1         | MySQLdb.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\xA7\\xA0Br...' for column 'title' at row 1")
cms-worker-1         | 
cms-worker-1         | The above exception was the direct cause of the following exception:
cms-worker-1         | 
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/edx-platform/cms/djangoapps/contentstore/tasks.py", line 723, in update_outline_from_modulestore_task
cms-worker-1         |     update_outline_from_modulestore(course_key)
cms-worker-1         |   File "/openedx/edx-platform/cms/djangoapps/contentstore/outlines.py", line 385, in update_outline_from_modulestore
cms-worker-1         |     replace_course_outline(course_outline_data, content_errors=content_errors)
cms-worker-1         |   File "/opt/pyenv/versions/3.8.15/lib/python3.8/contextlib.py", line 75, in inner
cms-worker-1         |     return func(*args, **kwds)
cms-worker-1         |   File "/openedx/edx-platform/openedx/core/djangoapps/content/learning_sequences/api/outlines.py", line 406, in replace_course_outline
cms-worker-1         |     _update_sequences(course_outline, course_context)
cms-worker-1         |   File "/openedx/edx-platform/openedx/core/djangoapps/content/learning_sequences/api/outlines.py", line 474, in _update_sequences
cms-worker-1         |     LearningSequence.objects.update_or_create(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
cms-worker-1         |     return getattr(self.get_queryset(), name)(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 608, in update_or_create
cms-worker-1         |     obj, created = self.select_for_update().get_or_create(defaults, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 588, in get_or_create
cms-worker-1         |     return self.create(**params), True
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 453, in create
cms-worker-1         |     obj.save(force_insert=True, using=self.db)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/model_utils/models.py", line 38, in save
cms-worker-1         |     super().save(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 739, in save
cms-worker-1         |     self.save_base(using=using, force_insert=force_insert,
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 776, in save_base
cms-worker-1         |     updated = self._save_table(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 881, in _save_table
cms-worker-1         |     results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 919, in _do_insert
cms-worker-1         |     return manager._insert(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
cms-worker-1         |     return getattr(self.get_queryset(), name)(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1270, in _insert
cms-worker-1         |     return query.get_compiler(using=using).execute_sql(returning_fields)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
cms-worker-1         |     cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
cms-worker-1         |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
cms-worker-1         |     return executor(sql, params, many, context)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
cms-worker-1         |     raise dj_exc_value.with_traceback(traceback) from exc_value
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
cms-worker-1         |     return self.cursor.execute(query, args)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
cms-worker-1         |     res = self._query(query)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
cms-worker-1         |     db.query(q)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
cms-worker-1         |     _mysql.connection.query(self, query)
cms-worker-1         | django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\xA7\\xA0Br...' for column 'title' at row 1")
cms-worker-1         | [2024-07-21 16:22:15,906: ERROR/ForkPoolWorker-1] Task cms.djangoapps.contentstore.tasks.update_outline_from_modulestore_task[9dc63219-231f-4a2c-8ddd-8dbd889356f3] raised unexpected: OperationalError(1366, "Incorrect string value: '\\xF0\\x9F\\xA7\\xA0Br...' for column 'title' at row 1")
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 581, in get_or_create
cms-worker-1         |     return self.get(**kwargs), False
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 435, in get
cms-worker-1         |     raise self.model.DoesNotExist(
cms-worker-1         | openedx.core.djangoapps.content.learning_sequences.models.LearningSequence.DoesNotExist: LearningSequence matching query does not exist.
cms-worker-1         | 
cms-worker-1         | During handling of the above exception, another exception occurred:
cms-worker-1         | 
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
cms-worker-1         |     return self.cursor.execute(query, args)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
cms-worker-1         |     res = self._query(query)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
cms-worker-1         |     db.query(q)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
cms-worker-1         |     _mysql.connection.query(self, query)
cms-worker-1         | MySQLdb.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\xA7\\xA0Br...' for column 'title' at row 1")
cms-worker-1         | 
cms-worker-1         | The above exception was the direct cause of the following exception:
cms-worker-1         | 
cms-worker-1         | Traceback (most recent call last):
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/celery/app/trace.py", line 451, in trace_task
cms-worker-1         |     R = retval = fun(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/celery/app/trace.py", line 734, in __protected_call__
cms-worker-1         |     return self.run(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/edx_django_utils/monitoring/internal/code_owner/utils.py", line 193, in new_function
cms-worker-1         |     return wrapped_function(*args, **kwargs)
cms-worker-1         |   File "/openedx/edx-platform/cms/djangoapps/contentstore/tasks.py", line 723, in update_outline_from_modulestore_task
cms-worker-1         |     update_outline_from_modulestore(course_key)
cms-worker-1         |   File "/openedx/edx-platform/cms/djangoapps/contentstore/outlines.py", line 385, in update_outline_from_modulestore
cms-worker-1         |     replace_course_outline(course_outline_data, content_errors=content_errors)
cms-worker-1         |   File "/opt/pyenv/versions/3.8.15/lib/python3.8/contextlib.py", line 75, in inner
cms-worker-1         |     return func(*args, **kwds)
cms-worker-1         |   File "/openedx/edx-platform/openedx/core/djangoapps/content/learning_sequences/api/outlines.py", line 406, in replace_course_outline
cms-worker-1         |     _update_sequences(course_outline, course_context)
cms-worker-1         |   File "/openedx/edx-platform/openedx/core/djangoapps/content/learning_sequences/api/outlines.py", line 474, in _update_sequences
cms-worker-1         |     LearningSequence.objects.update_or_create(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
cms-worker-1         |     return getattr(self.get_queryset(), name)(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 608, in update_or_create
cms-worker-1         |     obj, created = self.select_for_update().get_or_create(defaults, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 588, in get_or_create
cms-worker-1         |     return self.create(**params), True
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 453, in create
cms-worker-1         |     obj.save(force_insert=True, using=self.db)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/model_utils/models.py", line 38, in save
cms-worker-1         |     super().save(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 739, in save
cms-worker-1         |     self.save_base(using=using, force_insert=force_insert,
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 776, in save_base
cms-worker-1         |     updated = self._save_table(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 881, in _save_table
cms-worker-1         |     results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/base.py", line 919, in _do_insert
cms-worker-1         |     return manager._insert(
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
cms-worker-1         |     return getattr(self.get_queryset(), name)(*args, **kwargs)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1270, in _insert
cms-worker-1         |     return query.get_compiler(using=using).execute_sql(returning_fields)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
cms-worker-1         |     cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
cms-worker-1         |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
cms-worker-1         |     return executor(sql, params, many, context)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
cms-worker-1         |     raise dj_exc_value.with_traceback(traceback) from exc_value
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
cms-worker-1         |     return self.cursor.execute(sql, params)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
cms-worker-1         |     return self.cursor.execute(query, args)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
cms-worker-1         |     res = self._query(query)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
cms-worker-1         |     db.query(q)
cms-worker-1         |   File "/openedx/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
cms-worker-1         |     _mysql.connection.query(self, query)
cms-worker-1         | django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\xA7\\xA0Br...' for column 'title' at row 1")
cms-worker-1         | [2024-07-21 16:22:15,935: WARNING/ForkPoolWorker-1] /openedx/venv/lib/python3.8/site-packages/elasticsearch/connection/base.py:208: ElasticsearchWarning: Elasticsearch built-in security features are not enabled. Without authentication, your cluster could be accessible to anyone. See https://www.elastic.co/guide/en/elasticsearch/reference/7.17/security-minimal-setup.html to enable security.
cms-worker-1         |   warnings.warn(message, category=ElasticsearchWarning)

ABC · August 2, 2024, 11:47am

In the end I solved this (hackishly) by editing
.local/lib/python3.10/site-packages/tutor/templates/apps/openedx/config/partials/auth.yml
and removing some sort of conditional it had

      {%- if RUN_MYSQL %}
      charset: "utf8mb3"
      {%- endif %}

and replacing that with just

      charset: "utf8mb4"

(This being under the DATABASES → default → OPTIONS heading)

This doesn’t really make sense still though, as to why it works with no change my beta Palm platform and it doesn’t work on the production Palm patform. My .local/share/tutor/config.yml has RUN_MYSQL: false in both cases…

Topic		Replies	Views
Tutor is giving us error Tutor Help tutor	3	264	March 6, 2023
Certain Learners Seeing 404 Page not Found Error on certain modules Site Operations Help	3	2567	May 20, 2021
How to fix missing "check-empty.png" (which is causing HTTP 404)? (DoneXBlock broken in Palm Master?) Site Operations Help	2	118	August 6, 2024
404 Page not found Error Site Operations Help tutor	1	406	October 20, 2022
Learner Enrollment 502 Error Site Operations Help tutor , maple	1	457	October 12, 2022

Return of emoji / utf8mb4 error in Palm - /api/edx_proctoring/v1/user_onboarding/status issue?

Related topics