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: | |
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
[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.