One more thing about creation, before I can delve into the work around required for generating alteration script using MySQL Workbench is that, when we generate the create script, the tool adds the db qualifier mydb by default. And, there is no way I could find to change that. So, I remove the qualifier manually whenever I generate the script.
Now, if we have a working database, for which a mysql creation script does not exist or even if it does, it most probably won't contain the mydb qualifier, without which the MySQL Workbench does not generate a correct alteration script. So, for the alter script generation to work correctly:
- Export the existing database structure using any mysql client as a script.
- In MySQL Workbench: File > Import > Reverse Engineer MySQL Create Script
- File > Export > Forward Engineer MySQL Create Script, will generate the sql create script for the existing database with the mydb qualifier.
- File > Export > Forward Engineer MySQL Alter Script.
- Input File: is the path to the sql script for existing database, with the mydb qualifier (the one that was generated in Step 3).
- Output File: is the path to the alteration file, that is to be generated.
- Select Next
- Select Execute
- Now, one can remove the mydb qualifier from the generated file (using search/replace in one's favorite editor)
