1

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'
10
  • Have you tried running DROP PROCEDURE IF EXISTS sp_myprocedure; before the execution of the procedure creation?
    – lemon
    Jan 17, 2023 at 18:02
  • @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. Jan 17, 2023 at 18:03
  • Look SHOW CREATE PROCEDURE sp_myprocedure;. Test CREATE PROCEDURE databasename.sp_myprocedure;.
    – Akina
    Jan 17, 2023 at 18:28
  • What version of MySQL are you using ??? Please run SELECT VERSION() MySQLVersion; and tell us. Jan 17, 2023 at 18:38
  • @RolandoMySQLDBA - I have added the versions involved to the post. Jan 17, 2023 at 18:41

1 Answer 1

3

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.

2
  • THANK YOU! Your guidance is spot on!!! That table was clearly corrupted. All my procs for the db are now listing again. Jan 17, 2023 at 18:53
  • 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. Jan 17, 2023 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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