MySQL Forums - Workbench - Schema Compare, Diff, Synch

Stored procedure code generation patch (no replies)

Rogelio Jacinto — Mon, 23 Nov 2009 17:09:13 +0000

I'm using Workbench OSS 5.2.8 Beta Rev. 4572, and I've experienced bug http://bugs.mysql.com/bug.php?id=39929.
So I downloaded the source and came up with a small patch to remove the line with the database name after the 'USE {database}//' line in the generated code for dropping and creating stored routines.
I've attached the patch file to the bug. Hopefully it will be included in your next build.

Sync decides to drop all and start fresh (1 reply)

Aaron Wallis — Sat, 21 Nov 2009 21:17:02 +0000

Hello there,

I've been using MySQL Workbench to model my database structure which I originally built using other tools.
I've created the model in MW and generated a diff report to ensure the migration is sound (i.e. do a dry-run) and I was pretty shocked to see that it decided to drop all my tables and recreate them all!

A similar thing happens when I run a sync, it doesn't think the schema in the model and the actual DB schema are the same. (see in screenshot: http://emberapp.com/d2kagw/images/mysqlworkbench)

Any ideas how to make MySQL WB match up the schemas?

I'm running Workbench 5.2.8 B Beta on OS X 10.6.2.

Cheers
Aaron.

Linux FC11 - mysql workbench crashes when trying to reference PK from FK tab (1 reply)

Alan Ahmetspahic — Fri, 06 Nov 2009 17:39:35 +0000

So you go to the Table, select Foreign Key tab, enter the Foreign key name and try to reference table with the primary key, it crashes.

Anyone else experiencing this? Possible workaround?

When using synchronization, sql statement is lost, causing the synchronization error (1 reply)

john kebo — Thu, 05 Nov 2009 05:19:24 +0000

When using synchronization, sql statement is lost, causing the synchronization error

The vision is : mysql-6.0.0-alpha-win32

Here is the error message：

[ERROR] Slave: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wh' at line 1' on query. Default database: 'supermarket'. Query: 'update goods_stores set count='10.0',cost='0.0' wh', Error_code: 1064

'where' has been cut into 'wh' , so the Error happened.

Please answer, thank you

Database Sync not working? (1 reply)

Joshua Ostrom — Mon, 02 Nov 2009 07:05:38 +0000

I just filed Bug #48444, does anyone have any insight on this?

Model<->DB Sync was the catalyst for purchasing SE.

Description:
The database synchronization does not work.

The sync will populate an empty database, but will not detect any
changes made to the model thereafter.

The sync also has problems with views and stored procedures, the SQL
generated in invalid MySQL SQL.

If you run a catalog diff, you do see changes so that logic is working.

How to repeat:
Create an empty database. Sync the model down to the empty database.
Add a new column to a table in the databaes.

Suggested fix:
No known work around.

The logic for the catalog diff feature *does* detect the changes so
maybe a code rewrite is in order leveraging that logic?

How can I see the differences between 2 databases with different names? (no replies)

plamen vasilev — Fri, 16 Oct 2009 16:02:58 +0000

On my MySQL server i have 3 databases
1. release_1
2. release_2
3. trunk

How can i see the differences between 2 of these databases.

When I try to reverse engineer "trunk" and and Synchronize Model with "release_2" the mysql Workbench wants to sync:
trunc --> n/a
n/a --> release_2

how can I sync them ????

I've tried to rename trunk to release_2 and I get the following :

release_2 --> n/a
n/a --> release_2

please help :)

What is the best schema (-1 replies)

Daniel Painchaud — Fri, 16 Oct 2009 12:40:25 +0000

-- moved topic --

Database synchronization NOT working - frustrating ! (2 replies)

Shantanu Upadhyaya — Fri, 09 Oct 2009 18:51:53 +0000

I don't know if this problem has been posted before and I apologize it if I haven't read the documentation enough, but here's my problems.

1. I'm using MWB 5.1.18 on Windows.
2. MySQL 5.1
3. I have just 5 tables in my model (.mwb file)

Here are the steps :
4. Forward engineer the model to my schema - works fine.
5. Modify datatype of a column / add a column / add constraints.
6. Save the file
7 Menu : Database --> Synchronize Model --> follow till the end - shows success message.
8. I check my table using Toad Freeware for MySQL - NO CHANGE !

