Do Not Upgrade to Any Version of MySQL After 8.0.37

In the world of database management, stability and reliability are paramount. Recently, a significant issue has been discovered that affects several versions of MySQL, causing concern within the database community. This blog post delves into the details of the problem, its implications, and why it’s crucial to avoid upgrading to any version of MySQL after 8.0.37 for the time being.

A Serious Bug in MySQL: What You Need to Know

Recently, Jean-François Gagné reported a serious bug on bug.mysql.com (#115517). Although the specifics of the bug report are now private, the impact of the bug is clear and concerning. The issue has been confirmed by Percona through various tests, leading to the opening of issue PS-9306 for further investigation.

The Nature of the Problem

The core of the problem lies in the MySQL daemon’s inability to handle a large number of tables. Specifically, if you create around 10,000 tables and then attempt to restart the MySQL server, the daemon crashes. This issue has been identified in the following MySQL versions:

• MySQL 8.0.38
• MySQL 8.4.1
• MySQL 9.0.0

Reproducing the Issue

For those who are interested in testing this issue, you can follow these steps:

1. Install the Affected MySQL Version:

   sudo docker run --name mysql-9.0.0 -p 3306:3306 -p 3060:3060 -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD='mysql' -d mysql:9.0.0 --log-error-verbosity=3

2. Verify Docker is Running:

   sudo docker ps

3. Create 12,000 Tables Using a Script:
   Ensure you have the MySQL client available in your PATH, then run the following bash script:

   #!/bin/bash

   # MySQL connection details
   MYSQL_HOST="127.0.0.1"
   MYSQL_PORT="3306"
   MYSQL_USER="root"
   MYSQL_PASSWORD="mysql"
   MYSQL_DATABASE="test"

   # Number of tables to create
   NUM_TABLES=12000
   THREADS=16

   # MySQL command to execute
   MYSQL_CMD="mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD"

   # Check MySQL connection
   echo "Checking MySQL connection..."
   echo "SELECT 1;" | $MYSQL_CMD 2>/dev/null
   if [ $? -ne 0 ]; then
       echo "Error: Unable to connect to MySQL. Please check your connection details."
       exit 1
   fi

   # Create database if it doesn't exist
   echo "Creating database if it doesn't exist..."
   echo "CREATE DATABASE IF NOT EXISTS $MYSQL_DATABASE;" | $MYSQL_CMD

   # Use the created or existing database
   MYSQL_CMD="$MYSQL_CMD $MYSQL_DATABASE"

   # Function to create a table
   create_table() {
       local table_name="table_$1"
       local sql="CREATE TABLE IF NOT EXISTS $table_name (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(255));"
       echo "$sql" | $MYSQL_CMD
       if [ $? -eq 0 ]; then
           echo "success"
       else
           echo "failure"
       fi
   }

   export -f create_table
   export MYSQL_CMD

   # Generate a sequence of table numbers and run the create_table function in parallel
   success_count=$(seq 1 $NUM_TABLES | parallel -j $THREADS create_table | grep -c "success")

   echo "Completed creating $success_count tables out of $NUM_TABLES."

4. Restart the MySQL Server:

   sudo docker stop mysql-9.0.0
   sudo docker start mysql-9.0.0
   sudo docker ps  # Verify the MySQL server status

After running these steps, you will notice that the MySQL server crashes and refuses to start, confirming the issue.

The Technical Details

The error log typically shows something similar to the following:

2024-07-11T13:03:54.829637Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
2024-07-11T13:03:54Z UTC - mysqld got signal 11 ;
Signal SIGSEGV (Address not mapped to object) at address 0x508
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=16f2b92d4b462f8210539e0b256540bb5c12320c
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x100000

Conclusion

Given the severity of this issue and the lack of a current workaround or fix, it is strongly advised that users do not upgrade to any MySQL version beyond 8.0.37. Until the root cause is identified and a solution is provided, maintaining a stable and functional database environment is critical. Stay tuned for updates from the MySQL development team and Percona as they work to resolve this problem.