MySQL 8 upgrade stored routine fix

From VoIPmonitor.org


This guide explains how to resolve MySQL 8.0 startup failures after upgrading from Ubuntu 18.04 to 20.04 (or similar OS upgrades that upgrade MySQL from 5.x to 8.0).

Quick Reference

Symptom Solution
MySQL won't start after OS upgrade Drop stored routines before/after upgrade
Error: "Invalid utf8mb3 character string" Use one of 4 strategies below
VoIPmonitor missing functions after fix VoIPmonitor recreates them automatically on startup

Problem

Symptoms

After upgrading Ubuntu 18.04 to 20.04 (MySQL 5.x → 8.0), MySQL fails to start with errors in /var/log/mysql/error.log: 

[ERROR] [MY-011825] Invalid utf8mb3 character string
[ERROR] [MY-010020] Data Dictionary initialization failed

Root Cause

MySQL 8.0 deprecated the utf8 alias (now utf8mb3) and strictly validates character sets. VoIPmonitor's stored routines created on MySQL 5.x contain old utf8 references, which MySQL 8.0 rejects during startup.

ℹ️ Note: VoIPmonitor automatically recreates stored routines with correct character sets after you drop them and restart the sensor.

Strategy 1: Pre-Upgrade Cleanup (Recommended)

Use this if you have NOT upgraded yet, or can revert to pre-upgrade state.

Before Ubuntu upgrade, connect to MySQL 5.x and drop all stored routines: 

mysql -u root -p voipmonitor

-- List routines
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';

-- Drop all (repeat for each routine found above)
DROP PROCEDURE IF EXISTS routine_name;
DROP FUNCTION IF EXISTS function_name;

-- Or use this one-liner to generate DROP statements:
SELECT CONCAT('DROP ', ROUTINE_TYPE, ' IF EXISTS `', ROUTINE_NAME, '`;')
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'voipmonitor';
-- Copy output and execute

Then proceed with sudo do-release-upgrade. MySQL 8.0 will start successfully.

Strategy 2: Dump and Restore

Use if you have a backup from before the upgrade. 

# 1. Revert to pre-upgrade state

# 2. Create MySQL 8.0 compatible backup
mysqldump -u root -p \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  voipmonitor > voipmonitor_backup.sql

# 3. Drop stored routines (see Strategy 1)

# 4. Upgrade OS
sudo do-release-upgrade

# 5. Restore data
mysql -u root -p voipmonitor < voipmonitor_backup.sql

# 6. Restart VoIPmonitor to recreate routines
systemctl restart voipmonitor

Strategy 3: Migration Instance

Use if you cannot revert and have no backup, but have a temporary server available.

  1. Set up VoIPmonitor on a temporary server with MySQL 8.0
  2. Use migration feature to copy CDRs from old server to temp server
  3. Upgrade main server (MySQL 8.0 starts with empty database)
  4. Copy CDRs back from temp server

See Database Migration for migration configuration details.

Strategy 4: Startup Bypass (Last Resort)

⚠️ Warning: Risky method. Use only if all other strategies fail. 

# 1. Find problematic routine name
grep -i "function\|procedure" /var/log/mysql/error.log | tail -20

# 2. Add bypass config to /etc/mysql/my.cnf under [mysqld]:
skip-grant-tables
performance_schema=OFF
lower_case_table_names=1
skip-networking

# 3. Start MySQL and drop routine
systemctl start mysql
mysql -u root voipmonitor -e "DROP PROCEDURE IF EXISTS problematic_routine;"

# 4. Remove bypass config and restart normally
systemctl stop mysql
# Edit /etc/mysql/my.cnf - remove the 4 lines added above
systemctl start mysql
systemctl restart voipmonitor

Prevention

💡 Tip: Always backup before major OS or MySQL upgrades. 

mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql

See Also

AI Summary for RAG

Summary: MySQL 8.0 startup failure after Ubuntu 18.04 to 20.04 upgrade caused by stored routines in VoIPmonitor database containing deprecated utf8 (utf8mb3) character set. MySQL 8.0 strictly enforces utf8mb4 and aborts startup when loading legacy stored routines. Error: "Invalid utf8mb3 character string" in /var/log/mysql/error.log. Four solution strategies: (1) PRE-UPGRADE CLEANUP - Before upgrade, DROP ALL stored procedures/functions from voipmonitor database; (2) DUMP & RESTORE - Revert, create mysqldump with --default-character-set=utf8mb4, drop routines, upgrade, restore; (3) MIGRATION INSTANCE - Copy CDRs to temp server, upgrade main, copy back; (4) STARTUP BYPASS - Edit my.cnf with skip-grant-tables/skip-networking, start MySQL, drop routine manually. VoIPmonitor automatically recreates stored routines with correct charset after restart.

Keywords: MySQL 8 upgrade, Ubuntu 20.04 upgrade, utf8mb3, utf8mb4, stored routine, stored procedure, stored function, data dictionary, invalid utf8mb3 character string, MySQL startup failure, mysqldump, skip-grant-tables, MySQL 5.7 to MySQL 8.0, character set error

Key Questions:

  • Why does MySQL fail to start after Ubuntu 18.04 to 20.04 upgrade?
  • How do I fix MySQL 8 startup failure due to invalid utf8mb3 character string?
  • How do I drop all stored procedures and functions from voipmonitor database?
  • Will VoIPmonitor recreate stored routines automatically?
  • Should I dump my database before MySQL 8 upgrade?
  • What is the bypass method to fix MySQL startup failure?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=MySQL_8_upgrade_stored_routine_fix&oldid=10075"