MWB does not synchronizing with DB. I've tried this several time. It's as if it's picking up the old file and applying it.

9. Drop all tables in the schema - via Toad.
10. In MWB model - I add a column to TableX. Save file.
11. Do step 7 again - but only synchronize TableX. Rest is ignored.
12. Goto Toad - TableX is present with new column.
13. Goto MWB - change TableX.someColumn from VARCHAR(45) to VARCHAR(100) - Save.
14. Do step 7 - I see the SQL statements do not contain any ALTER statement. It has :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

15. Click Execute. Shows 'Synchronization Completed Successfully'
16 Goto Toad. TableX.someColumn IS STILL VARCHAR(45) !! Why ??

Am I doing something wrong ??

I will try this on Snow Leopard.

Workbench drops entire database any help please (1 reply)

dan carter — Tue, 06 Oct 2009 18:42:52 +0000

Hi There,

Extremely frustrated here 30-40's of hours of lost work trying out workbench for the first time!

Opened workbench from the menu clicked database->synchronize model selected my database once the wizard was finished the entire database was lost!!!

Its gone... Any suggestions would be greatly appreciated. Have no backup of db.

Regards,

Daniel

MySQL Workbench: Free and easy schema diff and synch (no replies)

Edwin DeSouza — Wed, 30 Sep 2009 16:37:07 +0000

MySQL Workbench: Free and easy schema diff and schema synch
http://www.pythian.com/news/4162/free-and-easy-schema-diff-and-patch

problem with MySQL cross schema FKey constraints (no replies)

Thirumal Reddy — Sun, 05 Jul 2009 17:46:05 +0000

Is there any way to maintain cross module fk relations in mysql

Thanks&Regards
tmadugula

Error sync with store proc (2 replies)

Pavel Dobryakov — Tue, 30 Jun 2009 16:22:11 +0000

When i sync model and there are only changes in store proc, generated scripted dose not have USE command.

Alter script (3 replies)

Al Lombardo — Tue, 23 Jun 2009 18:43:02 +0000

Every time I perform an alter script, it always generates a change to one of my tables (drops an index and recreates it as a unique index) - even though there is no change (it is defined as a unique index in my create script I compare against). Every time I run an alter I need to strip out this change. Any ideas as to why this keeps coming up as a change? Also, I have several other unique indexes defined that do not produce this problem. I am using OSS ver 5.0.22 rev 3118.

problem with Export | Alter SQL script (no replies)

Al Lombardo — Wed, 03 Jun 2009 15:19:51 +0000

After I Export|SQL Create my schema, I shouldn't have any changes if I immediately do an Alter script. I, however, always have one change which always come up. The SQL alter drops an index and re-creates it as a unique index on a table called status. I've checked the SQL created by the SQL create and it is defined as a unique index. Every time I do an alter script, I need to remove this entry from the script. Any ideas as to why this table always generates a change?

Synchronize & Schema Names (3 replies)

Edward Savage — Wed, 10 Jun 2009 18:09:05 +0000

I am creating a master schema in MySQL Workbench that would like to be able to use when setting up a site for a new client. The hosting service I'm using doesn't give full control over the schema names. I was wondering if there's a way to do comparisons/synchronizations and either ignore or specify the source and target schemata. Thanks!

N/A model problem (1 reply)

Cosmin Cimpoi — Fri, 08 May 2009 15:53:55 +0000

Hi.
I have this ghost N/A model in my file that is linked to the lowercase name of my database. I also have a valid model linked to the real database name. I think that I accidentally created the ghost model when I first tried out the Workbench.
But now when I do a model sync this N/A model is still there and is linked to the source with the lowercase name of my real db. And even if I x the arrow between them it still creates a
DROP SCHEMA IF EXISTS `lowercase_db_name`;
line in the sync script. I can delete this line every time to avoid the problem. But if I do not delete it then the DROP shema actually deletes my real database.
So any idea how I could get rid of this ghost N/A model that is stuc around. Or move my real model to a new file?

Uncaught Exception during synchronization (1 reply)

Brice Targat — Fri, 17 Apr 2009 14:57:27 +0000

Hi all,

I'm quite new with MySQL Workbench, and already experience some troubles.
While synchronizing my shemata with my MySQL database (for the first time in the forward-side : shemata to db), I get an error :

