Column count of mysql.proc is wrong. Expected 20,

问题:

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? Thank you.

Edited: Here is my code

<?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();
?>

回答1:

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

回答2:

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

回答3:

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

回答4:

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

Hope it will help you