Toad World® Forums

Converting database from MySQL to Postgres8.4


#1

(TDM 3.6.7.1)

Hi, hope everything is well.

I don’t know if I should log each of these problems in it’s own thread rather, but they both concern conversion from MySQL to Postgres.

  1. I picked up a problem when converting from MySQL to Postgres8.4 databases. The already removed relationships appear again in the converted database.

You can replicate this by creating a small MySQL model, then remove one of the relationships by clicking on the relationship line, Del button and then “delete from model” option. Then just do a normal conversion to Postrges8.4.

The relation is created again and without a caption.

  1. Along with this, a lot of my Primary key names duplicated. I can’t replicate this as such, because not all the tables did this. I ran a Verify before converting, then everything was fine… after conversion I run a verify again and see that a lot of the primary keys changed to another entity’s PK name.

Let me know if there’s more information needed.

Regards,
Rudi


#2

Hi Rudi,

thank you for your feedback!

  1. I tried to create a model for MySQL 5.1, added three entities with some attributes and PKs to it and draw one identifying and one non-identifying relationship. Then I clicked each relationship line and selected Delete from Model. After that action relationship disappeared from Physical Model Explorer - please see what you have in your physical model explorer in folder Relationships. The deleted relationships should not be there. Tip: you can also use SHIFT+Del instead of Del. This will delete objects from model directly.

I also tried to convert the model to PostgreSQL 8.4 and relationships were not converted, because they were not in my MySQL 5.1 model.

  1. What did you try to verify, please? MySQL model before conversion and the same MySQL model after conversion? The MySQL model, as a source model, should not get modified by the conversion process. Please write me more information. Thank you very much.

  2. Or could you send us the model to modeling@quest.com?

Regards,

Vaclav Message was edited by: modelingadmin Message was edited by: dkretek

Message was edited by: dkretek


#3

Thanks for the response.

  1. After checking in my MySQL model, I found that the relationship is still visible in my relationship tree on the left, but the model doesn’t display it as such. I see the physical relationship line, but when I hover over it like with other relationship lines, the child attribute doesn’t highlite in the child entity, the FK attribute isn’t in that entity anymore. This might be because of a previous bug maybe, because this relationship was removed quite a few version ago. So I guess this answers the question on why the relationship was re-inserted in the postgres data model when I converted.

  2. Sorry, I was a bit unclear with this one. The Primary key names duplicated in the Postgres database after conversion. In the MySQL Model before conversion, the two entities’ PK names was different according to my standards and they still are in the MySQL database, but in the postgres database after conversion, those same two entities now have the same PK name. I was thinking… A lot of the time, I copy an entity if I want to create a new entity with basically the same attributes and I then just change the names of the entity name, keys, certain attributes etc. Can this maybe have an effect?

Thanks for all the assistance


#4

Hi Rudi,

thank your for sending me details.

  1. Please try to Test and Repair your model. This might be caused by a bug in earlier version of TDM. Read the following article to find out how to test and repair your model:
    http://blogs.inside.quest.com/modeling/2010/10/25/how-to-test-and-repair-your-model/

  2. Can you send me your model to modeling@quest.com and write me what entities you refer to, plaase? Foreign keys mapping may solve this, but I am not sure. See the Help file, section Model | Physical Data Model | Entity Relationship Diagram | Keys | Foreign Keys | FK Mapping.

Regards,

Vaclav


#5

Hi,

Thanks for all assistance… but I already sorted it out by just writing a small script that just renames the postgres model’s PK names and captions to the ones found in the original mysql model.

I also fixed the original problem in the same manner, but by reversing the existing database.

I can send normal database models that I create to replicate a problem, but in this case, it’s hard, because the original removal of the relationships took place in older versions. I cannot send the model I’m working on though, because it’s not a personal model of mine, but my company’s… sure you’ll understand.

This issue can be marked as answered, I know more about this now and will check when removing relationships that it actually removes from the relationship list on the left as well… else i’ll immediately build you a mock model to send you guys.

Thanks again for the quick responses.

Best Regards
Rudi


#6

Rudi,

thank you for sending me additional information. Of course feel free to write us again in case you need our assistance.

Regards,

Vaclav