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.

We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication.

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

I found some useful information from this posting, but not specifically about replication. It seems like if you are upgrading, then you need to do the mysql_upgrade (with --force if needed) on that table. My guess is that the replication from 5.0 to 5.5 is like an upgrade...all of the time. So how would I do a mysql_upgrade here? If I do the upgrade followed by a slave start, will it be flushed out immediately as the data is still coming from a master without the upgrade?

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

    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)

This is fixed with a

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

and replication picks up again. Obviously not a full replica of data when doing this more than a few times.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The reason for the discrepancy is obvious.

When you run

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;

you get

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>

and

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>

Here is a quick-and-dirty way to fix mysql.proc. Please pay attention:

STEP 01 : On the Slave DB Server, run STOP SLAVE;

STEP 02 : Install MySQL 5.5.26 on a Staging DB Server

STEP 03 : Copy the following files from that Staging DB Server to the Slave DB Server

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

STEP 04 : On the Slave DB Server, run FLUSH TABLES;

STEP 05 : Dump the Stored Procedures on the Master DB Server

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

STEP 06 : Move StoredProc.sql from Master DB Server to /root on the Slave DB Server

STEP 07 : On the Slave DB Server, load the Stored Procedures

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

STEP 08 : On the Slave DB Server, run START SLAVE;

You should never encounter this issue any more.

Give it a Try !!!

UPDATE 2013-04-22 12:45 EDT

Please run this query

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

For MySQL 5.0, you should get 37. For MySQL 5.5, you should get 42.

If you get 37 on the MySQL 5.5. server, go ahead with running mysql_upgrade.

If you get 42, run my original answer to fix mysql.proc.

share|improve this answer
    
I get +--------------+------------------+ | MySQLVersion | MySQLProcColumns | +--------------+------------------+ | 5.0.45-log | 16 | +--------------+------------------+ and +--------------+------------------+ | MySQLVersion | MySQLProcColumns | +--------------+------------------+ | 5.5.28 | 16 | +--------------+------------------+ –  Justin Edmands Apr 22 '13 at 16:34
    
Just I figured. You have a MySQL 5.0 version of mysql.proc in the MySQL 5.5 database. –  RolandoMySQLDBA Apr 22 '13 at 16:40
    
yep. 37 on both servers. I will look further into the mysql_upgrade. –  Justin Edmands Apr 22 '13 at 17:09
    
+1 running mysql_upgrade against the slave should do it... assuming mysql_upgrade for 5.5 understands how to migrate all the way from 5.0; however, replication from 5.0 to 5.5 isn't officially a valid configuration (though it most likely should be fine) since: "MySQL supports replication from one major version to the next higher major version" dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html ... if the master can't be upgraded, the safest/most officially proper configuration for the slave would be the latest version of 5.1, not 5.5. –  Michael - sqlbot Apr 23 '13 at 1:59
    
looks like this did the trick for now. I haven't seen any skips in about 24 hours. I also added "slave-skip-errors = 1547" in the my.cnf because I was skipping it anyways. After a day or so I'll have to re-import the master and do the upgrade again to ensure I have an actual replica. –  Justin Edmands Apr 24 '13 at 16:51
show 1 more 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.