MySQL 8 upgrade stored routine fix: Difference between revisions

From VoIPmonitor.org
(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)
(Rewrite: konsolidace a vylepšení struktury - přidána Quick Reference tabulka, zjednodušen SQL pro drop routines, zkráceno o 50%)
 
Line 3: Line 3:
{{DISPLAYTITLE:MySQL 8 Upgrade: Stored Routine Fix}}
{{DISPLAYTITLE:MySQL 8 Upgrade: Stored Routine Fix}}


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 <code>utf8</code> character set (now called <code>utf8mb3</code> in MySQL 8.0), which causes MySQL to fail during startup while populating data dictionary tables.
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 ==
 
{| class="wikitable"
|-
! 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
|}


<kroki lang="mermaid">
<kroki lang="mermaid">
Line 15: Line 28:
     F -->|Yes| G[Strategy 3: Migration Instance]
     F -->|Yes| G[Strategy 3: Migration Instance]
     F -->|No| H[Strategy 4: Startup Bypass]
     F -->|No| H[Strategy 4: Startup Bypass]
    C --> I[Drop routines → Upgrade]
    E --> I
    G --> J[Copy CDRs → Upgrade → Copy back]
    H --> K[Bypass mode → Drop routine]
</kroki>
</kroki>


== Problem Symptoms ==
== Problem ==
 
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 <code>/var/log/mysql/error.log</code>) shows errors similar to:


<syntaxhighlight lang="text">
=== Symptoms ===
[ERROR] [MY-011825] [Server] Error in loading of data dictionary: Invalid utf8mb3 character string
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed
</syntaxhighlight>


The service may also reference a specific stored routine containing the invalid character set definition:
After upgrading Ubuntu 18.04 to 20.04 (MySQL 5.x → 8.0), MySQL fails to start with errors in <code>/var/log/mysql/error.log</code>:


<syntaxhighlight lang="text">
<syntaxhighlight lang="text">
[ERROR] [MY-010957] [Server] Function/Procedure 'voipmonitor.function_name': Invalid utf8mb3 character string
[ERROR] [MY-011825] Invalid utf8mb3 character string
[ERROR] [MY-010020] Data Dictionary initialization failed
</syntaxhighlight>
</syntaxhighlight>


This error prevents MySQL from populating data dictionary tables during startup.
=== Root Cause ===
 
== Root Cause ==
 
MySQL 5.x used the <code>utf8</code> character set alias, which referred to a 3-byte encoding (now called <code>utf8mb3</code>). MySQL 8.0 deprecated the <code>utf8</code> alias and now enforces strict character set validation.


VoIPmonitor's stored functions and procedures created on MySQL 5.x contain references to the old <code>utf8</code> character set. When MySQL 8.0 attempts to load these routines into the new Data Dictionary during startup, it detects the invalid <code>utf8mb3</code> definition and aborts the service.
MySQL 8.0 deprecated the <code>utf8</code> alias (now <code>utf8mb3</code>) and strictly validates character sets. VoIPmonitor's stored routines created on MySQL 5.x contain old <code>utf8</code> references, which MySQL 8.0 rejects during startup.


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.
{{Note|1='''VoIPmonitor automatically recreates stored routines''' with correct character sets after you drop them and restart the sensor.}}
 
== Solution Strategies ==
 
There are four strategies to resolve this issue, ordered from recommended to least recommended.
 
