MySQL Bugs: #87285: Cannot load from mysql.proc. The table is probably corrupted

Bug #87285	Cannot load from mysql.proc. The table is probably corrupted
Submitted:	2 Aug 2017 15:21	Modified:	3 Sep 2017 14:56
Reporter:	Manojkumar Gali	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:		OS:	Red Hat
Assigned to:		CPU Architecture:	Any

Description:
Hello, 

After upgrade from 5.6 to 5.7.18, ran mysql_upgrade. that gave the error as 
Cannot load from mysql.proc. The table is probably corrupted. 

Tried running mysql_upgrade --force too, still it is giving same error. 

How to repeat:
Have the root access, tried running mysq_upgrade as a root user and as well as mysql user still it is not resolving

Hi!

Can you please try to run these queries twice:

SHOW CREATE TABLE mysql.proc;

SELECT * FROM mysql.proc;

Once with latest 5.6 and once with latest 5.7.

Thanks in advance.

Create Table: CREATE TABLE `proc` (
  `db` char(64) 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) 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 NOT NULL,
  `character_set_client` char(32) DEFAULT NULL,
  `collation_connection` char(32) DEFAULT NULL,
  `db_collation` char(32) DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stored Procedures'
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.proc\G
Empty set (0.00 sec)

Hi!

As I wrote before, we need this output from both 5.6 and 5.7.

Beside that, with both versions, please run CHECK TABLE on that particular table.

Sorry about that. We already upgraded that server version. so we no longer have 5.6 now.

Hi!

As this is MyISAM table , you can always download latest 5.6 package, create data directory, save mysql.proc , copy the one from 5.7 data directory and do as I wrote to you.

Also, as this table is MyISAM you can run CHECK TABLE with both versions. We do need the output from this command. If CHECK TABLE finds nothing wrong, you can create a stored routine and see if it works.

Hello,

Thanks for quick responses. 

I am just rephrasing you comment. 

Should i download mysql 5.6 to my server and install it to different directory and then do as you said?

Thanks,
Manoj.

Hi,

Yes, of course, in totally different directory or different machine, even.

And follow all other of my instructions .....

Thanks. 

I will install latest 5.6 and will let you know what happens. 

Regards, 
Manoj.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".