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

I am using 000webhost.com and I am using phpMyAdmin there. I am getting this error from MySQL when I run my PHP script as the title says:

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

Is there any solution for this?

<?php
$username="usrname";
$password="passwd";
$database="a1xxxxx_mydb";
$host="mysqlxx.000webhost.com";
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if (isset($_GET["userLatitude"]) && isset($_GET["userLongitude"])) {

 $userLatitude=$_GET['userLatitude']; 
 $userLongitude=$_GET['userLongitude']; 
 $result = mysql_query("SELECT locationName, ( 6371 * acos( cos( radians(floatval(     $userLatitude) )) * cos( radians( locationLatitude ) ) * cos( radians( locationLongitude ) - radians( floatval($userLatitude)) ) + sin( radians(floatval($userLongitude)) ) * sin( radians( locationLatitude) ) ) ) AS distance 
         FROM Location HAVING distance < 2 ORDER BY distance LIMIT 0 ,20") or die(mysql_error()); 
echo $result;

 // check for empty result
if (mysql_num_rows($result) > 0) {
   // looping through all results
   // products node
  $response["Location"] = array();

  while ($row = mysql_fetch_array($result)) {
    // temp user array
    $product = array();
    $product["locationName"] = $row["locationName"];
    $product["locationInfo"] = $row["locationInfo"];
    $product["locationLatitude"] = $row["locationLatitude"];
    $product["locationLongitude"] = $row["locationLongitude"];
    $product["locationPic"] = $row["locationPic"];
    $product["city"] = $row["city"];

    // push single product into final response array
    array_push($response["Location"], $product);
 }
 // success
 $response["success"] = 1;

   // echoing JSON response
   echo json_encode($response);
 } else {
// no products found
$response["success"] = 0;
$response["message"] = "No products found";

// echo no users JSON
echo json_encode($response);
 }
 }
  else {
    // required field is missing
   $response["success"] = 0;
   $response["message"] = "Required field(s) is missing";

  // echoing JSON response
  echo json_encode($response);
 }
mysql_close();
?>

I had this error as well. I fixed it by running

mysql_upgrade -u root -p

Also, restart the mysql service by running

service mysqld restart

I had the same problem when I updated XAMPP from xampp-osx-7.2.10 to 7.3.9 in MacOS Mohave. So the solution was:

Find the file mysql_upgrade in "Macintosh HD⁩ ▸ ⁨Applications⁩ ▸ ⁨XAMPP⁩ ▸ ⁨xamppfiles⁩ ▸ ⁨bin⁩" and double click on it.

This error happens when a bad upgrade is done. For example, it happens if you upgrade from 5.0 to 5.1 but don't run the mysql_upgrade script; or, in rare cases, it probably happens if you directly upgrade from 5.0 to 5.5. (Many people does this, but such updrages are not officially supported) You say you are using a hosting service - well, I think that you should create a ticket and tell them about the problem. If you don't have the SUPER privilege, there is nothing you can do. But if you have that right, simply run mysql_upgrade: http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

A similar error appeared in MySQL Workbench 8.0.21 after installing XAMPP 7.4.8 on Ubuntu 18.04. There were no problems using phpMyAdmin.

Error Code: 1558 Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 100108, now running 100413. Please use mysql_upgrade to fix this error

Solution:

sudo /opt/lampp/lampp start
/opt/lampp/bin/mysql_upgrade

Problem solved

Credits: There appear to be a bug in XAMPP - https://community.apachefriends.org/f/viewtopic.php?f=17&t=78386&sid=3d3824dd0b6aa2e33c3adc73c744b4b4

I had the same problem when I updated the mysql server from 5.5 to 5.7 in Debian 8 (jessie). In my case, it worked fine when I executed the follow command:

mysql_upgrade --force -uroot -p

I am using xampp on Ubuntu 20.04 and the my problem that return this same message error was solved with this solution:

1. Go to the xampp directory: cd /opt/lampp/
2. Go to the bin directory: cd bin
3. Execute the script: sudo ./mysql_upgrade

Credits: https://askubuntu.com/questions/1171409/how-to-run-mysql-upgrade-when-using-xampp

Although you may be correct about the necessity for upgrade, that's not the only reason this error occurs.

When the following is called with a query that returns 1 row

 my $rv = $sth_indexq->fetchall_arrayref;

the following error is reported:

DBD::mysql::st execute failed: Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50520, now running 50528. Please use mysql_upgrade to fix this error. at 
...

However, the real cause of the error was use of fetchall_arrayref instead of fetchrow_arrayref. The following worked without errors:

my $rv = $sth_indexq->fetchrow_arrayref;

The data in $rv was only 1 level deep, not 2.

The mysql_upgrade solution may very well solve this issue, but the simple solution is know your data and use the right retrieval code.

J.White

I was using a windows 10 system and the solution that worked for me was mysql_upgrade -u root -p

But you need to ensure that the path to the mysql_upgrade script which is present in the mysql/bin folder of your installation directory needs to be added in the environment variable path for this command to work

Hit on browser below command

/opt/lampp/bin/mysql_upgrade

For Linux

If you are using LAMPP, then you can fix it like -

sudo /opt/lampp/lampp start
/opt/lampp/bin/mysql_upgrade

else if you installed mysql separately, then you can like this -

mysql_upgrade -u root -p 
service mysqld restart

For any other OS, you can simply go to bin directory of lampp installation & try to find mysql_upgrade file & execute it.

This my happen when eg. upgrading instead of migrating;

The part where it reads version check failed is the relevant part:

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
FATAL ERROR: Upgrade failed

Obviously this is about an incompatible client version, but the mysql database is also different.

This here would be the current definition of the mysql.proc table ... which shows that enum column type has changed and that column aggregate has been added, since MariaDB 10.3.3.

Yet another problem is:

> In MariaDB 10.4 and later, this table uses the Aria storage engine.

Downgrading the server to 5.x and deleting the InnoDB log files, then restoring table proc helped; (for MariaDB 5.x): https://github.com/google/mysql/blob/master/scripts/mysql_system_tables.sql