我們正在通過 WAN 從 mysql 5.0.45 主機複製到 5.5.26 主機。每隔一段時間，我們的一個數據庫不接受查詢並停止複制。

“‘mysql.proc 的列數錯誤。預期為 20，實際為 16。查詢時表可能已損壞。”

我從這篇文章中找到了一些有用的資訊，但不是專門關於複製的。似乎如果您正在升級，那麼您需要在該表上執行 mysql_upgrade （如果需要，使用 –force ）。我的猜測是，從 5.0 到 5.5 的複制就像升級……一直都是。那麼我將如何在這裡進行 mysql_upgrade 呢？如果我在從啟動後進行升級，是否會立即刷新數據，因為數據仍來自未升級的主控？

將 MySQL 從 5.0.* 升級到 5.1.* 後解決 mysql.proc 的問題

   mysql> show slave status\G
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
              Slave_IO_State: Connecting to master
                 Master_Host: 192.168.15.61
                 Master_User: replica
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000016
         Read_Master_Log_Pos: 1054186012
              Relay_Log_File: mysql-relay-bin.000431
               Relay_Log_Pos: 39375086
       Relay_Master_Log_File: mysql-bin.000016
            Slave_IO_Running: Connecting
           Slave_SQL_Running: Yes
             Replicate_Do_DB: 
         Replicate_Ignore_DB: 
          Replicate_Do_Table: 
      Replicate_Ignore_Table: 
     Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
                  Last_Errno: 1547
                  Last_Error: Error 'Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted' on query. Default database: 'ResumeDB'. Query: 'INSERT INTO `search` (zip_code,lat,lon,dist)
SELECT zipcode,latitude,longitude,GetDistance( NAME_CONST('lat1',32.84), NAME_CONST('long1',-83.68),B.latitude,B.longitude) as dist
FROM zipcodes AS B
WHERE
B.latitude BETWEEN  NAME_CONST('lat1',32.84)-( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457)) AND  NAME_CONST('lat1',32.84)+( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457))
AND B.longitude BETWEEN  NAME_CONST('long1',-83.68)-( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457)) AND  NAME_CONST('long1',-83.68)+( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457))
AND GetDistance( NAME_CONST('lat1',32.84), NAME_CONST('long1',-83.68),B.latitude,B.longitude) <=  NAME_CONST('range',25) ORDER BY dist'
                Skip_Counter: 1
         Exec_Master_Log_Pos: 1053930692
             Relay_Log_Space: 39635387
             Until_Condition: None
              Until_Log_File: 
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File: 
          Master_SSL_CA_Path: 
             Master_SSL_Cert: 
           Master_SSL_Cipher: 
              Master_SSL_Key: 
       Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error: 
              Last_SQL_Errno: 1547
              Last_SQL_Error: Error 'Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted' on query. Default database: 'ResumeDB'. Query: 'INSERT INTO `search` (zip_code,lat,lon,dist)
SELECT zipcode,latitude,longitude,GetDistance( NAME_CONST('lat1',32.84), NAME_CONST('long1',-83.68),B.latitude,B.longitude) as dist
FROM zipcodes AS B
WHERE
B.latitude BETWEEN  NAME_CONST('lat1',32.84)-( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457)) AND  NAME_CONST('lat1',32.84)+( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457))
AND B.longitude BETWEEN  NAME_CONST('long1',-83.68)-( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457)) AND  NAME_CONST('long1',-83.68)+( NAME_CONST('range',25)* NAME_CONST('rangeFactor',0.014457))
AND GetDistance( NAME_CONST('lat1',32.84), NAME_CONST('long1',-83.68),B.latitude,B.longitude) <=  NAME_CONST('range',25) ORDER BY dist'
 Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 40
1 row in set (0.00 sec)

這是固定的

mysql > slave stop; SET GLOBAL sql_slave_skip_counter = 1; slave start;

複製又開始了。當多次這樣做時，顯然不是數據的完整副本。

差異的原因是顯而易見的。

當你跑

SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
(SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='proc') B;

你得到

mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
   -> (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
   -> WHERE table_schema='mysql' AND table_name='proc') B;
+-----------------------+------------------+
| MySQLVersion          | MySQLProcColumns |
+-----------------------+------------------+
| 5.0.51a-community-log |               16 |
+-----------------------+------------------+
1 row in set (0.54 sec)

mysql>

和

mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
   -> (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
   -> WHERE table_schema='mysql' AND table_name='proc') B;
+--------------+------------------+
| MySQLVersion | MySQLProcColumns |
+--------------+------------------+
| 5.5.8-log    |               20 |
+--------------+------------------+
1 row in set (0.01 sec)

mysql>

這是一種快速而骯髒的修復方法mysql.proc。請注意：

STEP 01 : 在從數據庫伺服器上，執行STOP SLAVE;

步驟 02：在暫存數據庫伺服器上安裝 MySQL 5.5.26

步驟 03：將以下文件從該暫存數據庫伺服器複製到從數據庫伺服器

• /var/lib/mysql/mysql/proc.frm
• /var/lib/mysql/mysql/proc.MYD
• /var/lib/mysql/mysql/proc.MYI

步驟 04 : 在從數據庫伺服器上，執行FLUSH TABLES;

步驟 05：將儲存過程轉儲到主數據庫伺服器上

MYSQLDUMP_OPTIONS="--routines --no-data --no-create-info --all-databases"
mysqldump -uroot -p ${MYSQLDUMP_OPTIONS} > StoredProc.sql

步驟 06：StoredProc.sql從主數據庫伺服器移動到從數據庫伺服器上的 /root

步驟 07 : 在從數據庫伺服器上，載入儲存過程

mysql -uroot -p -A < /root/StoredProc.sql

步驟 08：在從數據庫伺服器上，執行START SLAVE;

你不應該再遇到這個問題了。

試一試 ！！！

更新 2013-04-22 12:45 EDT

請執行此查詢

SELECT COUNT(1) FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

對於 MySQL 5.0，你應該得到 37。對於 MySQL 5.5，你應該得到 42。

如果你在 MySQL 5.5 上得到 37。伺服器，繼續執行mysql_upgrade。

如果你得到 42，執行我原來的答案來修復mysql.proc.

引用自：https://dba.stackexchange.com/questions/40616