MySQL Bugs: #45533: workbench not able to synchronize schemas with different names

Bug #45533	workbench not able to synchronize schemas with different names
Submitted:	16 Jun 2009 14:47	Modified:	12 Jun 2013 3:59
Reporter:	Heribert Steuer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	schema, synchronize

Description:
hi,

it seems that mysql workbench is not able to synchronize schemas that have
different names.

i also tried the following:

wanted to compare schema B on database 1 to schema A on database 2.

reverse engineeded B on database 1.
renamed the schema in the model to A 
synchronized to schema A on database 2.

it is important to do that as schemas do not always have the same name. think about development and testing schemas that you want to compare agains production schema on the clients database server.

How to repeat:
simply try to compare schemas with different names

This is a reasonable feature request.

thanks for your reply. any known workarounds for this?

Any work arounds for this or possible implementation dates for this? It's becoming an essential feature for projects. Thanks.

This is a problem for me too...

Must said that when changing the workbench schema name to the right database name it's still not synchronizable when they even seem to have the right name...

Another thing is that my database / schema have underscores '_' in their name, that's also maybe a thing what's causing trouble. Because if you have the name 'project_en_dev' it's sometimes interpreted as 'project_endev' or 'projecten_dev' or even other exotic possibilities within workbench when synchronizing.

Is there a date planned for this feature / bug?

The spaces in schema name should not be replaced by underscore '_' .

Underscore in schema name should be supported.

It seems that you cannot synchronize if the caps for the schema is different between model and database. If you forward-engineer your schema in the DB will have an all lowercase name even if you have caps in your model. But when you synchronize the caps have to match or synchronization doesn't work.

So a schema with caps breaks synchronization if you have forward-engineered to create the db.

Another bump for this necessary feature. There have to be a lot of other developers who need this and don't have time to file a bug report / feature request. Regardless of the technicalities of the matter, this *feels* like a bug to me. I'm using 5.2.21.

The synchronzize funktionality is a great feature of Workbench but it does not quit cut it if one can not synchronize schemas with different names. It would be very useful if it can be done.

The synchronzize funktionality is a great feature of Workbench but it does not quit cut it if one can not synchronize schemas with different names. It would be very useful if it can be done.

Another request for this feature please. Would be nice to have alternative names for a schema, as we do the following for database names...

Development: "example" (Local Server)
Testing: "example_test" (Live Server)
Production: "example" (Live Server)

Same to me, I need testing, development and production diff names. But event with the same name (d_arrendamas) doesn't work.

d_arrendamas => N/A
N/A => d_arrendamas

I'm not using caps:

SHOW CREATE DATABASE d_arrendamas;