The Database Synchronization form shows "Executing... Synchronizing selected objects", then an error pop with text "Unknown Exception caught in c:\document and settings\mysqldev\my documents\visual studio 2005\projects\workbench\backend\windows\wb.wr\src\Wb.h at line 1010"

if this could help ...

diff and stored procedures (1 reply)

trash mail — Thu, 09 Apr 2009 07:06:35 +0000

I have been testing the SE version and cannot get the schema difference to function correctly with stored procedures. it seems that it only picks up the presence/absence of procedures and does not compare the body.

As a simple illustrative example I performed a sync on a local database, removed the body of one procedure completely (it was still present it just did nothing). Then i performed a synchronize and the workbench reported no difference. I also tried a diff report and exporting a sql alter script.

is this just a bug? has that feature not been implemented yet?

Synch via command-line? (3 replies)

Jake Chung — Wed, 01 Apr 2009 13:59:25 +0000

Thanks Edwin for the response on the Newbie forum =).

I am using Workbench SE, but so far I have only been able to determine that I can use the Schema Synch feature via the GUI. Is there a way to use the Schema Synch feature via a command-line interface with Workbench? Or is there any other way I would be able to use the Schema Synch feature in Workbench with a batch file or VB script or any other script?

I'm looking for a synch feature that could be used in product upgrades where a command-line utility would be used to compare the latest schema SQL file with the database that is local to the machine that the upgrade is run on. It would then execute the appropriate ALTER TABLE commands to synch up the database with the latest schema SQL file. This upgrade be done via an installer, and so I would like to avoid requiring a user to interact with the GUI to upgrade his/her database.

Thanks for the help!
Jake

Applying difference to DB schema (4 replies)

Leon de Beer — Fri, 24 Apr 2009 09:11:12 +0000

Hi,

I have 2 database DEV and LIVE. We make all our schema changes to DEV and when do a release cycle we need to apply these change to LIVE. The only way I've figured out how to do this is to:

Dump the DEV schema
Dump the LIVE data
Drop the LIVE database
Create the LIVE database from the DEV schema
Restore the LIVE data

Obviously that's FAR from ideal as it's very dangerous and it breaks replication - every time! Ideally what I would like to do is:

exec [MAGICSCRIPT] which creates the relevant ALTER TABLE statements
Apply ALTER TABLE statement to LIVE database.

I did some research on this and I found some third party tools but they seem to cumbersome for what I'm trying to do. Is there a simpler way or is it time to dust of the bash scripting books and start coding?

Thanks

:Leon

Foreign key lines disappeared (1 reply)

Ziphyre Marco — Tue, 10 Feb 2009 08:21:41 +0000

Hello,

With Workbench Standard Edition 5.0.29, after synching with the db, all foreign key lines between tables have been disappeared. But the foreign key information is still there on the tables.

I've searched bug db and google but couldn't find a similar problem.
I'll be glad if anyone gives me an idea...

Thanks

Synch errors due to not dropping foreign keys? (1 reply)

Marc Ford — Fri, 30 Jan 2009 10:07:42 +0000

I consistently have synch problems after altering fields or adding new tables or fields and their relationships. If I delete the foreign keys surrounding the "problem" table in the target database, the synch process will complete successfully. It appears that Workbench needs to learn when to DROP foreign keys so that changes can be made, and then re-insert the foreign keys at the end of the script after the changes have been made.

When I synch 2 actual databases using SQLyog, their script shows this action. FK's dropped at the beginning, and then you seem them being reinserted at the end.

I really love the Workbench ERD tool and don't want to give it up, but synching will become unmanageable soon if I have to constantly delete foreign keys so the synch can take place.

Please let me know if I am in error...

Synchronizing the diagram from the Database doesn't seem to work (1 reply)

Dan Grube — Thu, 15 Jan 2009 16:39:11 +0000

Hi All,
I've built a Diagram from a set of tables in a database, then those tables changed from another source and I've attempted (on a test database) to use Sychronize to update my Diagram and it always seems to update the tables in the database from my diagram rather than updating my Diagram from the tables.

Can someone tell me how to get this to work?

Help would be greatly appreciated.

Thanks

Dan

Workbench runs out of memory when creating a model (1 reply)

Bill Ennis — Mon, 27 Oct 2008 18:02:51 +0000

Hi,

Just started using Workbench and was hoping to use it to perform schema differencing. Version 5.0.26 SE

