MySQL 8 upgrade stored routine fix

From VoIPmonitor.org
Revision as of 21:29, 6 January 2026 by Admin (talk | contribs) (Review: opravy formátování (syntaxhighlight místo pre), oprava nadpisů, přidání DISPLAYTITLE, přidání rozhodovacího diagramu, úprava struktury kroků, oprava info boxů s 1= prefix)


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). The issue occurs when stored routines (functions or procedures) in the VoIPmonitor database use the deprecated utf8 character set (now called utf8mb3 in MySQL 8.0), which causes MySQL to fail during startup while populating data dictionary tables.

Problem Symptoms

After upgrading the operating system from Ubuntu 18 to 20, which automatically upgrades MySQL from 5.x to 8.0, the MySQL service fails to start.

Error Logs

The MySQL error log (typically /var/log/mysql/error.log) shows errors similar to: 

[ERROR] [MY-011825] [Server] Error in loading of data dictionary: Invalid utf8mb3 character string
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed

The service may also reference a specific stored routine containing the invalid character set definition: 

[ERROR] [MY-010957] [Server] Function/Procedure 'voipmonitor.function_name': Invalid utf8mb3 character string

This error prevents MySQL from populating data dictionary tables during startup.

Root Cause

MySQL 5.x used the utf8 character set alias, which referred to a 3-byte encoding (now called utf8mb3). MySQL 8.0 deprecated the utf8 alias and now enforces strict character set validation.

VoIPmonitor's stored functions and procedures created on MySQL 5.x contain references to the old utf8 character set. When MySQL 8.0 attempts to load these routines into the new Data Dictionary during startup, it detects the invalid utf8mb3 definition and aborts the service.

The Data Dictionary in MySQL 8.0 is a new system database that stores metadata about database objects including stored routines. It must be fully initialized before MySQL will start.

Solution Strategies

There are four strategies to resolve this issue, ordered from recommended to least recommended.

ℹ️ Note: VoIPmonitor will automatically recreate stored routines: After you drop the stored routines and restart VoIPmonitor, the sensor will automatically recreate necessary functions and procedures with the correct MySQL 8.0 compatible character sets.

Strategy 1: Pre-Upgrade Cleanup (Recommended)

This is the best approach if you have NOT yet upgraded the operating system, or if you can revert to a pre-upgrade state from a backup.

Steps

Step 1: Before initiating the Ubuntu upgrade, connect to your MySQL 5.x server: 

mysql -u root -p

Step 2: Identify stored functions and procedures in the voipmonitor database: 

USE voipmonitor;

-- List all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';

-- List all stored functions
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';

Step 3: Drop all stored functions and procedures: 

-- Drop all procedures
SET @schema = 'voipmonitor';
SET @done = 0;
SET @dropstmt = '';

WHILE @done = 0 DO
  SELECT CONCAT('DROP PROCEDURE IF EXISTS `', ROUTINE_NAME, '`;')
  INTO @dropstmt
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_SCHEMA = @schema AND ROUTINE_TYPE = 'PROCEDURE'
  LIMIT 1;

  IF @dropstmt IS NOT NULL THEN
    PREPARE stmt FROM @dropstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @dropstmt = NULL;
  ELSE
    SET @done = 1;
  END IF;
END WHILE;

-- Drop all functions
SET @done = 0;

WHILE @done = 0 DO
  SELECT CONCAT('DROP FUNCTION IF EXISTS `', ROUTINE_NAME, '`;')
  INTO @dropstmt
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_SCHEMA = @schema AND ROUTINE_TYPE = 'FUNCTION'
  LIMIT 1;

  IF @dropstmt IS NOT NULL THEN
    PREPARE stmt FROM @dropstmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @dropstmt = NULL;
  ELSE
    SET @done = 1;
  END IF;
END WHILE;

-- Verify all stored routines are removed
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';

Step 4: Exit MySQL: 

EXIT;

Step 5: Proceed with the Ubuntu 18.04 to 20.04 upgrade: 

sudo do-release-upgrade

Step 6: After the upgrade completes, MySQL 8.0 should start correctly. VoIPmonitor will automatically recreate its necessary routines with the correct character set when the sensor starts.

Verification

After the upgrade: 

# Check MySQL is running
systemctl status mysql

# Check VoIPmonitor can connect
systemctl status voipmonitor

# View sensor logs for any database errors
journalctl -u voipmonitor -n 50

Strategy 2: Dump and Restore

If you have already performed the OS upgrade and MySQL will not start, you can use this approach. It requires reverting to a pre-upgrade state or having a database backup.

Prerequisites

  • A logical backup (mysqldump) from before the OS upgrade, OR
  • Ability to revert the system to a pre-upgrade state

Steps

Step 1: Revert the system to a pre-upgrade state (e.g., from a backup or snapshot).

Step 2: Run mysqlcheck on the voipmonitor database to identify and fix potential issues: 

mysqlcheck -u root -p --check-upgrade voipmonitor

Step 3: Create a logical backup of the voipmonitor database with options compatible with MySQL 8.0: 

mysqldump -u root -p \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  voipmonitor > voipmonitor_backup.sql

Step 4: Drop all stored routines (see Strategy 1, Step 3) to prevent the character set issue.

