Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
This only happens with stored procedures? –  eiefai Jan 28 '11 at 2:43
    
I have updated the beginning of the question to list the three occurrences in which we have seen the error. –  Shaun Jan 28 '11 at 3:19
    
did you ever get a better resolution to this topic? –  jcolebrand Feb 17 '11 at 0:20
    
@jcolebrand Unfortunately, no. We're in the process of setting up a new development server with MySQL 5.1.49 on it so I can test Dan's suggestion. –  Shaun Feb 17 '11 at 0:22
    
Well do keep us posted. I'm curious to hear how it works out. –  jcolebrand Feb 17 '11 at 0:25
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
This will likely be the solution we employ provided there's not a better solution that doesn't involve another instance of MySQL to repair. –  Shaun Jan 28 '11 at 17:24
add comment

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.

share|improve this answer
    
Tnx I solved with "mysql_upgrade --force -p" –  user7395 Mar 13 '12 at 17:33
    
This solved my error too after upgrading Ubuntu 11.10 => 12.04. My error was mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM PARAMETERS`': Cannot load from mysql.proc. The table is probably corrupted (1548)` and occured when executing /usr/bin/mysqldump --single-transaction -aep --databases information_schema –  jippie May 14 '12 at 20:05
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.