MySQL Bugs: #59244: Column types incorrectly generated with model-first

Bug #59244	Column types incorrectly generated with model-first
Submitted:	31 Dec 2010 14:50	Modified:	13 Jul 2011 15:52
Reporter:	Tanguy Gilmont	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.3.5	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	connector, model-first

Description:
The choice made by the connector regarding some types, when a database is generated from a model, is not correct.

A test case is uploaded, with a comparison between SQL and MySQL models and their generated scripts, to illustrate those problems, so I won't detail the whole procedure here. But in summary, here are some of the issues:

1) The 'Name' EDM property is a string with 
- Fixed Length = False
- Max Length = 80

=> translated to NCHAR(80) in the MySQL script. First, syntaxically it should probably be CHAR and not NCHAR which is SQL, but what it should really be is VARCHAR(80) since the length is not fixed, here we get padded columns which is clearly not what is wanted.

=> correctly translated to NVARCHAR(80) in SQL.

2) 'Description' is a string with
- Fixed Length = False
- Max Length = Max (or 65535)
- Nullable = True

=> translated to NVARCHAR(65535) (thanks to CHAR being limited to 255!), it would be great to be able to get a text (or mediumtext and so on with larger sizes which here generate an error). And maybe VARCHAR i/o NVARCHAR, though I'm not sure of how much the latter is supported.

3) 'Archive' is a binary with
- Fixed Length = False
- Max Length = Max (or 65535, or more)
- Nullable = True

=> translated to VARBINARY(65535), it would be great to be able to get a blob or larger.

How to repeat:
A test case is uploaded with two models:

- EFModel_MySQL.edmx
- EFModel_SQL.edmx

and the generated scripts for comparison.

Suggested fix:
The CHAR vs VARCHAR is straightforward, I'm not sure about the TEXT / BLOB vs VARCHAR / VARBINARY though.

Test case

Attachment: ModelFirstMySQL.zip (application/zip, text), 154.62 KiB.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed on 6.4.4, 6.3.9, 6.2.6