{{Note|1='''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) ==
== 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.
Use this if you have NOT upgraded yet, or can revert to pre-upgrade state.


=== Steps ===
'''Before Ubuntu upgrade''', connect to MySQL 5.x and drop all stored routines:
 
'''Step 1:''' Before initiating the Ubuntu upgrade, connect to your MySQL 5.x server:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
mysql -u root -p
mysql -u root -p voipmonitor
</syntaxhighlight>
</syntaxhighlight>
'''Step 2:''' Identify stored functions and procedures in the <code>voipmonitor</code> database:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
USE voipmonitor;
-- List routines
 
-- List all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
-- List all stored functions
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';
</syntaxhighlight>


'''Step 3:''' Drop all stored functions and procedures:
-- Drop all (repeat for each routine found above)
DROP PROCEDURE IF EXISTS routine_name;
DROP FUNCTION IF EXISTS function_name;


<syntaxhighlight lang="sql">
-- Or use this one-liner to generate DROP statements:
-- Drop all procedures
SELECT CONCAT('DROP ', ROUTINE_TYPE, ' IF EXISTS `', ROUTINE_NAME, '`;')
SET @schema = 'voipmonitor';
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'voipmonitor';
SET @done = 0;
-- Copy output and execute
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';
</syntaxhighlight>
 
'''Step 4:''' Exit MySQL:
 
<syntaxhighlight lang="sql">
EXIT;
</syntaxhighlight>
 
'''Step 5:''' Proceed with the Ubuntu 18.04 to 20.04 upgrade:
 
<syntaxhighlight lang="bash">
sudo do-release-upgrade
</syntaxhighlight>
</syntaxhighlight>


'''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.
Then proceed with <code>sudo do-release-upgrade</code>. MySQL 8.0 will start successfully.
 
=== Verification ===
 
After the upgrade:
 
<syntaxhighlight lang="bash">
# 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
</syntaxhighlight>


== Strategy 2: Dump and Restore ==
== 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.
Use if you have a backup from before the upgrade.
 
=== Prerequisites ===
 
* A logical backup (<code>mysqldump</code>) 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 <code>mysqlcheck</code> on the <code>voipmonitor</code> database to identify and fix potential issues:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
mysqlcheck -u root -p --check-upgrade voipmonitor
# 1. Revert to pre-upgrade state
</syntaxhighlight>


'''Step 3:''' Create a logical backup of the <code>voipmonitor</code> database with options compatible with MySQL 8.0:
# 2. Create MySQL 8.0 compatible backup
 
<syntaxhighlight lang="bash">
mysqldump -u root -p \
mysqldump -u root -p \
   --routines \
   --routines --triggers --events \
  --triggers \
  --events \
   --default-character-set=utf8mb4 \
   --default-character-set=utf8mb4 \
   --set-gtid-purged=OFF \
   --set-gtid-purged=OFF \
   voipmonitor > voipmonitor_backup.sql
   voipmonitor > voipmonitor_backup.sql
</syntaxhighlight>


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


'''Step 5:''' Perform the Ubuntu 18.04 to 20.04 upgrade:
# 4. Upgrade OS
 
<syntaxhighlight lang="bash">
sudo do-release-upgrade
sudo do-release-upgrade
</syntaxhighlight>


'''Step 6:''' After the upgrade completes, create the <code>voipmonitor</code> database if needed and restore data:
# 5. Restore data
 
<syntaxhighlight lang="bash">
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
mysql -u root -p voipmonitor < voipmonitor_backup.sql
</syntaxhighlight>


'''Step 7:''' Restart VoIPmonitor to recreate stored routines:
# 6. Restart VoIPmonitor to recreate routines
 
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
systemctl restart voipmonitor
</syntaxhighlight>
</syntaxhighlight>
Line 214: Line 102:
== Strategy 3: Migration Instance ==
== 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.
Use if you cannot revert and have no backup, but have a temporary server available.
 
=== Overview ===


# Copy CDRs from the old server to a temporary server with an accessible MySQL 8.0 database
# Set up VoIPmonitor on a temporary server with MySQL 8.0
# Upgrade the main server (OS and MySQL)
# Use migration feature to copy CDRs from old server to temp server
# Copy the CDRs back to the newly upgraded server
# Upgrade main server (MySQL 8.0 starts with empty database)
# Copy CDRs back from temp server


This method avoids the stored routine issue entirely because you are working with CDR data only, not the database with stored routines.
See [[Redundant_database|Database Migration]] for migration configuration details.
 
=== Detailed Steps ===
 
# Install VoIPmonitor on a temporary server with MySQL 8.0 compatibility
# Configure the temporary server as a "migration" destination
# Use the migration feature to copy CDRs from your old (broken) server to the temporary server
# Upgrade your main server (Ubuntu 18.04 to 20.04, MySQL 5.x to 8.0)
# On the upgraded main server, MySQL 8.0 should start (because the database will be fresh/new)
# 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 [[Sniffer_distributed_architecture|distributed architecture]] documentation for details on using the migration feature.


== Strategy 4: Startup Bypass (Last Resort) ==
== 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|1=Risky method. Use only if all other strategies fail.}}
 