Step 5: Perform the Ubuntu 18.04 to 20.04 upgrade: 

sudo do-release-upgrade

Step 6: After the upgrade completes, create the voipmonitor database if needed and restore data: 

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS voipmonitor CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p voipmonitor < voipmonitor_backup.sql

Step 7: Restart VoIPmonitor to recreate stored routines: 

systemctl restart voipmonitor

Strategy 3: Migration Instance

If you cannot use Strategy 1 or Strategy 2 (no backups, cannot revert), you can use the VoIPmonitor "migration" instance to preserve your data.

Overview

  1. Copy CDRs from the old server to a temporary server with an accessible MySQL 8.0 database
  2. Upgrade the main server (OS and MySQL)
  3. Copy the CDRs back to the newly upgraded server

This method avoids the stored routine issue entirely because you are working with CDR data only, not the database with stored routines.

Detailed Steps

  1. Install VoIPmonitor on a temporary server with MySQL 8.0 compatibility
  2. Configure the temporary server as a "migration" destination
  3. Use the migration feature to copy CDRs from your old (broken) server to the temporary server
  4. Upgrade your main server (Ubuntu 18.04 to 20.04, MySQL 5.x to 8.0)
  5. On the upgraded main server, MySQL 8.0 should start (because the database will be fresh/new)
  6. Use the migration feature again to copy CDRs from the temporary server back to the upgraded main server

ℹ️ Note: The migration instance must have sufficient storage capacity to hold the CDR data.

See the distributed architecture documentation for details on using the migration feature.

Strategy 4: Startup Bypass (Last Resort)

This is a risky method and should only be attempted if all previous strategies fail. It involves launching MySQL with bypass flags to manually remove stored routines.

⚠️ Warning: This method can lead to data corruption if not done correctly. Use only as a last resort.

Steps

Step 1: Check the error log to identify the specific routine: 

grep -i "function\|procedure" /var/log/mysql/error.log | tail -20

Step 2: Configure MySQL for safe startup by modifying /etc/mysql/my.cnf (or /etc/mysql/mysql.conf.d/mysqld.cnf) and adding these lines under [mysqld]: 

[mysqld]
# Bypass authentication to allow connection during repair
skip-grant-tables
# Disable performance schema to reduce startup load
performance_schema=OFF
# Force lower case table names (common fix for 5.x -> 8.0 mismatch)
lower_case_table_names=1
# Skip network connections for safety mode
skip-networking

Step 3: Attempt to start MySQL: 

systemctl start mysql

Step 4: If MySQL starts, connect and drop the offending routine: 

mysql -u root

USE voipmonitor;

-- Drop the specific procedure/function named in the error log
-- Replace with actual names from your error log
DROP PROCEDURE IF EXISTS offending_procedure_name;
DROP FUNCTION IF EXISTS offending_function_name;

-- List all routines to ensure all problematic ones are removed
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';

EXIT;

Step 5: Remove the temporary configuration lines and restart MySQL normally: 

systemctl stop mysql
# Edit /etc/mysql/my.cnf to remove the temporary config lines
systemctl start mysql

Step 6: Restart VoIPmonitor to recreate necessary routines: 

systemctl restart voipmonitor

Prevention

To avoid this issue in future upgrades:

💡 Tip: Always perform full database backups before any major OS or MySQL upgrades.

  • Create logical backups before upgrading:
mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql
  • Test the upgrade process on a non-production system
  • Review MySQL 8.0 release notes for deprecated features
  • Consider using MariaDB instead of MySQL for VoIPmonitor (MariaDB is generally more compatible across versions)

See Database Backup and Restore for backup examples.

Related Documentation

AI Summary for RAG

Summary: MySQL 8.0 startup failure after Ubuntu 18.04 to 20.04 upgrade caused by stored routines (functions/procedures) in VoIPmonitor database containing deprecated utf8 (utf8mb3) character set. MySQL 8.0 deprecated utf8 alias, strictly enforces utf8mb4, and aborts startup when loading legacy stored routines with invalid character set into Data Dictionary. Error message: "Invalid utf8mb3 character string" in /var/log/mysql/error.log. Four solution strategies: (1) PRE-UPGRADE CLEANUP (Recommended) - Before Ubuntu upgrade, connect to MySQL 5.x, DROP ALL stored procedures and functions from voipmonitor database, then upgrade; (2) DUMP & RESTORE - Revert to pre-upgrade, run mysqlcheck, create mysqldump backup with --default-character-set=utf8mb4, drop stored routines, upgrade OS, restore dump; (3) MIGRATION INSTANCE - Copy CDRs to temp server, upgrade main server, copy CDRs back; (4) STARTUP BYPASS (Last Resort) - Edit my.cnf to add skip-grant-tables, performance_schema=OFF, lower_case_table_names=1, skip-networking, start MySQL, drop offending routine manually, remove temp config, restart. VoIPmonitor automatically recreates stored routines with correct charset after startup.

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, mysqlcheck, mysqldump, migration instance, skip-grant-tables, lower_case_table_names, 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?
  • Can I use VoIPmonitor migration instance to avoid this issue?
  • What is the bypass method to fix MySQL startup failure?
  • What error log indicates MySQL 8 stored routine issue?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=MySQL_8_upgrade_stored_routine_fix&oldid=9667"