MySQL 8 upgrade stored routine fix: Difference between revisions

From VoIPmonitor.org
(Create guide for fixing MySQL 8 startup failure after Ubuntu upgrade due to stored routines with utf8mb3 character set)
 
(Rewrite: konsolidace a vylepšení struktury - přidána Quick Reference tabulka, zjednodušen SQL pro drop routines, zkráceno o 50%)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
[[Category:Database]]
[[Category:Database]]
[[Category:Troubleshooting]]
[[Category:Troubleshooting]]
{{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 `utf8` character set (now called `utf8mb3` 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).


== Problem Symptoms =
== Quick Reference ==


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.
{| 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
|}


=== Error Logs =
<kroki lang="mermaid">
%%{init: {'flowchart': {'nodeSpacing': 15, 'rankSpacing': 30}}}%%
flowchart TB
    A[MySQL fails to start] --> B{Can revert?}
    B -->|Yes| C[Strategy 1: Pre-Upgrade Cleanup]
    B -->|No| D{Have backup?}
    D -->|Yes| E[Strategy 2: Dump & Restore]
    D -->|No| F{Temp server available?}
    F -->|Yes| G[Strategy 3: Migration Instance]
    F -->|No| H[Strategy 4: Startup Bypass]
</kroki>


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


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


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>:
<pre>
[ERROR] [MY-010957] [Server] Function/Procedure 'voipmonitor.function_name': Invalid utf8mb3 character string
</pre>


This error prevents MySQL from populating data dictionary tables during startup.
<syntaxhighlight lang="text">
[ERROR] [MY-011825] Invalid utf8mb3 character string
[ERROR] [MY-010020] Data Dictionary initialization failed
</syntaxhighlight>


== Root Cause =
=== 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.
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.


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.
{{Note|1='''VoIPmonitor automatically recreates stored routines''' with correct character sets after you drop them and restart the sensor.}}


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.
== Strategy 1: Pre-Upgrade Cleanup (Recommended) ==


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


There are three strategies to resolve this issue, ordered from recommended to least recommended.
'''Before Ubuntu upgrade''', connect to MySQL 5.x and drop all stored routines:


{{Note|VOIPMONITOR WILL AUTOMATICALLY RECREATE STORED ROUTES: 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.}}
<syntaxhighlight lang="bash">
mysql -u root -p voipmonitor
</syntaxhighlight>


---
<syntaxhighlight lang="sql">
 
-- List routines
== 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 ===
 
1. Before initiating the Ubuntu upgrade, connect to your MySQL 5.x server:
 
<pre>
mysql -u root -p
</pre>
 
2. Identify stored functions and procedures in the `voipmonitor` database:
 
<pre>
USE voipmonitor;
 
-- List all stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'voipmonitor';
 
-- List all stored functions
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';
</pre>
 
3. Drop all stored functions and procedures:
 
<pre>
-- 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 PROCEDURE STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';
SHOW FUNCTION STATUS WHERE Db = 'voipmonitor';
</pre>
4. Exit MySQL:
<pre>
EXIT;
</pre>
5. Proceed with the Ubuntu 18.04 to 20.04 upgrade:
<pre>
sudo do-release-upgrade
</pre>
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:
<pre>
# 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
</pre>
---
== Strategy 2: Dump and Restore (If Upgrade Already Failed) =
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
-- Drop all (repeat for each routine found above)
* Ability to revert the system to a pre-upgrade state
DROP PROCEDURE IF EXISTS routine_name;
DROP FUNCTION IF EXISTS function_name;


=== Steps ===
-- 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
</syntaxhighlight>


1. Revert the system to a pre-upgrade state (e.g., from a backup or snapshot).
Then proceed with <code>sudo do-release-upgrade</code>. MySQL 8.0 will start successfully.


2. Run `mysqlcheck` on the `voipmonitor` database to identify and fix potential issues:
== Strategy 2: Dump and Restore ==


<pre>
Use if you have a backup from before the upgrade.
mysqlcheck -u root -p --check-upgrade voipmonitor
</pre>


3. Create a logical backup of the `voipmonitor` database with options compatible with MySQL 8.0:
<syntaxhighlight lang="bash">
# 1. Revert to pre-upgrade state


<pre>
# 2. Create MySQL 8.0 compatible backup
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
</pre>
4. Drop all stored routines (see Strategy 1, Step 3) to prevent the character set issue:
<pre>
mysql -u root -p voipmonitor < <(cat <<'EOF'
-- Paste the stored routine drop commands from Strategy 1, Step 3 here
EOF
)
</pre>


5. Perform the Ubuntu 18.04 to 20.04 upgrade:
# 3. Drop stored routines (see Strategy 1)


<pre>
# 4. Upgrade OS
sudo do-release-upgrade
sudo do-release-upgrade
</pre>
6. After the upgrade completes, create the `voipmonitor` database if needed and restore data:


<pre>
# 5. 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
mysql -u root -p voipmonitor < voipmonitor_backup.sql
</pre>
7. Restart VoIPmonitor to recreate stored routines:


<pre>
# 6. Restart VoIPmonitor to recreate routines
systemctl restart voipmonitor
systemctl restart voipmonitor
</pre>
</syntaxhighlight>
 
---
 
== Strategy 3: VoIPmonitor 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 [[Sniffer_distributed_architecture#Migration_Instance distributed architecture]] documentation for details on using the migration feature.


---
== Strategy 3: Migration Instance ==


== Strategy 4: Startup Bypass and Manual Removal (Last Resort) =
Use if you cannot revert and have no backup, but have a temporary server available.


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.
# Set up VoIPmonitor on a temporary server with MySQL 8.0
# Use migration feature to copy CDRs from old server to temp server
# Upgrade main server (MySQL 8.0 starts with empty database)
# Copy CDRs back from temp server


{{Warning|This method can lead to data corruption if not done correctly. Use only as a last resort.}}
See [[Redundant_database|Database Migration]] for migration configuration details.


=== Steps ===
== Strategy 4: Startup Bypass (Last Resort) ==


1. Check the error log to identify the specific routine:
{{Warning|1=Risky method. Use only if all other strategies fail.}}


<pre>
<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
</pre>
</syntaxhighlight>
 
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]`:


<pre>
<syntaxhighlight lang="bash">
[mysqld]
# 2. Add bypass config to /etc/mysql/my.cnf under [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
</pre>
</syntaxhighlight>
 
3. Attempt to start MySQL:


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


<pre>
# 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
</pre>
6. Restart VoIPmonitor to recreate necessary routines:
<pre>
systemctl restart voipmonitor
systemctl restart voipmonitor
</pre>
</syntaxhighlight>
 
---
 
== Prevention =
 
To avoid this issue in future upgrades:


{{Note|Always perform full database backups before any major OS or MySQL upgrades.}}
== Prevention ==


* Create logical backups before upgrading: `mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql`
{{Tip|Always backup before major OS or MySQL upgrades.}}
* 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.
<syntaxhighlight lang="bash">
mysqldump -u root -p --routines --triggers --events voipmonitor > backup.sql
</syntaxhighlight>


== 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
* [[Ubuntu_20.04_LTS]] - Ubuntu 20.04 installation guide


== 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: "/var/log/mysql/error.log shows Invalid utf8mb3 character string" during MySQL startup. SOLUTION (4 strategies): (1) PRE-UPGRADE CLEANUP (RECOMMENDED) - Before Ubuntu upgrade, connect to MySQL 5.x, DROP ALL stored procedures and functions from voipmonitor database (VoIPmonitor will auto-recreate with correct charset), then upgrade; (2) DUMP & RESTORE - Revert to pre-upgrade, run mysqlcheck --check-upgrade, create mysqldump backup with --routines --triggers --events --default-character-set=utf8mb4, drop stored routines from old DB, upgrade OS, restore dump; (3) MIGRATION INSTANCE - Copy CDRs to temp server, upgrade main server, copy CDRs back (avoids stored routine issue); (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. PREVENTION: Always take mysqldump backup before major upgrades, test on non-production first. Related: Recovering_corrupted_database_tables for backup procedures.
'''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:'''
* Why does MySQL fail to start after Ubuntu 18.04 to 20.04 upgrade? (Stored routines use deprecated utf8/utf8mb3, MySQL 8.0 rejects them)
* 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? (Drop stored routines BEFORE upgrade - Strategy 1 recommended)
* How do I fix MySQL 8 startup failure due to invalid utf8mb3 character string?
* How do I drop all stored procedures/functions from voipmonitor database? (USE INFORMATION_SCHEMA to query ROUTINES, loop and DROP)
* How do I drop all stored procedures and functions from voipmonitor database?
* Will VoIPmonitor recreate stored routines automatically? (Yes, sensor recreates necessary routines with correct charset after startup)
* Will VoIPmonitor recreate stored routines automatically?
* Should I dump my database before MySQL 8 upgrade? (Yes - Strategy 2 recommends mysqldump with --routines --triggers --events --default-character-set=utf8mb4)
* Should I dump my database before MySQL 8 upgrade?
* Can I use VoIPmonitor migration instance to avoid this issue? (Yes - Strategy 3: copy CDRs to temp server, upgrade, copy back)
* What is the bypass method to fix MySQL startup failure?
* What is the bypass method to fix MySQL startup failure? (Add skip-grant-tables, performance_schema=OFF, lower_case_table_names=1 to my.cnf, start MySQL, manually drop routine)
* What error log indicates MySQL 8 stored routine issue? ("Invalid utf8mb3 character string" in /var/log/mysql/error.log)

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"