d_arrendamas	CREATE DATABASE `d_arrendamas` /*!40100 DEFAULT CH...

An even more valuable version of this feature would include a simple batch mode for Forward Engineer and Synchronize Model. It would be ideal to select multiple configurations, each with its own network connection and schema name, and have the operation performed on each of them. It would be a big improvement even if one had to specify all the parameters manually.

One can currently copy the generated SQL and paste it into an editor, create a version for each database, and run each script, but automating all that in Workbench would streamline the process and eliminate many potential errors.

this is a must fix. Its been going on for more than 1,5 year, and nothing happens. I belive it would be better to fix this, than adding new features all the time. Best, thanks for good work.

There is a dirty workaround for anyone who really needs it:
Use mysql-proxy to alter the Database name on the fly. Tested, works, but dont forget to make backups before!!

For help with the proxy modifying script you may ask me: mysqlproxy__nils@huegelmann.info

I second the request. I have real trouble getting schemas to sync with my production and my testing database, mostly resulting in me manually altering the testing database to the schema will fit again.

EASIEST SOLUTION I'VE DISCOVERED

1. Don't touch original schema yet
2. Reverse engineer second schema(new tab will appear)
3. Just copy any tables from first tab to another
4. Now synchronize second tab

That works for me and I can synchronize both databases. Only hassle is copying wchich is almost effortless.

why cant we just change the scheme name to sync to a different scheme??? The sync shows:
mydb -> N/A
N/a -> mydb

This copy-paste procedure turns to be annoying after a couple times you try. Give us a solution!!!

@pablo aguirre
"This copy-paste procedure turns to be annoying after a couple times you try. Give us a
solution!!!"

Maybe you're doing it wrong. If you try to select tables one by one it's annoying, but when you select them with shift button, and after that "Edit-> Copy X Selected Objects" it's not that bad.

Anyway, Why synchronizing should be possible with different schema name?
Because hosting companies often makes prefixes to your databases names, and it's not possible to keep the same DB name on every server.

> Anyway, Why synchronizing should be possible with different schema name?
> Because hosting companies often makes prefixes to your databases names,
> and it's not possible to keep the same DB name on every server.

As already mentioned, it's a sensible feature request anyway.

File comparison tools are simple to use and work pretty well. I see no reason to reuse their abstractions in a DB compare tool, you just need to replace "line" with "column", "file" with "table", "subdirectory" with "database" and "directory" with "server". If I'm comparing two complete servers, I'd expect database names to match. If I'm comparing two databases (the most usual case?) I only care about their contents.

Please fix this problem soon.   Thanks.

hehe its more than a year old. I guess for workbench team this isnt`t a bug :SS Its a shame, without this bug it would be perfect tool.

This is super frustrating!

Is there an alternative to solving this problem – apart from manually updating all the tables myself?  I’ve tried the suggested solution by “porno porno”  (reverse engineer schema -> copy / paste) but copy and paste to the newly reversed schema tab does not work.  Although the paste tables do show up on the new tab, but the table itself sits under the original source.   Impossible!

Definitely a bug because I’ve managed to get it to work once before using below method!!!  But the 2nd -> * time was not so lucky.

To repeat:
1. Dump current DB (name=x1) to sql file.
2. Change DB name inside the sql file (on - create schema / use ) to x2
3. Import from the saved sql file.
4. Rename the updated schema model from y1 to x2
5. Sync model.

==> RESULT

x2 => N/A
N/A => x2

(I’ve removed cap, removed underscore, nothing works!)

This is a must fix feature…

Maybe we should send this link to some support email? Maybe anyone has something? Can some people from workbench/mysql/oracle replay if this feature/bug is being looked at, or what is the status of this?

Best,

Definately a nasty bug here .. please fix!

I think it's not caused by underscore since this works. Unless you start renaming sync works. Besides in the "model tab > right click > edit schema" workbench itself *recommends* using underscores.

At first I renamed and synced with another schema on another connection and it worked. When I renamed it back the trouble started. Unfortunately I had saved my model in between and there is no way to go back. Renaming a schema and renaming it back does something behind the scenes to wreck your mwb.

This does not get any developers love for some reason so In the meantime:
-avoid renaming schema. If you ever *have to* rename your schema however:
-First save your workbench model to a new mwb file
-then rename and do your sync/forward engineer or what you need
-Never rename the schema in the original mwb

I wish I had backed up my original mwb. Now I can only generate a new one by reverse engineering. I lose all my EER Diagram layout of 50 tables and have to start over with that!

I have found out something that could help tracking down the cause. In my test synchronize broke when renaming to a schema like:

myuser_db

But worked when using something different like:

myuser_system

I'd guess it might be the length of the word or the word itself, used after the underscore (reserved word?). This should be reproducable.

Hi, same problem here:
duplicate a db with a new name (localhost, no Caps or underscore); used same .mwb file just re-opened and sobstited db name (scheme name) with the new one.

then Synchronize Model with database and:

NewDB ---> N/A
N/A  ---> NewDB

I guess it's a complicate issue for the Workbench team but it's frustrating!
Appreciate your hard work guyz

After changing schema name and trying to synchronise (as described by Heribert Steuer), I hunted for where the old schema name might still remain. I only found one place.
This is in the xml file that you can access by changing the mwb file extension to zip then extracting the contents (I made a copy of the mwb file first to be on the safe side). There you will find an xml file. Open this with a text editor and search for the old schema name. 
It occurs in an element with key="oldName".
I discovered that if I changed this to the new name, saved, put the xml file back into the zip, and renamed the zip back to mwb, the synchronisation will then work as required.
Clearly, despite all appearances in the GUI, the synch is using the name from the "oldName" key.

I tryed to unzip, modify the document.mwb.xml file with oldName=newName, zip back document.mwb.xml+@db folder and rename the zip to mwb as per Bruce Thomas procedure but got the following msg:

"can't open XML file C:\Documents ad setting...."

am I doing s'ting wrong ?

Hello NiceMotion
I do not think that there is anything you can do wrong so far as opening the file is concerned.
I just tried it again - no problem.
I'm working on a Windows XP machine.
I do not have Workbench open when I'm doing this.
I have Administrator rights.

I can open the Xml file with Notepad, but its difficult to see the structure in that.
Notepad++ makes it easy to see.

The db file is an sqlite file. I can open also open that in sqlite - but there is nothing useful in it.

Hi Thomas, tks x yr ppt reply.
Same OS and condition here - i'm using UltraEdit and I've found the old DbName.sql in the xml file on line 6900:
key="MySQLDbModule:input_filename"
and on line 6902:
key="MySQLDbModule:output_filename"
Both with a path pointing to desktop where my db have never been; i edited also these 2 lines but no success.

Hi again Nicemotion

The key oldName occurs many times in the xml file.
Search for your MySql schema name (the name you want to change). It only occurs once (or twice if you have not changed it) 
In my case (where I wanted to synchronise my dev_master database to my test_master database) this section of XML looks like this:
              <value type="string" key="createDate">2011-02-23 13:18</value>
              <value _ptr_="0E8DE7A0" type="dict" key="customData"/>
              <value type="string" key="lastChangeDate">2011-03-01 13:26</value>
              <value type="int" key="modelOnly">0</value>
              <value type="string" key="name">test_master</value>
              <value type="string" key="temp_sql"></value>
              <value type="string" key="comment"></value>
              <value type="string" key="oldName">dev_master</value>
I had to change the dev_master to test_master to get it to work.

Same issue, I'd like to be able to sync up with dev/live databases.

Please please please fix this, guys. It's a simple yet fundamental thing to have.

Thanks!

Simpler test case:

When you go through Synchronization and you have:

mytable => n/a
n/a => mytable

Click ahead to the "Review DB Changes" part, delete all of the SQL statements so that nothing is executed on the server. When you next run through the synchronization process you should see:

mytable => mytable

as expected; it's not an elegant solution as if you skip the part where you delete the sql statement you could end up wiping your live database! No fun for anyone.

Bug #61157 is a duplicate

This happens because at some point, either the model or the DB was renamed after
synchronization. That causes WB to think the counterpart as having a different name.

A workaround is to open the Scripting Shell, inspect the name of the schema and make
sure that the oldName field contains the same name as what you're trying to synchronize
against. 
Commands for that are:

# print current oldName value
print grt.root.wb.doc.physicalModels[0].catalog.schemata[0].oldName
# change the oldName field value
grt.root.wb.doc.physicalModels[0].catalog.schemata[0].oldName = "newname"

In a closer inspection, this bug contains 2 different requests.
The original request is to synchronize schemas with different names, regardless of their past.
The 2nd is a bug about synchronizing schemas that were once the same, but were renamed independently at some point. I'm moving the 2nd type to bug #61157

Any news on a working solution to this. 
Just want to be able to compare two Schemas on the same database.
<system>_DEV, <system>_TEST and then <system>_LIVE. 
To produce a patch!

On the Model tab of your model you have a big tab with schema name in Physical Schemata section (your_schema_name MySQL Schema). If you click on this tab with right mouse button, and select Edit schema menu item, you will be able to change schema name. Then, the model synchronization will work with given schema. You can also add another schemas to model here, and I believe that then you can synchronize your db between these schemas.

This workaround works and is what I use (with great care):
[5 Apr 2011 15:36] David King

The latest comment (by Lukasz Frankowski) misses the point a little. This is the way it *should* indeed work .. were it not for this bug. You will run into this right after performing that rename and run synchronization.

Please post tested solutions only or provide new information. This bug thread tends to be confusing as it is.

I really think the easiest way to approach this is in a manner similar to the way that SQLYog does...Pick source db, pick destination db. Basically they migrate the contents of the db but don't really necessarily care about the db itself, which in all reality is the simplest way to do it. If you're migrating tables, you should have the db ready to receive the them.

Added bug #56128 as dup

ah same problem! can't sync tables from different schemas in 5.2.38 (ubuntu)

I have dev and live site with different schemas and it's murder manually updating over 60 tables :(

Love the software guys! =D

This is a more than needed feature. 
I have to use other software to handle this daily operation.

It's very strange from de developpement team not to take care about this after 3 years ....

I would really like to see this implemented, it makes Workbench very hard to use for a lot of my cases.

In the newest version, the name-change workaround seems to work fairly well.

1. reverse engineer the source database
2. head over the "mysql model" tab, right click the imported database, hit edit
3. change the name to that of your destination database
4. select database -> synchronize model with database
5. when you get to the "select schema" step, click the name of your database in the list, and hit the "ignore name change" button.

From here, you should be able to proceed normally.

+1 for the feature request of being able to select the destination database when doing synchronization, instead of it matching the name of the source schema.

It's really annoying during development across different environments (development, staging, etc) to have to rename the schema model to reflect the name of the database you want to push changes to.

@Samuel Dufel: I am not able to find the feature you mentioned. I am using 5.2.44 CE.

@Samuel Dufel: Sorry looks like I missed the important step: Step 2: to rename to that of the destination database.

Will be better if we do not need to rename the destination destination and just select the destination database at step 5.

Following up on Nils Hügelmann's suggestion that mysql-proxy offered a clumsy workaround I have implemented a Lua script for mysql-proxy which substitutes the schema name passed in as the default_db from the workbench connection profile for the one received from the model schema.

I have posted it as https://github.com/obrun/map-schema

You need to have/install mysql-proxy, preferably on the same machine as the mysql server and set an environment variable in the start script with the workbench model's schema name. You then connect the workbench connection profile to port 4040 on the machine and synchronize.

I'm pretty sure that earlier versions of MySQL Workbench actually supported the ability to synchronize across different database names.

As stated before, this must be a very common scenario, that one would have multiple copies of the same database (with different names) on one server - I can't imagine doing any development without this pattern.  Also, anyone using shared hosts will be subject to this as well, since you are likely developing and testing on the same db server.

It has been 3 years, 7 months and 21 days since this bug has been reported. Today my hands are tied again trying to update a database with a different name.

The method described in the comment [15 Aug 2012 20:44] Samuel Dufel has worked for us for several months. However, since upgrading to the most recent version of Workbench, we find that the EER must have some historical reference to other schemas that cause bad things to happen. Like, for instance the other night when I synchronized my development database and workbench also did DROP SCHEMA on my production database. I'm fairly sure that I had chosen to "ignore" this action but I'm not going to try again to find out. Ever again.

So to recap, syncing our development database Workbench dropped our production database. Thankfully we had a 20 minute old backup at the time. But holy hell. We continue to have this problem even with a database built from scratch. So be careful.

The fact that this issue has not been resolved for almost 4 years is quite embarrassing. This "bug" (or however you call it) is very annoying and I have just spent hours trying to get around it. 

With most of PaaS offerings it is common these days that you may have no control over how your production schema is called.

Plus it seems that even when I rename my model's name the sync does not work - probably if schema name in target database is longer (mine is ove 24 characters).

Fixed as of the upcoming MySQL Workbench 6.0.2 release, and here's the changelog entry:

It was not possible to force synchronization of a schema with another
schema that had a different name. The new "Override Target" option allows
this, and is available during the "Select Schemata" stage of the
synchronization wizard.

Thank you for the bug report.

guys, you know this annoying antique bug! still remains on 2017?
i'm using workbench 6.2. amazing.

guys, you know this annoying antique bug! still remains on 2017?
i'm using workbench 6.2. amazing.

two hours to not to synchronize a model with exactly equivalent model.
Found a solution, above someone already noticed this, that's run synchronization with ignoring al the changes.
After that, workbench treats same model as equivalent one.