Data Type Not Supported

Data Type Not Supported

Postby queuecumber » Fri 28 Jun 2013 17:24

I am very interested in using the product to migrate an existing project from SQL Server using the standard linq to sql support in visual studio 2010 to a MySQL database. For this I chose the trial version, if I can get everything working I will buy a full license.

After using the migration wizard, I am getting a number of "data type not supported" errors. I will post the full error listing below but the issue is with the following two data types:

Code: Select all
The data type 'BigInt NOT NULL IDENTITY' is not supported.

and
Code: Select all
The data type 'NVarChar(50)' is not supported.


I also get the last error on NVarChar(MAX) (I'm assuming any NVarChar type). The first error is very crippling because the automatic SQL Server to MySQL conversion seems to have chosen BigInt as the type for my primary keys.

It looks like either I have something set up wrong in linqconnect or my schema on the mysql server needs to be modified. Can anyone give me a suggestion for how to proceed with either option?

thanks a lot,
Max

Full list of errors:

Code: Select all
Error   108   The ServerDataType value of the Column element 'WavFileName' in the Type element 'Video' is not valid. The data type 'NVarChar(50)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   104   The ServerDataType value of the Column element 'VideoSourceId' in the Type element 'VideoSource' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   111   The ServerDataType value of the Column element 'VideoShotId' in the Type element 'VideoShot' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   112   The ServerDataType value of the Column element 'VideoShotFileName' in the Type element 'VideoShot' is not valid. The data type 'NVarChar(50)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   106   The ServerDataType value of the Column element 'VideoIdAtSource' in the Type element 'Video' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   105   The ServerDataType value of the Column element 'VideoId' in the Type element 'Video' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   109   The ServerDataType value of the Column element 'VideoFrameId' in the Type element 'VideoFrame' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   110   The ServerDataType value of the Column element 'VideoFrameFileName' in the Type element 'VideoFrame' is not valid. The data type 'NVarChar(50)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   107   The ServerDataType value of the Column element 'VideoFileName' in the Type element 'Video' is not valid. The data type 'NVarChar(50) NOT NULL' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   117   The ServerDataType value of the Column element 'UserFriendlyName' in the Type element 'Concept' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   125   The ServerDataType value of the Column element 'TextMetaData' in the Type element 'ConceptInstanceView' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   119   The ServerDataType value of the Column element 'TextMetaData' in the Type element 'ConceptInstance' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   127   The ServerDataType value of the Column element 'SpatioTemporalMetaData' in the Type element 'ConceptInstanceView' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   121   The ServerDataType value of the Column element 'SpatioTemporalMetaData' in the Type element 'ConceptInstance' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   126   The ServerDataType value of the Column element 'SpatialMetaData' in the Type element 'ConceptInstanceView' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   120   The ServerDataType value of the Column element 'SpatialMetaData' in the Type element 'ConceptInstance' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   123   The ServerDataType value of the Column element 'MetaData' in the Type element 'ConceptThreshold' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   102   The ServerDataType value of the Column element 'MetaData' in the Type element 'ConceptEvidence' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   115   The ServerDataType value of the Column element 'MetaData' in the Type element 'Algorithm' is not valid. The data type 'NVarChar(MAX)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   113   The ServerDataType value of the Column element 'FileDirectoryId' in the Type element 'FileDirectory' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   103   The ServerDataType value of the Column element 'DataSourceId' in the Type element 'DataSource' is not valid. The data type 'Int NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   101   The ServerDataType value of the Column element 'ConceptTypeId' in the Type element 'ConceptType' is not valid. The data type 'Int NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   124   The ServerDataType value of the Column element 'ConceptInstanceId' in the Type element 'ConceptInstanceView' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   118   The ServerDataType value of the Column element 'ConceptInstanceId' in the Type element 'ConceptInstance' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   116   The ServerDataType value of the Column element 'ConceptId' in the Type element 'Concept' is not valid. The data type 'Int NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   122   The ServerDataType value of the Column element 'ComparisonOperator' in the Type element 'ConceptThreshold' is not valid. The data type 'NVarChar(50)' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
Error   114   The ServerDataType value of the Column element 'AlgorithmId' in the Type element 'Algorithm' is not valid. The data type 'BigInt NOT NULL IDENTITY' is not supported.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   
queuecumber
 
Posts: 4
Joined: Fri 28 Jun 2013 17:15

Re: Data Type Not Supported

Postby MariiaI » Mon 01 Jul 2013 05:29

After changing your connection (or making a migration) from SQL Server to MySQL, your model still holds storage part and model mapping for the previous database - SQL Server - and because some data types in SQL Server and MySQL are different, the model is not valid.

To make the model valid, you can do the following: perform "Create database script from Model" or "Update database from Model" (if the database does not exist yet, or need to be updated) with the 'Regenerate storage' check box selected. In this case all server data types in the model will be updated to the correct types.

Please tell us if this helps.

JIC: from the next build of dotConnect for MySQL the "Regenerate Storage and Mapping" option for regenerating model parts, specific to the database server, will be implemented.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Data Type Not Supported

Postby queuecumber » Mon 01 Jul 2013 13:09

Ok I think this helped.

Rather than choosing "Update database from model" I chose "Update model from database" in the hope that updating the model would help since the database itself is rather large and would probably take a while to update. This resolved the type errors. Just to be clear, the mysql database already exists and was converted from a sql server database using external tools.

I then changed the namespace for the Context and Entity classes by right clicking in the Model Explorer and selecting properties.

Now I am getting tons of errors about the Entity classes not being defined and I can't seem to find them from intellisense or from the Class View.

For example

Code: Select all
Error   58   The type or namespace name 'ConceptEvidence' could not be found (are you missing a using directive or an assembly reference?)   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Handlers\RemoteProcedures.cs   123   35   AladdinWeb


ConceptEvidence in this case is a table in the database that should have an entity class created with it. I have double checked that namespaces are correct, clicked "Run custom tool" on the lqml file.

Any advice?

Also, when I chose to update the model from the database earlier, all the associations between the database tables disappeared from the model, is that normal or should foreign key relationships automatically be mapped?

thanks,
Max
queuecumber
 
Posts: 4
Joined: Fri 28 Jun 2013 17:15

Re: Data Type Not Supported

Postby MariiaI » Tue 02 Jul 2013 09:32

queuecumber wrote:ConceptEvidence in this case is a table in the database that should have an entity class created with it. I have double checked that namespaces are correct, clicked "Run custom tool" on the lqml file.

Please check that there is an available template in your Devart LinqConnect model (please open your model by double-clicking the *.lqml file, then expand the Templates node in Model Explorer - there should be at least one template, e.g. 'LinqConnect' or 'POCO Entities'). If there are no templates, perform the following steps:
- select "New Template" from the shortcut menu of the "Templates" node in Model Explorer and then select the "LinqConnect" template;
- save changes and re-generate your model.
After saving the model, the code for DataContext and its entities should be generated.

queuecumber wrote:Also, when I chose to update the model from the database earlier, all the associations between the database tables disappeared from the model, is that normal or should foreign key relationships automatically be mapped?

Please specify the version of MySQL server you are working with. There is an issue with not adding associations to LinqConnect models when working with MySQL servers of version 4. In this case, we recommend you to upgrade the MySQL server.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Data Type Not Supported

Postby queuecumber » Tue 02 Jul 2013 11:54

Ok I think we are almost there.

I am now getting errors about missing fields in classes which is because of the missing associations. I tried adding these associations manually but it gave me errors.

Here is the mysql version information.
Code: Select all
-bash-4.1$ mysql --version
mysql  Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1


Is this the version of mysql that has the issue you mentioned?

Once this is finished, I will be attempting to deploy the compiled binaries on Mono. Do you have any recommendations for a Mono deployment? Can the project by compiled on Linux using Mono or should I compile it on Windows?

thanks a lot,
Max
queuecumber
 
Posts: 4
Joined: Fri 28 Jun 2013 17:15

Re: Data Type Not Supported

Postby MariiaI » Thu 04 Jul 2013 10:48

-bash-4.1$ mysql --version
mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

We couldn't reproduce the issue with missing associations on MySQL 5.1.69. Please make sure that all tables in MySQL database were converted correctly from the SQL Server database (all associations have been created).
Please send us the model and the SQL scripts, with which the issue could be reproduced and try implementing this scenario on another version of MySQL server and tell us about the results.
Also, please specify the following:
- whether you are connecting to the MySQL database with a user with full privileges or not;
- what storage engine is used for the tables (e.g., INNODB);
- whether you have selected the 'Recreate Model' check box when processing the "Update Model from Database" wizard?

For more information please refer to the corresponding topic in the Entity Developer documentation: ORM Support->LinqConnect->Updating Model from Database.

I tried adding these associations manually but it gave me errors.
Please specify what exactly errors you are getting.

Can the project by compiled on Linux using Mono or should I compile it on Windows?

For running applications written with LinqConnect under Mono you should deploy all the necessary assemblies to the bin folder of your application (Devart.Data.MySql.dll, Devart.Data.dll, Devart.Data.MySQL.Linq.dll and Devart.Data.Linq.dll).
Please take a look at the topic on technical licensing, as you may need to manually compile the license resource for your Mono project when using LinqConnect Trial/dotConnect for MySQL:
http://www.devart.com/dotconnect/mysql/docs/Licensing.html#mono
For more information about the recommendations for a Mono deployment please refer to this topics on our forum:
http://forums.devart.com/viewtopic.php?t=19773
http://forums.devart.com/viewtopic.php?f=29&t=23676
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Data Type Not Supported

Postby queuecumber » Mon 15 Jul 2013 12:46

Thanks a lot for your help and sorry for the delay getting back to you.

Here is an example of the error I get when I add an association

Code: Select all
Error   10   Custom tool error: The property Source 'VideoShotId' in the class 'ConceptInstance' has been used already.   C:\Users\mehrlich\Projects\AladdinWeb\AladdinWeb\Server\Database\AladdinModel.lqml   1   1   


This happens when I add a one to many association from the VideoShot table to the ConceptInstance table (e.g. one VideoShot has many ConceptInstances assocated with it). There should already by a foreign key relationship between ConceptInstance and VideoShot on the VideoShotId column that is in both tables.

I examined the mysql database and it looks like it is missing foreign keys. Also the engine is set to MyISAM (which is strange I thought we used INNODB). So I am going to talk to the person who did the conversion and see if we can fix that. Do thses things seem like they would cause the problems I am seeing?
queuecumber
 
Posts: 4
Joined: Fri 28 Jun 2013 17:15

Re: Data Type Not Supported

Postby MariiaI » Tue 16 Jul 2013 10:03

Here is an example of the error I get when I add an association

Thank you for the additional information. We have reproduced this issue. It occurs when adding association with the selected "Add foreign key properties to the child class" check box - in this case a new foreign key property is added to the child class with the Source property which has been already specified for another property. We will investigate this issue and inform you about the results as soon as possible.
We recommend you to add the association without selecting the "Add foreign key properties to the child class" check box and specifying referential constraint properties manually in the Association Editor (if you know beforehand fields over which the association is built).
You could also change the Source property for the newly created foreign key property manually in the Property Editor (e.g., by double-clicking the foreign key property and specifying new value in the Source field).
I examined the mysql database and it looks like it is missing foreign keys. Also the engine is set to MyISAM (which is strange I thought we used INNODB).

The MyISAM storage engine doesn't provide foreigh key support (please refer here), that's why your database was created without associations and accordingly the LinqConnect model was also created from this database without them.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Data Type Not Supported

Postby MariiaI » Fri 19 Jul 2013 06:40

The bug related to the wrong Source values for foreign key properties when adding associations with the selected "Add foreign key properties to the child class" check box is fixed.
The fix is included in the new version of dotConnect for MySQL 7.7!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=2&t=27557.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL