How can you repair a MySQL 8.0 Installation after changing `character_set_system` from utfmb3 to utfmb4 fails?

Question

NOTE: My issue was due to a syntax error in the my.cnf file, not anything to do with errors on converting to utf8mb4. I still do not have any idea how you do that. I posted an answer explaining what the issue was.

Background

I was writing some code to access the INFORMATION_SCHEMA. I wanted to make a MySql data source so that I could create a data set out of it and generate the select/fill/etc. functionality from a typed DataSet.

When I connected to the DB without a DB it worked fine, we just updated everything to uft8mb4 from the new utf8mb3 default, because .NET does not support that character set. When I tried to connect to INFORMATION_SCHEMA, everything looked okay except when I tried to open the tables, I got the .NET doesn't support utfmb3 error.

Changing character_set_system

I went to look at the Server variables on Workbench, and noticed that everything was updated to utf8mb4 except for the character_set_system. I had read that you have to be careful updating that but well, I added character_set_system utfmb4 to my.cnf and rebooted in one fell swoop, and only a second later did I realize what I had done.

At that point I get an error message telling me to run systemctl status mysqld.service and journalctl -xe. I snipped what seemed to be the relevant portions of those logs (domain names have been changed to protect the guilty):

journalctl -xe

Jun 06 10:19:21 servername.tld polkitd[640]: Registered Authentication Agent for unix-process:32382:26043948 (system bus name :1.186 [/usr/bin/pkttyagent --notify-fd 5 --fallback]
Jun 06 10:19:21 servername.tld systemd[1]: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysqld.service has begun starting up.
Jun 06 10:19:31 servername.tld systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 06 10:19:31 servername.tld systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysqld.service has failed.
--
-- The result is failed.
Jun 06 10:19:31 servername.tld systemd[1]: Unit mysqld.service entered failed state.
Jun 06 10:19:31 servername.tld systemd[1]: mysqld.service failed.
Jun 06 10:19:31 servername.tld polkitd[640]: Unregistered Authentication Agent for unix-process:32382:26043948 (system bus name :1.186, object path /org/freedesktop/PolicyKit1/Aut

systemctl status mysqld.service

Jun 06 10:19:31 ● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2022-06-06 10:19:31 EDT; 7min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 32415 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 32388 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 32415 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Jun 06 10:19:21 servername.tld systemd[1]: Starting MySQL Server...
Jun 06 10:19:31 servername.tld systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 06 10:19:31 servername.tld systemd[1]: Failed to start MySQL Server.
Jun 06 10:19:31 servername.tld systemd[1]: Unit mysqld.service entered failed state.
Jun 06 10:19:31 servername.tld systemd[1]: mysqld.service failed. systemd[1]: mysqld.service failed.

At this point I figured I really broke things, because I had seen people warning not to change those without knowing what you were doing. Which, clearly, I did not.

I went back to my.cnf and changed the setting to character_set_system utf8mb3 I would say 'changeed it back' but I didn't, since it wasn't like that to begin with. I just added the setting.

Upon restarting, I got the same message about checking the errors, the errors are practically identical, so trying to set it to mb3 didn't make a difference.

Anyway, I've searched dba/serverfault/stackoverflow/google for how to fix this, and I saw lots of people warning not to do it, but, I, at least, was unable to find anyone who had actually been reckless enough to do it.

Maybe we can back up the data somewhere and then restore a backup from last week, and then put the data back, worst case? I'm just not familiar enough with the back end to know what horrors I have done to the data.

Answer 1

This was just me being careless with formatting. I had a space instead of an equals on the character_set_system variable assignment. So, fortunately I never touched the system database at all, and taking the line out fixed the syntax error, so it works again.

I would still be interested in knowing if there's a way to get more information from those errors. The errors basically just said that things failed; if I had seen it was an error parsing my.cnf or similar, I would have probably caught it immediately. But, if anyone else finds themselves in this circumstance, be sure to just make sure any changes you made are actually syntactically valid,

TL;DR: since you're not getting detailed errors, check anything you changed very carefully for syntax errors (or figure out how to get the details, which I probably would have tried to do if this wasn't a production server); and obviously the real solution is don't try to change system settings if you aren't sure what you're doing.

Answer 2

Do not make changes in the mysql or information_schema databases.

In your databases, what symptoms do you have? Was anything else of interest in the log files? How did you do the conversion -- was it ALTER...CONVERT TO... or some other method? Where there non-utf8 columns that got accidentally converted?

Modifying character_set_system is not how you to change the charset.

(It sounds like you did more than change that one setting??)