MySQL Bugs: #63699: Invalid cast exception when using .Include for optionally linked tables

Bug #63699	Invalid cast exception when using .Include for optionally linked tables
Submitted:	9 Dec 2011 17:16	Modified:	9 Jan 2012 21:44
Reporter:	Talbot McInnis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.4.4	OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	Exception Include

Description:
I am using .NET connection from a C#.net WPF application and the entity framework.

This query works:
List<Song> DBSongs = bmnce.Songs
.Include("SongArtistsInSongs.SongArtist")
.Include("SongArtistsFeaturedInSongs.SongArtist")
.Where(s => results.Contains(s.ID)).ToList();

Note that not all rows in Songs have entries in SongArtistsFeaturedInSongs.

I am now adding an additional include to another table linked to songs:
List<Song> DBSongs = bmnce.Songs
.Include("SongsInCategories.SongCategory")
.Include("SongArtistsInSongs.SongArtist")
.Include("SongArtistsFeaturedInSongs.SongArtist")
.Where(s => results.Contains(s.ID)).ToList();

This query works if and only if the data being fetched does NOT have an entry in SongArtistsFeaturedInSongs.  If it does, then I get an InvalidCastException saying the object must implement IConvertable.  I've dug into the underlying SQL and for the rows from the SongArtistsFeaturedInSongs table, MySQLworkbench reports them as BLOB type even though when I use the data visualizer they are all integers (IDs, mostly).

How to repeat:
Create three tables, Songs, SongCategories and SongArtists.  Create linking tables for each, (Song -> SongArtist has two linking tables, SongArtistsInSongs and SongArtistsFeaturedInSongs).  Create a song and create an entry in each linking table, and then run the entity query shown above.

Suggested fix:
Ensure that data which starts as integers is never converted to a BLOB in the queries that the connectoer generates.

The SQL query generated by the entity framework which produced the BLOB

Attachment: Offending SQL.sql (application/octet-stream, text), 7.40 KiB.

SQL to create tables (without data)

Attachment: Create tables.sql (application/octet-stream, text), 3.75 KiB.

Hi Talbot,

Thanks for the detailed problem description.
Can you provide the data for the tables involved in the query to ensure that logical links are set correctly?

Talbot,

Thanks for sending the table data.
I created the test project and found a few issues there.
Your LINQ query is referencing objects not mentioned anywhere, so I did some guesswork to avoid the compiler errors:

1. What is "s" in Where(s => results.Contains(s.ID))?

I guessed it is a Song:

Song s = bmnce.Songs.First();

2. What is "results"? It can be literally anything, so I did not come with
   any guess.

Also, I run the offending query in the workbench and none of columns were BLOB type. This could be the server problem on your side since the actual issue is related to BLOB instead of INT in the results. I used MySQL Server 5.5.19.

The server version may be the major difference.  I am upgrading one of my slaves to 5.5.19 and then I'll run the test query.  If I do not get the BLOB results then I think we've found our issue.  I will let you know.

I upgraded our slave to 5.5.19 and the data no longer returns as BLOB.  Would you like to consider this resolved, or should the connector handle this case more gracefully?

Which version of MySQL Server returned BLOB columns?
I tested with 5.1.60 and it was fine as well.

5.0.77.  This was the version recommended by Rackspace as the most stable version (2 years ago, and they do not apply updates unless asked).

Talbot,

thanks for your help and interest in MySQL.

MySQL Server Version 5.0 is not considered high-demand, so the bug is closed.

In case anyone else encouters this, I do want to confirm that after upgrading our servers to 5.5.19 I no longer experience this bug.