Updating MariaDB to utf8mb4 character set failed

[1xPdd]

I originally posted this in pull request, but realized, since the pull had been merged, it would probably go unseen. Sorry for any confusion.

(updated for clarity)

I ended up here after seeing the MariaDB notice for v1.9.3. I had HedgeDoc pinned 1.9.2 with MariaDB pinned to 10 (currently 10.8). I followed the direction that @agross outlined and the database seems to have converted, but I now have this error repeating in my log:

hedgedb     | 2022-05-26T13:42:19.195934326Z 2022-05-26 13:42:19 6 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
hedgedb     | 2022-05-26T13:42:19.196017170Z 2022-05-26 13:42:19 6 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

Trying to resolve this, I tried restoring a mysqldump but get this error:

ERROR 1005 (HY000) at line 25: Can't create table 'hedgedoc'.'Authors' (errno: 150 "Foreign key constraint is incorrectly formed")

I'm unsure if this relates to mounting with the new utf8.cnf file. I tried restoring with both the old and the new but get the same error regardless.

---

The wrong answer to the log errors was: docker exec -i hedgedb mysql_upgrade --user hedgedoc --password=redacted hedgedoc, based on this. That returned:

Major version upgrade detected from 10.6.3-MariaDB to 10.8.3-MariaDB. Check required!
Phase 1/7: Checking and upgrading mysql database
Processing databases                                 
mysql                                                
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
sys.host_summary                                   OK
sys.host_summary_by_file_io                        OK                                                                                                                                      sys.host_summary_by_file_io_type                   OK
sys.host_summary_by_stages                         OK
sys.host_summary_by_statement_latency              OK
sys.host_summary_by_statement_type                 OK
sys.innodb_buffer_stats_by_schema                  OK
sys.innodb_buffer_stats_by_table                   OK
sys.innodb_lock_waits                              OK
sys.io_by_thread_by_latency                        OK
sys.io_global_by_file_by_bytes                     OK
sys.io_global_by_file_by_latency                   OK
sys.io_global_by_wait_by_bytes                     OK
sys.io_global_by_wait_by_latency                   OK
sys.latest_file_io                                 OK
sys.memory_by_host_by_current_bytes                OK
sys.memory_by_thread_by_current_bytes              OK
sys.memory_by_user_by_current_bytes                OK
sys.memory_global_by_current_bytes                 OK
sys.memory_global_total                            OK
sys.metrics                                        OK
sys.processlist                                    OK
sys.ps_check_lost_instrumentation                  OK
sys.schema_auto_increment_columns                  OK
sys.schema_index_statistics                        OK
sys.schema_object_overview                         OK
sys.schema_redundant_indexes                       OK
sys.schema_table_lock_waits                        OK
sys.schema_table_statistics                        OK
sys.schema_table_statistics_with_buffer            OK
sys.schema_tables_with_full_table_scans            OK
sys.schema_unused_indexes                          OK
sys.session                                        OK
sys.session_ssl_status                             OK
sys.statement_analysis                             OK
sys.statements_with_errors_or_warnings             OK
sys.statements_with_full_table_scans               OK
sys.statements_with_runtimes_in_95th_percentile    OK
sys.statements_with_sorting                        OK
sys.statements_with_temp_tables                    OK
sys.user_summary                                   OK
sys.user_summary_by_file_io                        OK
sys.user_summary_by_file_io_type                   OK
sys.user_summary_by_stages                         OK
sys.user_summary_by_statement_latency              OK
sys.user_summary_by_statement_type                 OK
sys.version                                        OK
sys.wait_classes_global_by_avg_latency             OK
sys.wait_classes_global_by_latency                 OK
sys.waits_by_host_by_latency                       OK
sys.waits_by_user_by_latency                       OK
sys.waits_global_by_latency                        OK
sys.x$host_summary                                 OK
sys.x$host_summary_by_file_io                      OK
sys.x$host_summary_by_file_io_type                 OK
sys.x$host_summary_by_stages                       OK
sys.x$host_summary_by_statement_latency            OK
sys.x$host_summary_by_statement_type               OK
sys.x$innodb_buffer_stats_by_schema                OK
sys.x$innodb_buffer_stats_by_table                 OK
sys.x$innodb_lock_waits                            OK
sys.x$io_by_thread_by_latency                      OK
sys.x$io_global_by_file_by_bytes                   OK
sys.x$io_global_by_file_by_latency                 OK
sys.x$io_global_by_wait_by_bytes                   OK
sys.x$io_global_by_wait_by_latency                 OK
sys.x$latest_file_io                               OK
sys.x$memory_by_host_by_current_bytes              OK
sys.x$memory_by_thread_by_current_bytes            OK
sys.x$memory_by_user_by_current_bytes              OK
sys.x$memory_global_by_current_bytes               OK
sys.x$memory_global_total                          OK
sys.x$processlist                                  OK
sys.x$ps_digest_95th_percentile_by_avg_us          OK
sys.x$ps_digest_avg_latency_distribution           OK
sys.x$ps_schema_table_statistics_io                OK
sys.x$schema_flattened_keys                        OK
sys.x$schema_index_statistics                      OK
sys.x$schema_table_lock_waits                      OK
sys.x$schema_table_statistics                      OK
sys.x$schema_table_statistics_with_buffer          OK
sys.x$schema_tables_with_full_table_scans          OK
sys.x$session                                      OK
sys.x$statement_analysis                           OK
sys.x$statements_with_errors_or_warnings           OK
sys.x$statements_with_full_table_scans             OK
sys.x$statements_with_runtimes_in_95th_percentile  OK
sys.x$statements_with_sorting                      OK
sys.x$statements_with_temp_tables                  OK
sys.x$user_summary                                 OK
sys.x$user_summary_by_file_io                      OK
sys.x$user_summary_by_file_io_type                 OK
sys.x$user_summary_by_stages                       OK
sys.x$user_summary_by_statement_latency            OK
sys.x$user_summary_by_statement_type               OK
sys.x$wait_classes_global_by_avg_latency           OK
sys.x$wait_classes_global_by_latency               OK
sys.x$waits_by_host_by_latency                     OK
sys.x$waits_by_user_by_latency                     OK
sys.x$waits_global_by_latency                      OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
hedgedoc
hedgedoc.Notes                                     OK
hedgedoc.Revisions                                 OK
hedgedoc.SequelizeMeta                             OK
hedgedoc.Session                                   OK
hedgedoc.Sessions                                  OK
hedgedoc.Temp                                      OK
hedgedoc.Temps                                     OK
hedgedoc.Users                                     OK
information_schema
performance_schema
sys
sys.sys_config                                     OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

