Bug #59292 Getting an error trying to reverse engineer 5.5 database using Workbench 5.2
Submitted: 4 Jan 2011 20:44 Modified: 27 Jan 2011 10:15
Reporter: Mohamed Elmallah Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:5.2.31 OS:Windows (Windows 7)
Assigned to: CPU Architecture:Any

[4 Jan 2011 20:44] Mohamed Elmallah
Description:
I'm getting the following error trying to reverse engineer my 5.5.8 database into a model using Workbench 5.2.31:

Fetching table list.
193 items from rcptest_reoremodel
OK
Fetching view list.
0 items from rcptest_reoremodel
OK
Fetching routine list.
Operation failed: Cannot load from mysql.proc. The table is probably corrupted

I can reverse engineer my 5.0.x databases okay.

How to repeat:
The error happens each time I try the reverse engineer the 5.5 database

Suggested fix:
I used TOAD Data Modeler to reverse engineer it
[5 Jan 2011 11:51] Valeriy Kravchuk
Please, send the results of

show create table mysql.proc\G

from your server. I had no problems with my 5.5.8.
[5 Jan 2011 17:37] Mohamed Elmallah
Find result below (the one thing I noticed, even though our default engine is innodb, the engine specified in the create was myISAM, not sure it is an issue though.

Thanks in advance
Mohamed

Create Table: 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(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES
TAMP,
  `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','PO
STGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO
_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLAS
H_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` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NU
LL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NU
LL,
  `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'

mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)
[5 Jan 2011 19:33] Valeriy Kravchuk
All tables in mysql database use MyISAM storage engine, and you should never change that.

Your mysql.proc table looks correct. I wonder why I can not repeat this though. The only difference in XP in my case and Windows 7 in your case.
[5 Jan 2011 19:33] Valeriy Kravchuk
Please, send error log of your MySQL server.
[5 Jan 2011 19:43] Mohamed Elmallah
Can you tell me which files exactly you need?
sorry, I'm relatively new to mySQL.
[5 Jan 2011 21:28] Mohamed Elmallah
mysqld.log

Attachment: mysqld.log (application/octet-stream, text), 52.46 KiB.

[5 Jan 2011 21:29] Mohamed Elmallah
I have uploaded /var/log/mysqld.log
Let me know if you need another file
[24 Jan 2011 2:51] Adi Luhung Suryadi
I have the same problem, when i want to synchronize model using WorkBench 5.2.31 (7110) and MySQL 5.5.8 64bit Mac OS X Snow 10.6.5

(x) Retrieve Objects from Selected Schemata
(x) Check Results

Fetching table list.
    19 items from kusmayadi_wisata
OK
Fetching view list.
    0 items from kusmayadi_wisata
OK
Fetching routine list.
Operation failed: Cannot load from mysql.proc. The table is probably corrupted

Server version: 5.5.8-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table mysql.proc\G;
*************************** 1. row ***************************
       Table: proc
Create Table: 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(77) 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` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `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'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> status;
--------------
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.5.8, for osx10.6 (i386) using readline 5.1

Connection id:		6
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.8-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			15 min 30 sec

Threads: 1  Questions: 81  Slow queries: 0  Opens: 92  Flush tables: 1  Open tables: 47  Queries per second avg: 0.87
--------------

mysql>
[24 Jan 2011 3:32] Adi Luhung Suryadi
simply this one given an error too:

mysql>  SELECT * FROM information_schema.routines WHERE routine_schema = 'mysql' ORDER BY routine_name;
ERROR 1548 (HY000): Cannot load from mysql.proc. The table is probably corrupted
[25 Jan 2011 11:24] Gregorio Hernández Caso
Hi all,

I had the same problem and I have fixed it simply doing a mysql_upgrade on the server.
[25 Jan 2011 19:02] Mohamed Elmallah
We executed mysql_upgrade, it is working fine now.

Thanks
[26 Jan 2011 0:06] Adi Luhung Suryadi
Nice.. thanks.
[26 Jan 2011 10:47] Valeriy Kravchuk
Mohamed,

So, looks like this problem was a result of corrupted table on server, not some bug in Workbench. Please, confirm.
[26 Jan 2011 19:27] Mohamed Elmallah
This was not Workbench problem.
[27 Jan 2011 10:15] Valeriy Kravchuk
This problem was not a result of any bug in the Workbench code.
[8 Sep 2011 5:45] Zahid Quadri
can any one tell me how to run mysql_upgrade on windows xp. i tried its giving error.
[20 Dec 2011 15:20] Daniel Weaver
I had the same problem, in looking at the mysql_error_log I found this statement:

[ERROR] Incorrect definition of table mysql.proc: expected column 'comment' at position 15 to have type text, found type char(64).

I went into MySQL with phpmyadmin, looked at the mysql database, proc table and edited the comment field to be text instead of CHAR(64).  Once I did that MySql Workbench reverse engineered everything fine.