1
  • 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?

1
  • 1
    Just to be sure, what are results of SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='procedure' AND routine_schema='reportingdb' and SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='procedure' (procedures in reportingdb, total procedures respectively) ? – Derek Downey Oct 19 '12 at 13:01
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.