MySQL Bugs: #91141: EntityFrameworkCore migration

Bug #91141	EntityFrameworkCore migration
Submitted:	5 Jun 2018 1:12	Modified:	6 Jun 2018 8:32
Reporter:	Nabil Hadjar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
i attempt to use two database providers sqlserver,mysql, and i have a property with a byte[] type.
applying migration has succeeded with sqlserver with varbinary(MAX), but failed when used with mysql.

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE Sliders (
Id int NOT NULL AUTO_INCREMENT,
BackgroundImage varbinary NULL,
Test text NULL,
Text text NOT NULL,
Title text NOT NULL,
PRIMARY KEY (Id)
);
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL,
Test text NULL,
Text text NOT NULL,
Title text NOT NU' at line 3

How to repeat:
1- i have defined an entity class which contains a byte[] property type.
2- create i new migration using "dotnet ef" tool.
3- update the database using sql server as a database provider.
4- the property of type byte[] has been mapped with type varbinary(MAX).
5- change the datasource to mysql and apply the same migration.
6- i got this error.

ailed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE Sliders (
Id int NOT NULL AUTO_INCREMENT,
BackgroundImage varbinary NULL,
Test text NULL,
Text text NOT NULL,
Title text NOT NULL,
PRIMARY KEY (Id)
);
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL,
Test text NULL,
Text text NOT NULL,
Title text NOT NU' at line 3

Suggested fix:
use the maximum number supported by varbinay to get a close user experience to sql server

The generated fails because no length is specified for the VARBINARY data column.

.. Peter
-- not a MySQL/Oracle person

The generated *statement* fails ...

.. I meant.

.. and if the column really is supposed to store an image it should probably be a BLOB or even LONGBLOB ..

This ressoruce may be useful https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mapping....

Here it says that .NET Framework type "byte[]" should be mapped to SqlDbType "varbinary".  But I don't know about properties, limitations and range of a SQL Server varbinary and also not if SQL SErver requires data length specified for a varbinary. 

With MySQL I believe [LONG]BLOB would be better.

thank you  Peter Laursen for comments.
for length specification and according to this document : 
http://www.entityframeworktutorial.net/code-first/maxlength-minlength-dataannotations-attr...
- i have specified the data length, but i got the same problem
- then i removed the "byte[]" type and replace it with "string" type, it has been mapped successfuly to varchar(length), where length is the length specified in the data annotation.
so we have a problem with the varbinary and not length specification.
for the LONGBLOB i agree with you, because it's the closest to varchar(max).

Hello Nabil Hadjar,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.