{{Warning|1=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:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# 1. Find problematic routine name
grep -i "function\|procedure" /var/log/mysql/error.log | tail -20
grep -i "function\|procedure" /var/log/mysql/error.log | tail -20
</syntaxhighlight>
</syntaxhighlight>


'''Step 2:''' Configure MySQL for safe startup by modifying <code>/etc/mysql/my.cnf</code> (or <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code>) and adding these lines under <code>[mysqld]</code>:
<syntaxhighlight lang="bash">
 
# 2. Add bypass config to /etc/mysql/my.cnf under [mysqld]:
<syntaxhighlight lang="ini">
[mysqld]
# Bypass authentication to allow connection during repair
skip-grant-tables
skip-grant-tables
# Disable performance schema to reduce startup load
performance_schema=OFF
performance_schema=OFF
# Force lower case table names (common fix for 5.x -> 8.0 mismatch)
lower_case_table_names=1
lower_case_table_names=1
# Skip network connections for safety mode
skip-networking
skip-networking
</syntaxhighlight>
</syntaxhighlight>
'''Step 3:''' Attempt to start MySQL:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# 3. Start MySQL and drop routine
systemctl start mysql
systemctl start mysql
</syntaxhighlight>
mysql -u root voipmonitor -e "DROP PROCEDURE IF EXISTS problematic_routine;"
 
'''Step 4:''' If MySQL starts, connect and drop the offending routine:
 
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>
 
'''Step 5:''' Remove the temporary configuration lines and restart MySQL normally:


<syntaxhighlight lang="bash">
# 4. Remove bypass config and restart normally
systemctl stop mysql
systemctl stop mysql
# Edit /etc/mysql/my.cnf to remove the temporary config lines
# Edit /etc/mysql/my.cnf - remove the 4 lines added above
systemctl start mysql
systemctl start mysql
</syntaxhighlight>
'''Step 6:''' Restart VoIPmonitor to recreate necessary routines:
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
systemctl restart voipmonitor
</syntaxhighlight>
</syntaxhighlight>
Line 306: Line 142:
== Prevention ==
== Prevention ==


To avoid this issue in future upgrades:
{{Tip|Always backup before major OS or MySQL upgrades.}}


{{Tip|Always perform full database backups before any major OS or MySQL upgrades.}}
* Create logical backups before upgrading:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql
mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql
</syntaxhighlight>
</syntaxhighlight>
* 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 [[Recovering_corrupted_database_tables|Database Backup and Restore]] for backup examples.


== Related Documentation ==
== See Also ==


* [[Recovering_corrupted_database_tables]] - Database corruption recovery and backup procedures
* [[Recovering_corrupted_database_tables]] - Database recovery and backup
* [[Database_troubleshooting]] - Common database issues
* [[Database_troubleshooting]] - Common database issues
* [[Sniffer_configuration]] - Sensor configuration options


== AI Summary for RAG ==
== 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.
'''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, mysqlcheck, mysqldump, migration instance, skip-grant-tables, lower_case_table_names, MySQL 5.7 to MySQL 8.0, character set error
'''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:'''
'''Key Questions:'''
Line 338: Line 165:
* Will VoIPmonitor recreate stored routines automatically?
* Will VoIPmonitor recreate stored routines automatically?
* Should I dump my database before MySQL 8 upgrade?
* 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 is the bypass method to fix MySQL startup failure?
* What error log indicates MySQL 8 stored routine issue?

Latest revision as of 16:48, 8 January 2026


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"