MySQL Bugs: #46380: Workbench crashing while reverse engineering django's default database

Bug #46380	Workbench crashing while reverse engineering django's default database
Submitted:	25 Jul 2009 1:11	Modified:	30 Jul 2009 16:59
Reporter:	Amir Habibi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.1.16OSS	OS:	Windows (XP SP 3.0)
Assigned to:		CPU Architecture:	Any
Tags:	crash, Django, reverse engineer, Worbench

Description:
Workbench is crashing on windows XP while reverse engineering Django's default database. 

I use the following:

MySQL on Fedora 11
Python 2.6 on windows XP
Python-Mysql driver
Django 1.1

How to repeat:
Use Django to create the default database.
Use Mysql Workbench to reverse engineer the database

(If you don't have time to set things up, email me to send you the .sql file. The result should be the same.

Thank you for the bug report. Could you please attach here the *.sql file. Thanks in advance.

BEGIN;
CREATE TABLE `auth_permission` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(50) NOT NULL,
    `content_type_id` integer NOT NULL,
    `codename` varchar(100) NOT NULL,
    UNIQUE (`content_type_id`, `codename`)
)
;
ALTER TABLE `auth_permission` ADD CONSTRAINT `content_type_id_refs_id_728de91f` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
CREATE TABLE `auth_group` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(80) NOT NULL UNIQUE
)
;
CREATE TABLE `auth_user` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `username` varchar(30) NOT NULL UNIQUE,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(30) NOT NULL,
    `email` varchar(75) NOT NULL,
    `password` varchar(128) NOT NULL,
    `is_staff` bool NOT NULL,
    `is_active` bool NOT NULL,
    `is_superuser` bool NOT NULL,
    `last_login` datetime NOT NULL,
    `date_joined` datetime NOT NULL
)
;
CREATE TABLE `auth_message` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `user_id` integer NOT NULL,
    `message` longtext NOT NULL
)
;
ALTER TABLE `auth_message` ADD CONSTRAINT `user_id_refs_id_650f49a6` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
CREATE TABLE `auth_group_permissions` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `group_id` integer NOT NULL,
    `permission_id` integer NOT NULL,
    UNIQUE (`group_id`, `permission_id`)
)
;
ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `group_id_refs_id_3cea63fe` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `permission_id_refs_id_5886d21f` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
CREATE TABLE `auth_user_groups` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `user_id` integer NOT NULL,
    `group_id` integer NOT NULL,
    UNIQUE (`user_id`, `group_id`)
)
;
ALTER TABLE `auth_user_groups` ADD CONSTRAINT `user_id_refs_id_7ceef80f` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `auth_user_groups` ADD CONSTRAINT `group_id_refs_id_f116770` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
CREATE TABLE `auth_user_user_permissions` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `user_id` integer NOT NULL,
    `permission_id` integer NOT NULL,
    UNIQUE (`user_id`, `permission_id`)
)
;
ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `user_id_refs_id_dfbab7d` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `permission_id_refs_id_67e79cb` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
CREATE INDEX `auth_permission_content_type_id` ON `auth_permission` (`content_type_id`);
CREATE INDEX `auth_message_user_id` ON `auth_message` (`user_id`);
CREATE TABLE `django_content_type` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(100) NOT NULL,
    `app_label` varchar(100) NOT NULL,
    `model` varchar(100) NOT NULL,
    UNIQUE (`app_label`, `model`)
)
;
CREATE TABLE `django_session` (
    `session_key` varchar(40) NOT NULL PRIMARY KEY,
    `session_data` longtext NOT NULL,
    `expire_date` datetime NOT NULL
)
;
CREATE TABLE `django_site` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `domain` varchar(100) NOT NULL,
    `name` varchar(50) NOT NULL
)
;
CREATE TABLE `django_admin_log` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `action_time` datetime NOT NULL,
    `user_id` integer NOT NULL,
    `content_type_id` integer,
    `object_id` longtext,
    `object_repr` varchar(200) NOT NULL,
    `action_flag` smallint UNSIGNED NOT NULL,
    `change_message` longtext NOT NULL
)
;
ALTER TABLE `django_admin_log` ADD CONSTRAINT `user_id_refs_id_c8665aa` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `django_admin_log` ADD CONSTRAINT `content_type_id_refs_id_288599e6` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
CREATE INDEX `django_admin_log_user_id` ON `django_admin_log` (`user_id`);
CREATE INDEX `django_admin_log_content_type_id` ON `django_admin_log` (`content_type_id`);
COMMIT;

The database schema to generate Django's default database

Attachment: django-default.sql (application/octet-stream, text), 4.21 KiB.

Please, send the output of Help > System Info. What version of MySQL server do you use?

I can not repeat this on Mac with your .sql while reverse engineering from recent 5.1.38, hence the requests.

MySQL Server: running on Feodra Linux (11 / Leonidas)

