MySQL procedure exists, but doesn't

Question

I am attempting to add a stored procedure to a database that does not show that any exist in tree (Workbench):

enter image description here

As this screenshot of tree shows.

Also, executing:

SHOW PROCEDURE STATUS where Db = 'my_database_name';

returns 0 records.

But when I attempt to create a procedure like:

DELIMITER $$
CREATE PROCEDURE `sp_myprocedure`(
 IN prm_id int,
 IN prm_active boolean
 )
BEGIN 
 
 # a select statement returning some records
END$$
DELIMITER ;

throws error:

Error Code: 1304. PROCEDURE sp_myprocedure already exists 0.0097 sec

I then checked for corruption:

mysqlcheck

for the database in question and it returned tables - all ok:

...
my_database_name.my_table_name OK
...

It does not appear to be corrupted, so I am very confused.

• MySQL 5.7.34
• Workbench 8.0.31

What is going on here?

Executing

SHOW CREATE TABLE mysql.proc;

returns

CREATE TABLE `proc` (
 `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `name` char(64) NOT NULL DEFAULT '',
 `type` enum('FUNCTION','PROCEDURE') NOT NULL,
 `specific_name` char(64) NOT NULL DEFAULT '',
 `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 `param_list` blob NOT NULL,
 `returns` longblob NOT NULL,
 `body` longblob NOT NULL,
 `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
 `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `body_utf8` longblob,
 PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

• lemon: Have you tried running DROP PROCEDURE IF EXISTS sp_myprocedure; before the execution of the procedure creation?
• Roy Hinkley: @lemon - I could do that, but it does not answer my question as to why I am not finding the stored procedures in Workbench, and why this weird state. I would like to understand this situation, not just brute force my way around it.
• Akina: Look SHOW CREATE PROCEDURE sp_myprocedure;. Test CREATE PROCEDURE databasename.sp_myprocedure;.
• RolandoMySQLDBA: What version of MySQL are you using ??? Please run SELECT VERSION() MySQLVersion; and tell us.
• Roy Hinkley: @RolandoMySQLDBA - I have added the versions involved to the post.

Answer 1

You should fix the table that stores the Stored Procedures

REPAIR TABLE mysql.proc;

This should clear it up.

Since the mysql.proc table is MyISAM, it probably got corrupted. This is easy to happen. When the header file for a MyISAM table (proc.MYD) does not have the correct number of open file handles written in it, it is marked as crashed.

You have a new problem. MySQL 5.7 should have InnoDB as the storage engine. You should convert that to InnoDB.

• Roy Hinkley: THANK YOU! Your guidance is spot on!!! That table was clearly corrupted. All my procs for the db are now listing again.
• Roy Hinkley: I will consider ISAM --> InnoDB at a future date, but I've been thrown off schedule by this corruption and need to get back on task.