4

I've been looking all over for any description of an issue like this and I haven't found anything so far.

I am trying to update my MySQL database using MySQL Workbench (5.2.40 CE). I am using the Synchronize Model option just as I have always done before to update the database with the changes I've made to the model. Main thing to note here is that the issue I'm going to describe happens even without any changes being made to the model.

The problem is when it gets to the point where it shows "Model and Database Differences", the fields which all normally line up between Model and Source are totally off. I get everything doubled with the first set showing the Model names of my Schema and Tables with the Sources next to all of them showing N/A. Then below that it has all of the Source values filled in while the Model values all say N/A.

If I run the update as it is my database does get updated, but it also completely wipes out all the information on the database because it is dropping all the tables and re-entering them. Here is the picture of what the page looks like.

workbench image

I have no idea if this is some kind of setting or bug. But on my test database after totally getting wiped out and trying it again, the Differences page shows up normally with everything 1:1 like it should, and it updated just fine.

Any clues on this would be appreciated.

3

MySql Workbench is powerful, but not perfect.

Some time in similar case, I've done the following to resolve the problem:

  • create a blank project
  • require a model update FROM db source
  • then I'll reapply the mod.

Often this happen if you just change a 'comma' [a small thing] in the db without sync it with the model, and then you change another 'comma' in the model. MySql Workbench goes crazy often as db increases in complexity and table number, but I think it's a very good product.

  • Wow, I forgot about posting this. Thanks for the answer. That makes sense and I can see that with Workbench. It's a really great tool but it's not perfect. – fender357 Aug 7 '13 at 4:04
-2

If you are sure your db is identical to your model, you can update your model instead your db.

UPDATE

I had faced the same problem with MySQLWorkbench. In my case I'm using Workbench v8.014 and test and production db server at v8.0.12 and v5.6 respectively.

Below is the algorithm to fix the synchronization issues. After each step, if the automated coded still drop any table or column it shouldn't or miss-detect any difference I try with the next step.

At the "Model and Database Differences" wizard screen:

  1. Check and update the table mappings using the button "Table Mapping". This mainly fix the mayor problems related to drop and create tables.
  2. Check the mapped fields using the button "Column Mapping". This sometimes works, and the generated sql code change from a "DROP" to a "CHANGE" when a table's field is updated in the model.

At the "Preview Database Changes to be Applied" screen:

  1. Sometimes, the previous steps does not fix the problems. So I manually edit the generated sql to update the bd. You can figure out if you will write by hand the update sql code then is pointless use the Synchronization tool and read this answer, although if you edit the generated code to the right one inside this wizard screen, the model "magically" get correctly synchronized.

At the "Model and Database Differences" wizard screen:

  1. At this point, all change and drop commands over a table or a table's field should be fixed. However there is still some indexes and foreign keys synchronization issues. For this scenario, first check the db exactly have same indexes and foreign keys as model has (I normally do it for 2 or 3 tables) then run the tool and update model instead db. Do that selecting all changes in the screen and then pressing the button "Update Model". If everything was fine as you already checked, your model diagrams should be intact: see mainly for any relationship change.

Notes:

  • When there are many real changes between the model and the bd, I recommend fix 2 or 3 tables on the same time, and set as "Ignore" all other changes detected by Workbench and and re-run above algorithm until your fix all tables.
  • I don't use views or routines, so the "algorithm" is not aware about It. It is all up to you
  • 1
    Single-sentence-answers are rarely helpful. Perhaps you could expand your answer to make it clearer what you are recommending, and why that will help. Also, please take the tour. – Max Vernon Jan 24 at 19:11
  • As the OP stated they had made changes to the model, and were trying to apply those changes to the DB, so they weren't identical - they just weren't as completely different as MySqlWorkbench thought. Or, perhaps you're suggesting that the user recreate the model from the database, apply their changes to the new model, and use that to update the database - in which case, your solution is the same as the accepted answer. – RDFozz Jan 24 at 19:26
  • Just complete my answer, pls check again – atc91 Feb 9 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.