Server version: 5.1.35 Source distribution

---------------------------------------------------------------

MySQL Workbench: running on Windows XP version 2002 SP3

MySQL Workbench SE for Windows version 5.1.16
Cairo Version: 1.8.6
Rendering Mode: GDI Rendering
OpenGL Driver Version: Not Detected
OS: Windows XP
CPU: 2x Intel(R) Pentium(R) 4 CPU 2.40GHz, 3.0 GB RAM
Video adapter info:
Adapter type: NVIDIA GeForce 8400 GS   
Chip Type: GeForce 8400 GS
BIOS String: Version 62.98.20.00.51
Video Memory: 524288 KB

I've created tables as described in your comment dated

[25 Jul 22:30] Amir Habibi

in a separate database, then reversed engineered it without any problems with 5.1.16 on XP. No crash. Can you, please, try to use WB on some other Windows machine? The only difference I see with your case is 64-bit XP SP2 in my case vs. SP3 in yours + different NVIDIA adapter with smaller memory in my case.

Hi Valeriy,

Thanks for your attention to this issue. Apparently the cause of this problem was mysql server inconsistency. Even in the light of this finding I can't explain why workbench should have crashed. 

The issue was that on my recent update of Fedora packages, mysql-server has been updated but my guess is that the to changes in mysql and information_schema tables had not been completed properly and without proper message that I could have noticed. Surprisingly enough the database worked fine with my application after the update and it was after your request to try another workstation that I got bugged down with all kinds of errors b/c I wanted to grant access to the new workstation. 

Long story short after hours of reading posts on Internet and different scripts that are shipped with Fedora package, I had no choice but rebuilding all databases from scratch to get things to work again. I can conclude that Fedora, MySQL server and MySQL Workbench one way or the other are not handling the issue properly. 

Fedora didn't upgrade the server correctly. MySQL Server started and worked fine on an inconsistent schema without reporting the issue and MySQL workbench crashed on a buggy database. I also noticed that mysql_fix_privilege_tables and it's corresponding .sql script are not covering errors and duplicate columns, etc properly.

I have kept a copy of the inconsistent database in the case that you need it. 

Regards

Many thanks for your feedback.

So, this seems more related to mysql server then to workbench am I right?

Which server version did you use?

Hi Susanne,

You're right, the cause of the crash was perhaps inconsistent database metadata that workbench uses for reverse engineering but it's interesting that MySQL server worked fine at application level and I'd run all kinds of DML operation on the server during this period. It was only grant statement that failed after Valeriy wanted me to test from a different workstation.

Apparently the server is upgraded to:
Server version: 5.1.35 Source distribution

I don't keep a log of package versions on this server as it's my test server but I might be able to fetch the information from Fedora repositories or the package maintainers.

Regards

Hello Amir Habibi,

many thanks for your fast feedback.

I think I know what happened here.

For MySQL server 5.1 we fixed some collation bugs. Unfortunately this means, that when you upgrade your databases to 5.1 that some indexes are broken and need to be repaired manually.

Of course the database is running with broken indexes. Selects just will get slower and on inserts/updates the new values won't get inserted into the index tables correct.

Broken indexes seem to confuse Workbench too much.

We fixed utf8_general_ci which means all your utf8 columns with indexes are broken.

To repair this either drop and re-create the indexes or use ALTER TABLE ... ENGINE=<your_engine>

ALTER TABLE will copy your table so that you have a fresh and proper table with repaired indexes after that.

Well, 

I think the mystery is unraveled now. I rebuilt the database completely b/c my upgraded server was a test server, however, I'm afraid that many production servers are out there that soon or late will receive the new packages through their operating systems. It would be nice if your recommendation can somehow be propagated to the community to help them know of the potential issue in advance.

Working with MySQL team on this issue was a great experience. I'm surprised that your team took it so seriously and came up with a conclusion in such a short period of time.

Please let me know if I can be of further assistance.

Regards

Hello Amir,

I couldn't reproduce crashing here.

Anyway, that mysql_uprade won't repair defect indexes by automatism is a bug and we are already on the way to get it fixed.

At the moment you won't have another chance as repair your database first.

Hello Susanne,

I also think that it would be tough to reproduce the error. If I find a spare computer around my office, I will try it though. I need to install Feodra 10, create mysql database and then upgrade it to Fedora 11 using pre-upgrade package. 

I don't know if it would be the case for other distributions but at least this is the scenario that happened on my server and my worry is that many existing Fedora 10 production servers will get upgraded to Fedora 11.

A quick remedy, I think, is to reinforce the schema upgrade scripts (mysql_fix_extensions, mysql_fix_privilege_tables) so that they don't fail if for example a duplicate of a column or an index (for whatever reason) exists on the table. That would help administrators to re-run the scripts again until they get the schema as expected to be in the target MySQL server. 

Regards
Amir