`show procedure status` doesn't list all of the procedures?

Question

• mysql-server-5.5.28-1.el5.remi

I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of them.

mysql> show procedure status where db='reportingdb';

...
| reportingdb | 7k_cronjob_log                             | PROCEDURE | user@host | 2012-10-17 10:10:55 | 2012-10-17 10:10:55 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-------------+--------------------------------------------+-----------+----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
146 rows in set (0.05 sec)

As you can see 7k_cronjob_log is the last procedure and there is only 146 rows. But let's see what happens:

mysql> show create procedure reportingdb.warningserver_getmaxtime_v2\G
*************************** 1. row ***************************
           Procedure: warningserver_getmaxtime_v2
            sql_mode: NO_AUTO_VALUE_ON_ZERO
    Create Procedure: CREATE DEFINER=`user`@`host` PROCEDURE `warningserver_getmaxtime_v2`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
    SELECT `LastTimeCalc` FROM `000_sys_params_v3`;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Any thoughts?

Answer 1

Only thing I can think of is that your mysql.proc database might be corrupt. Try with:

ANALYZE TABLE mysql.proc;

If corrupt, you can try to repair it:

REPAIR TABLE mysql.proc;