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

Site Operators
2

@Abdess this seems to apparently be the reintroduction of the MySQL emoji issue after I upgraded a Lilac instance to Palm. :frowning: 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):

  1. 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.)

  1. 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
  1. 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
  1. 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 :slightly_frowning_face:. 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?)