Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

All we need is an easy explanation of the problem, so here it is.

Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we’ve been running into an error that repeats itself when we perform certain actions:

  • Attempting to view or run a stored procedure
  • Attempting to insert into a table after adding a trigger for ON INSERT actions
  • Executing SELECT LENGHT(description), id FROM table (with the misspelling… when LENGTH was spelled correctly, the error did not display)

The error in question:

Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

This has been reported as a bug (basically, lack of backwards compatibility), but there appears to be some confusion as to how to resolve the issue:

  • MySQL’s documentation says the solution is to dump your stored procedures again after the upgrade to 5.1, then restore them again. The bug report I mentioned earlier shows that this has dubious results.
  • The bug report suggests running mysql_upgrade as a possible solution (albeit with some concerning warnings). However, it has been reported elsewhere that this solution doesn’t always work.

I am in the unfortunate circumstance of not having a test box under which to test possible solutions as our development box is pending an OS upgrade to allow us to install the versions of MySQL we want to install. I am hesitant to try any solution on our production environment unless it has been verified as working by people who have encountered this issue.

What is the correct way to resolve this issue outside of waiting for an official patch or solution from MySQL?

While losing our stored procedures is not ideal, it is also a route we are willing to take to resolve the issue provided that there are no better alternatives and it will solve the issue cleanly.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Install a fresh copy of the same version of MySQL on another computer and copy its mysql.proc table to your broken server. That’ll get you a clean, correct table. Then, recreate your stored procedures.

Method 2

I was having this issue, and thought I’d mention that running “mysql_upgrade –force” fixed it for me.

I upgraded from 5.0 to 5.1 to 5.5. The interesting this is that I had already run mysql_upgrade which is why I needed to add the –force option. Hope this helps someone.

Method 3

Installed Suse Linux 11.4 on my new development system. The MySQL database was an upgrade from the previous version on my old Suse 11.1 box. After copying my PHP sources and configuring my Apache and PHP servers, I found that MySQL was complaining about the mysql.proc file having the wrong number of columns when I hit a page that attempted to connect to the database.

The mysql_upgrade command was then balking about my connection although I had it set up in MySQL Workbench. A quick and dirty way to fix the new version of MySQL was to use the MySQL Workbench to create a “routine” otherwise called a stored procedure. It was nothing more than a select on a table.

The page hitting the database then came right up. The new database version rewrote the mysql.proc table (or file) with the correct number of columns because I created a new stored procedure. I back up all the code for the old ones, so after that it was merely a matter of reloading them.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply