MySQL Bugs: #18860: Export SQL Create Script

Bug #18860	Export SQL Create Script
Submitted:	6 Apr 2006 17:46	Modified:	26 Apr 2006 2:48
Reporter:	Klaus Bensch	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Workbench Preview	Severity:	S3 (Non-critical)
Version:	1.0.5./1.0.6	OS:	Windows (WinXP-SP2)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
Since the Synchronisation Tool is not functional on my machine (if someone knows a fix please let me know) I tried to export the model as a SQL-file for direct import with the command line tool 'mysql'. The resulting SQL-file is not functional.

Maybe the listed errors are responsible that the synchronisation fails. I have not tried every datatype, therefore there could be more bugs.

How to repeat:
Load file a2z.wmb.
Export model as a2z.sql.
Execute in a shell 'mysql -u root -p a2z < a2z.sql'.
This will fail.
Try with the fixed skript a2z-fix.sql.

Suggested fix:
1. File output format should be ANSI or similar. -> Convert with text editor.
2. DOUBLE(0, 0) -> Change to DOUBLE.
3. VARCHAR: uncomplete COLLATION definition -> delete this part
4. uncomplete foreign key definition -> add datatype
5. Foreign keys with identical names. MySQL accepts foreign keys with identical names, but the Export tool creates constraints with names derived from foreign keys without a check, which will be rejected by the server. -> Add an index to a double constraint.
6. Order does matter for primary and foreign keys. -> Make sure that your primary key is always the first key and the foreign keys are in the same order as in the dependent table. -> It should be possible to order keys in the table editor by drag and drop.

test model

Attachment: a2z.zip (application/x-zip-compressed, text), 22.44 KiB.

fixed skript

Attachment: a2z-fix.sql (application/octet-stream, text), 12.09 KiB.

Thank you for the bug report.