Solving the Infamous “Error #1558: Column count of mysql.proc is wrong” with mysql_upgrade: A Step-by-Step Guide

If you’re reading this, chances are you’ve stumbled upon the frustrating “Error #1558: Column count of mysql.proc is wrong. Expected 21, found 20” message while trying to access your MySQL database. Don’t worry, you’re not alone! This error is more common than you think, and the solution might be simpler than you expect. In this article, we’ll explore the root cause of this error, why running mysql_upgrade doesn’t always work, and provide a comprehensive, step-by-step guide to resolving this issue once and for all.

What causes the “Error #1558: Column count of mysql.proc is wrong”?

The error message itself is quite cryptic, but it’s actually telling us that the MySQL procedure table (mysql.proc) has an incorrect column count. This usually occurs when there’s a mismatch between the MySQL server version and the database structure. Often, this happens when:

• You’ve upgraded your MySQL server version, but the database structure hasn’t been updated accordingly.
• You’ve restored a database backup from an older MySQL version.
• You’ve imported a database from a different MySQL server with a different version.

In each of these cases, the mysql.proc table becomes outdated, leading to the dreaded “Error #1558”.

Why doesn’t running mysql_upgrade solve the issue?

Running the mysql_upgrade command is the most common solution suggested for this error. However, it might not work in all cases, especially if:

• You’re running an older MySQL version (pre-5.6).
• The mysql.proc table is heavily customized or has been altered.
• There are issues with the MySQL server’s metadata.

In such cases, running mysql_upgrade might not be enough to resolve the issue. That’s where our step-by-step guide comes in!

Step-by-Step Solution to Resolving “Error #1558”

Before we dive into the solution, make sure you have a backup of your database. Yes, you read that right – a backup! We’re about to make some changes to your database structure, and it’s always better to be safe than sorry.

Step 1: Check your MySQL server version

First, let’s verify your MySQL server version using the following command:

mysql -V

This will display your current MySQL server version. Take note of it, as we’ll need it later.

Step 2: Dump the mysql database

Next, we’ll dump the entire mysql database using the following command:

mysqldump -u root -p mysql > mysql_dump.sql

Replace “root” with your MySQL root username and enter the corresponding password when prompted. This will create a file named mysql_dump.sql in your current directory.

Step 3: Update the mysql.proc table

Now, we’ll update the mysql.proc table to match the correct column count. Create a new file named update_proc.sql with the following contents:

USE mysql;

ALTER TABLE proc
DROP COLUMN db;

ALTER TABLE proc
ADD COLUMN db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER routine_name;

ALTER TABLE proc
DROP COLUMN type;

ALTER TABLE proc
ADD COLUMN type enum('FUNCTION','PROCEDURE') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER db;

This script will add the missing columns to the mysql.proc table. You can adjust the CHARACTER SET and COLLATE settings according to your database’s needs.

Step 4: Apply the updates

Run the following command to apply the updates:

mysql -u root -p mysql < update_proc.sql

Again, replace "root" with your MySQL root username and enter the corresponding password when prompted.

Step 5: Reload the mysql database

Finally, reload the mysql database using the following command:

mysql -u root -p mysql < mysql_dump.sql

This will re-import the updated mysql database.

Post-Solution Checks

After completing the above steps, verify that the error has been resolved by running the following command:

mysql -u root -p mysql -e "SHOW TABLES" | grep proc

This should display the mysql.proc table with the correct column count. If you still encounter issues, you can try running mysql_upgrade again or seek further assistance from a MySQL expert.

Conclusion

Resolving the "Error #1558: Column count of mysql.proc is wrong" might seem daunting, but by following this step-by-step guide, you should be able to overcome this hurdle. Remember to backup your database, update the mysql.proc table, and reload the mysql database to ensure a smooth resolution. If you're still struggling, don't hesitate to reach out to the MySQL community or a qualified database administrator for further assistance.

MySQL Version	Solution Applicability
pre-5.6	Partially applicable; might require additional steps.
5.6 and above	Fully applicable; should resolve the issue.

By following these instructions, you should be able to resolve the "Error #1558" and get your MySQL database up and running smoothly. Happy troubleshooting!

Frequently Asked Question

Get answers to your questions about resolving the "Error #1558 - Column count of mysql.proc is wrong. Expected 21, found 20" error with mysql_upgrade.