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