I was able to reverse engineer the database. However, when I try to create a diagram it runs out of memory. The db has approx 170 tables so it's not that large. Has anyone seen this problem?

Also, when I try and perform a synchronization Workbench doesn't seem to align the model with the database. The diff tree shows:
! mydb -> !N/A

Underneatch !mydb -> !N/A I DO see the correct scham names.
What is the first level denote?

The beginning of the diff script syas create schema if not exists 'mydb'
Why so?

Where can I get more info on how to use this tool? The docs that I have seen are not detailed at all.

Thanks

note: mydb is NOT the name of the schema.

It looks as if Workbench isn't aligning the database with the model

Synchronizing database structure and/or data (1 reply)

Brice B — Tue, 21 Oct 2008 07:28:29 +0000

Hello,

I'm looking for a fully automated way to synchronize just the structure and just the data of two databases (preferably FOSS).

The script I've used previously works something like this:
mysqldump dev_db --no-data > dev_structure.sql
mysqldump live_db --insert-ignore --no-create-db > live_data.sql
mysql -Dlive_db < dev_structure.sql
mysql -Dlive_db < live_data.sql
(the actual query has more options but I think it's irrelevant to post here.)
When a column is removed from the dev database and this script is run, it fails when it tries to insert values into the removed column. INSERT IGNORE doesn't ignore this that error.

I've asked the #mysql IRC channel for help. I was asked to look at Maatkit (http://www.maatkit.org/). I haven't figured out a way to fix my problem with it yet.

While searching the forums I've come across MySQL Workbench, SQLyog, ModelRight, http://www.artfulsoftware.com/infotree/queries.php?&bw=1276#624
The first three are GUI tools, however I need a way to synchronize the databases in an automated way.

== Background ==
We're using a development, test and live environment. Each environment has its own version of the database.
In the development version we make changes to the database structure. When it works, the same changes are made to the test db and eventually to the live db.

While testing we also import the data from the live database to work with 'real' data.

This means the structure goes this way:
Dev => Test => Live

And the data goes the other way:
Live => Test => Dev

Does anybody have suggestions?

Thanks in advance.

Workbench SE does NOT handle cross-db foreign key references? (2 replies)

lorenzo lucioni — Mon, 06 Oct 2008 16:58:22 +0000

it is useful (for many reasons, for example back-up policy) to have different DataBases (Schema?) running under the same engine. They also could be designed to host tables related to different business categories.
MySql, of course, let me able to define cross-db (cross-schema) foreign-key references.

Does Workbench handle cross-db (cross-schema) FK reference?

thank you,
lorenzo

"Error: Invalid Object Name" during Forward Engineer SQL Alter Script (2 replies)

Malcolm Cook — Mon, 27 Oct 2008 18:17:56 +0000

When I choose

File > Export > Forward Engineer SQL Alter Script

and provide it a .sql file to compare with the current model

I get a window:

"Error: Invalid Object Name"

and the SQL diff tree does not appear.

running version 5.0.25 build 3427 for windows xp

any workarounds or suggestions how to figure out what object name is invalid are welcome....

thanks

Error 1292: Incorrect date value: '0000-00-00' stops synchronizing (1 reply)

Georg Menhard — Sun, 21 Sep 2008 22:55:46 +0000

Hi,

I really have problems synchronizing my databases. Quite often the synchronization breaks with the error:

Error 1292: Incorrect date value: '0000-00-00' for column 'date' at row 123

The only solution I know is to change all "0000-00-00" dates in the db.

Is there a way to force MySQL Workbench into ignoring this error and continue the synchronization?

Thanks for you help!!!

Georg

Connect to database on network Table 'information_schema.schemata' doesn't exist (1 reply)

servian frederic — Mon, 08 Sep 2008 09:49:49 +0000

when i want to connect the tool to the database who is in an computer of the network,
i have a error
Opening connection.
OK

Fetching schemata list.
Table 'information_schema.schemata' doesn't exist
Failed to load schemata.
i don't understand what's means this error,
when i do it in localhost, it's works correctly

How to synchronize database between local and remote server (1 reply)

Cheah Zhi Hao — Mon, 19 Jan 2009 21:57:52 +0000

Currently, I'm developing a system consists of two database (one in local, another one in the remote server - centralized database). I want to do some programming to enable synchronization between this two databases. I'm using .NET, any idea?

Thank you.