# Palm upgrade, running RDBMS in Tutor, MySQL vs. MariaDB, utf8 vs. utf8mb4 **URL:** \ **Category:** Site Operators\ **Tags:** palm\ **Created:** [August 23, 2023, 2:15pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030 "2023-08-23T14:15:31Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [August 23, 2023, 2:15pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/1 "2023-08-23T14:15:31Z")
Hi everyone, apologies for the hodgepodge of a subject; I am trying to wrap my head around a few things as I write this. 🙂 We are currently in the process of planning our transition to Palm. All our production platforms are upgrades that were originally installed multiple releases back; we are not launching any new ones on Palm. We run with `tutor k8s`, with `RUN_MYSQL: true`, meaning our RDBMS instances run in a container in the same Kubernetes cluster and namespace as the rest of each Open edX platform. For certain reasons we have always run with MariaDB 10.4, which is a MariaDB release that is binary-compatible with MySQL 5.7. With Palm, Tutor moves from MySQL 5.7 to MySQL 8, which means we are now considering to migrate to Oracle MySQL, but it doesn’t seem like that’s the worst of our worries right now — instead, it’s the transition from the `utf8` character set to `utf8mb4`. @dave discussed this in a post last year: > [@Upgrading MySQL charset to utf8mb4](https://discuss.openedx.org/t/upgrading-mysql-charset-to-utf8mb4/7927): > > Nutmeg introduced a new bug in outlines where the [underlying issue](https://discuss.openedx.org/t/nutmeg-upgrade-outlines-py-openedx-core-djangoapps-content-learning-sequences-models-learningcontext-doesnotexist-learningcontext-matching-query-does-not-exist/7892) was our use of utf8 character set encoding for our MySQL tables. The problem: MySQL’s utf8 encoding isn’t “real” UTF-8. It’s a three-byte encoding that is missing certain characters, including emojis. We want to upgrade to utf8mb4 which is MySQL’s real UTF-8 data. This has been an issue for a while, but is only going to bite us more over time as some of our content storage gets shifted into MySQL. Some relevant pieces that make… And then much more recently, the encoding transition appears to have caused some major issues for people who upgraded from Olive to Palm, as evident by this emergency fix that @regis put into Tutor, and which precipitated the Tutor 16.1.0 release: > > > This fix is for a rather serious issue that affects users who upgrade from Olive… to Palm. The client mysql charset and collation was incorrectly set to utf8mb4, while the server stil runs utf8mb3. Only users who run the mysql container are affected. > > To resolve this issue, we explicitely configure the client to use the utf8mb3 charset/collation. > > Important note: users who have somehow managed to upgrade from olive to Palm before may find themselves in an undefined state. They might have to fix their mysql data manually. Same thing for users who launched Palm from scratch; although, according to my preliinary tests, they should be able to downgrade their connection from utf8mb4 to utf8mb3 without issue. > > Close #887. I’m having a bit of difficulty reconstructing what happened around this issue in the interim (that is, between July 2022 and August 2023), since the release notes for [Olive](https://docs.openedx.org/en/latest/community/release_notes/olive.html) and [Palm](https://docs.openedx.org/en/latest/community/release_notes/palm.html) are both silent about MySQL encoding considerations. (I also checked the [Nutmeg](https://docs.openedx.org/en/latest/community/release_notes/nutmeg.html) ones for good measure.) What I _can_ do is run the following query against one of our Olive databases: ```sql SELECT COUNT(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME)) AS column_count, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='openedx' GROUP BY CHARACTER_SET_NAME; ``` … and that returns: ```auto +--------------+--------------------+ | column_count | CHARACTER_SET_NAME | +--------------+--------------------+ | 2555 | NULL | | 1559 | utf8 | | 7 | utf8mb4 | +--------------+--------------------+ ``` So for some reason, there are already some `utf8mb4` columns mixed in between the lot of `utf8` (aka `utf8mb3`) ones. (I wonder why, by the way. @jmbowman’s [comment on the aforementioned Tutor PR](https://github.com/overhangio/tutor/pull/890#issuecomment-1678882862) sounds to me like this is unexpected.) So, what’s the way forward in this situation? Ultimately I believe we want everything to be `utf8mb4`, but [the discussion on Tutor PR 890](https://github.com/overhangio/tutor/pull/890) seems to indicate that this is far from trivial. What Tutor does now (as of 16.1.0) is set `--character-set-server=utf8mb3 --collation-server=utf8mb3_general_ci` when invoking `mysqld`, and I’m not sure what effect that has on data that lives in columns that are already set to `utf8mb4`. Perhaps someone can shed some light on this. 🙂 I guess the extremely condensed version of my question is this: If one upgrades - from Open edX Olive running on MySQL 5.7 or MariaDB 10.4 - to Open edX Palm running on MySQL 8.1 with `--character-set-server=utf8mb3 --collation-server=utf8mb3_general_ci`, then - is there any manual in-place data conversion that needs to be done, and - are any problems expected to arise from the fact that an existing database might already contain `utf8mb4`-charset columns? Thanks in advance! Cheers, Florian ---
**Author:** ![dave](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/dave/32/263_2.png) [@dave](https://discuss.openedx.org/u/dave)\ **Post date:** [August 24, 2023, 4:27am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/2 "2023-08-24T04:27:47Z")
> [@fghaas](#): > > So for some reason, there are already some `utf8mb4` columns mixed in between the lot of `utf8` (aka `utf8mb3`) ones. (I wonder why, by the way. @jmbowman’s [comment on the aforementioned Tutor PR](https://github.com/overhangio/tutor/pull/890#issuecomment-1678882862) sounds to me like this is unexpected.) I can chime in on this part. There are a few apps that explicitly set their tables to be `utf8mb4` because the thinking was: 1. It might take a long time for us to switch everyone over to `utf8mb4` because it will require some amount of downtime. 2. Some apps need this support more than others–e.g. it’s probably okay if your course title isn’t allowed to have an emoji or more obscure mathematical symbols, but it’s not okay for course content to have that restriction. 3. Even if we can’t actually store `utf8mb4` data in these tables yet (because the connection is `utf8mb3`), we’ll at least have the columns for these new apps in the correct format so that we can avoid an expensive future migration that requires downtime when the time comes to switch over to a `utf8mb4` connection. 4. `uf8mb3` is a subset of `utf8mb4`, and the data written into `utf8mb4` columns through a `utf8mb3` connection will still be valid (even if emojis couldn’t be written because they’re not supported in `utf8mb3`). 5. We can at some point switch the Django connection to be `utf8mb4` which will allow us to write full Unicode to these new columns. We still won’t be able to write full Unicode to `utf8mb3` columns that haven’t been migrated (because the database will reject the text as invalid), but we weren’t able to do that before anyhow, so we don’t lose anything. The GitHub issue you reference definitely casts doubt on this line of reasoning. I’m not entirely clear what happened–[tutor #890](https://github.com/overhangio/tutor/pull/890) seems point towards a table corruption bug introduced in MySQL 8.0.33, that was later patched in MySQL 8.0.34. That makes more sense to me, since I wouldn’t expect the database would allow the client to corrupt data by doing a select with the wrong connection settings. What I’m not clear on is whether upgrading the database alone fixes the bug, or if it really manifests anytime a `utf8mb4` connection operates on `utf8mb3` tables. (I just asked that question at the bottom of the issue). ---
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [August 24, 2023, 12:05pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/3 "2023-08-24T12:05:29Z")
Thanks! This reminds me that I should probably add a few more bits of information for reference: - The [PR 890 thread](https://github.com/overhangio/tutor/pull/890) explains that the issue identified was most likely fixed in 8.0.34, citing the [release notes](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html) for that release (specifically, the reference to bug #35410528, but this is a reference to an internal database so we can’t use this to dig any further). - Rather than updating to MySQL 8.0.34, @regis decided to go directly to MySQL 8.1, because that release “seems to fix a large number of bugs”. I wonder if the move to MySQL 8.1 by default wasn’t a bit premature, since 8.1 is not currently in [General Availability](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html) like the 8.0 series, but is rather labeled an [Innovation Release](https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html) — which happens to be a [completely new category](https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions), so I’m not sure if most people would be all too comfortable running it. 8.1 is also [not supported on AWS RDS at this time](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Concepts.VersionMgmt.html), which means that Tutor-managed MySQL databases now diverge by default from what’s arguably the most popular Open edX RDBMS option. What do others think? ---
**Author:** ![regis](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/regis/32/2640_2.png) [@regis](https://discuss.openedx.org/u/regis)\ **Post date:** [August 29, 2023, 3:55pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/4 "2023-08-29T15:55:03Z")
This is a complex issue, and I’ve not been to the bottom of it. So I’m not able to answer all your questions yet. > [@fghaas](#): > > So for some reason, there are already some `utf8mb4` columns mixed in between the lot of `utf8` (aka `utf8mb3`) ones. (I wonder why, by the way. @jmbowman’s [comment on the aforementioned Tutor PR](https://github.com/overhangio/tutor/pull/890#issuecomment-1678882862) sounds to me like this is unexpected.) Yes, I saw that, too. The tables come from blockstore.bundles: [https://github.com/openedx/blockstore/blob/14b2a844e6c5244980f5a95d2061a505429dba15/blockstore/apps/bundles/models.py#L70](https://github.com/openedx/blockstore/blob/14b2a844e6c5244980f5a95d2061a505429dba15/blockstore/apps/bundles/models.py#L70) Source: ```auto mysql> select table_schema, table_name, column_name, character_set_name from information_schema.COLUMNS where character_set_name="utf8mb4" and table_schema="openedx"; +--------------+-----------------------+--------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | +--------------+-----------------------+--------------------+--------------------+ | openedx | bundles_bundle | description | utf8mb4 | | openedx | bundles_bundle | slug | utf8mb4 | | openedx | bundles_bundle | title | utf8mb4 | | openedx | bundles_bundleversion | change_description | utf8mb4 | | openedx | bundles_collection | title | utf8mb4 | | openedx | bundles_draft | name | utf8mb4 | +--------------+-----------------------+--------------------+--------------------+ 6 rows in set (0.04 sec) ``` > [@fghaas](#): > > So, what’s the way forward in this situation? My 2 cents: if you are already able to upgrade to utf8mb4, then by all means please do. I did not want to push a rushed charset change in-between major releases, so I decided to stick with utf8mb3. > [@fghaas](#): > > If one upgrades > > - from Open edX Olive running on MySQL 5.7 or MariaDB 10.4 > - to Open edX Palm running on MySQL 8.1 with `--character-set-server=utf8mb3 --collation-server=utf8mb3_general_ci`, > [@fghaas](#): > > is there any manual in-place data conversion that needs to be done, and No, I don’t think so. > [@fghaas](#): > > are any problems expected to arise from the fact that an existing database might already contain `utf8mb4`-charset columns? If there are some problems, they should already exist in Olive. So I would not worry about it. > [@dave](#): > > [tutor #890](https://github.com/overhangio/tutor/pull/890) seems point towards a table corruption bug introduced in MySQL 8.0.33, that was later patched in MySQL 8.0.34. This is incorrect. Data corruption would occur even with 8.0.34 or 8.1.0. What matters is the explicit client-side charset definition. We only upgraded to 8.0.34 because it included a series of unrelated bugfixes. And then we decided to go straight to 8.1.0 for the same reason (but that was a mistake, see below). > [@dave](#): > > What I’m not clear on is whether upgrading the database alone fixes the bug, or if it really manifests anytime a `utf8mb4` connection operates on `utf8mb3` tables. The latter. > [@fghaas](#): > > I wonder if the move to MySQL 8.1 by default wasn’t a bit premature It was. I had not realized that [8.0 was considered LTS](https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions). If I had, I would have stuck with 8.0.34. But I’m not too worried about that decision, as 8.1 should be backward-compatible with 8.0. ---
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [August 31, 2023, 7:57am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/5 "2023-08-31T07:57:23Z")
> [@regis](#): > > This is a complex issue, and I’ve not been to the bottom of it. So I’m not able to answer all your questions yet. No problem, and thanks for the comprehensive reply! > [@regis](#): > > My 2 cents: if you are already able to upgrade to utf8mb4, then by all means please do. Hmmm, how would I be able to tell if I’m “able to upgrade”? From [your comment on the PR thread](https://github.com/overhangio/tutor/pull/890#issuecomment-1697476250) it looks to me like the only way to test would be switching the server-side settings to `--character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci`, running a migration, and then seeing if anything breaks — but for the migration to do anything meaningful, it would only make sense to do this on an Olive data dump in the middle of an upgrade to Palm. Is there any way to test this _after_ we’re through with the Palm upgrade? > [@regis](#): > > If there are some problems, they should already exist in Olive. So I would not worry about it. Makes sense, considering with MySQL 5.7/MariaDB 10.4 they would only have been populated with the subset of `utf8mb4` that’s equivalent to `utf8mb3`. Did I understand that correctly? > [@regis](#): > > It was. I had not realized that [8.0 was considered LTS](https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions). If I had, I would have stuck with 8.0.34. But I’m not too worried about that decision, as 8.1 should be backward-compatible with 8.0. OK. My concerns from upthread still stand — I’m a little bit hesitant to run with a MySQL version that the majority of Palm users arguably won’t be using\[1\], so we currently run our Palm tests with `DOCKER_IMAGE_MYSQL: docker.io/mysql:8.0.34` and see where that takes us. 🙂 * * * 1. This is on the assumption that more than 50% of learners using a production Open edX instance use one that runs on AWS with RDS. I have no hard data to back this up; it’s just a hunch. ---
**Author:** ![regis](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/regis/32/2640_2.png) [@regis](https://discuss.openedx.org/u/regis)\ **Post date:** [September 5, 2023, 4:00pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/6 "2023-09-05T16:00:48Z")
> [@fghaas](#): > > Is there any way to test this _after_ we’re through with the Palm upgrade? Yes. I don’t see why you would have to make the switch in Olive. Instead, I suggest you switch your server and client to utf8mb4 after the Palm upgrade. > [@fghaas](#): > > Makes sense, considering with MySQL 5.7/MariaDB 10.4 they would only have been populated with the subset of `utf8mb4` that’s equivalent to `utf8mb3`. Did I understand that correctly? Yes. > [@fghaas](#): > > I’m a little bit hesitant to run with a MySQL version that the majority of Palm users arguably won’t be using[[1]](#footnote-28274-1) My assumption is the opposite: I expect that the vast majority of people _do not_ use RDS. Actually, they probably don’t use AWS at all. ---
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [September 6, 2023, 7:07am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/7 "2023-09-06T07:07:51Z")
> [@regis](#): > > Yes. I don’t see why you would have to make the switch in Olive. Instead, I suggest you switch your server and client to utf8mb4 after the Palm upgrade. Right, but my question was aiming at something else: _how_ does one reliably test, after the upgrade to Palm is complete, that nothing broke in the process? ---
**Author:** ![regis](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/regis/32/2640_2.png) [@regis](https://discuss.openedx.org/u/regis)\ **Post date:** [September 6, 2023, 7:46am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/8 "2023-09-06T07:46:14Z")
Palm is a named release which was extensively tested. I would suggest you only test your own customizations to check that the upgrade worked. ---
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [September 6, 2023, 7:59am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/9 "2023-09-06T07:59:01Z")
Yes but is there a means to test that an installation is unaffected by _this specific issue,_ namely the UTF-8 encoding problem (which evidently _wasn’t_ detected during Palm release testing)? As in, if we run an upgrade and all migrations succeed, does that mean that all is definitely well with the database data? Or is there a scenario in which migrations might succeed and data corruption still occurred, and if yes, is there a test for that? ---
**Author:** ![regis](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/regis/32/2640_2.png) [@regis](https://discuss.openedx.org/u/regis)\ **Post date:** [September 6, 2023, 8:29am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/10 "2023-09-06T08:29:19Z")
Right, sorry about that, I misunderstood your question. You will definitely notice the encoding issue very quickly if you face it. Migrations will succeed, but your platform will have many 500 errors. The Python stacktrace will include UnicodeDecodeError exceptions. You will be unable to run mysqldump on your database. The following query will render garbled data: ``` tutor local do sqlshell openedx select * from course_overviews_courseoverview; ``` ---
**Author:** ![fghaas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/fghaas/32/168_2.png) [@fghaas](https://discuss.openedx.org/u/fghaas)\ **Post date:** [September 6, 2023, 8:36am UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/11 "2023-09-06T08:36:01Z")
OK, thanks! “Run `mysqldump` and if it succeeds we’re most probably fine” is a test we can work with. 🙂 ---
**Author:** ![dave](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.openedx.org/dave/32/263_2.png) [@dave](https://discuss.openedx.org/u/dave)\ **Post date:** [September 6, 2023, 2:54pm UTC](https://discuss.openedx.org/t/palm-upgrade-running-rdbms-in-tutor-mysql-vs-mariadb-utf8-vs-utf8mb4/11030/12 "2023-09-06T14:54:54Z")
[Important update](https://github.com/overhangio/tutor/pull/890#issuecomment-1708284771) from the PR thread from @regis. TLDR excerpt: > OK I’ve done some more research and I think I’ve finally found the root cause, which is not at all related to the utf8mb3 charset. Instead the issue is caused by that MySQL bug #35410528. > > (snip details) > > Thus, the following are all working fixes: > > 1. Restarting the mysql 8.0.33 container just prior to applying migrations. > 2. Upgrading to mysql 8.0.34 or 8.1.0 before applying migrations. (in that case restarting the container is unnecessary) @regis: Thank you for digging into this so thoroughly!