That looked good, so I restarted the docker compose file and now things are really broken:

hedgeapp    | 2022-05-26T14:10:38.568594675Z 
hedgeapp    | 2022-05-26T14:10:38.750446745Z 2022-05-26T14:10:38.749Z info:     deserializeUser: 281d6306-835d-4177-af23-63bf40de2c26
hedgeapp    | 2022-05-26T14:10:38.805723307Z 2022-05-26T14:10:38.804Z error:    Table 'hedgedoc.Authors' doesn't exist
hedgeapp    | 2022-05-26T14:10:38.994588593Z 2022-05-26T14:10:38.994Z info:     deserializeUser: 281d6306-835d-4177-af23-63bf40de2c26
hedgeapp    | 2022-05-26T14:10:39.022967210Z 2022-05-26T14:10:39.022Z info:     redacted_IP - - [26/May/2022:14:10:39 +0000] "GET /500 HTTP/1.1" 500 - "https://redacted_URL" "Mozilla/5.0 (Windows NT 10.0; rv:91.0) Gecko/20100101 Firefox/91.0"

At this point, any document I open in HedgeDoc gives me a "500 Internal Error wtf" error. If anyone could donate some wisdom, I'd much appreciate it as I'm in over my head and fear I've both corrupted the database and made the backup dump somehow incompatible.

Updated by @ErikMichelson for readability reasons