Up and Running with RIA Services, EF 4, and MySQL

I saw several devs were having trouble with this stack so I thought I'd do a quick how-to post since I'm hosting truthwidget.com here with no trouble. I'll assume you have MySQL 5.n installed localhost as well as MySQL Connector Net 6.n.n and have been successfully developing against it and EF 4 on top of it. Now it's time to deploy to discountasp.net:

(1) So that RIA works correctly in Web.config disable basic authentication:


<configuration>
<system.web>
<system.webServer>
<security>
<authentication>
<basicAuthentication enabled="false" />
</authentication>
</security>
</system.webServer>
</system.web>
</configuration>


(2) Also in Web.config configure MySQL as a provider:


<system.data>
<DbProviderFactories>
<add name="MySQL Data Provider"
invariant="MySql.Data.MySqlClient"
description=".Net Framework Data Provider for MySQL"
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" />
</DbProviderFactories>
</system.data>


(3) Bin deploy the following DLLs as they are not in the GAC:

System.ServiceModel
System.ServiceModel.DomainServices.EntityFramework
System.ServiceModel.DomainServices.Hosting
System.ServiceModel.DomainServices.Server
MySql.Data

That's it. Another thing I did was use Web.config transforms so that I could put my localhost EF connection string in Web.config per normal but my prod connection string in the Web.Release.config.

James
truthwidget.com a production of squarewidget.com

 

I spoke too soon. Last night I made some trivial changes to my app (swapped out an image and changed a style sheet) and after testing locally published a release build up to DASP. The app broke. My logs and stack trace show that EF cannot call MySqlClientFactory and get back a provider. I didn't even touch my Web.config.

I can connect via MySQL Workbench to the database instance and query data. I can also run my app localhost pointing to the database on DASP no problem. It's only when I try to run the app on DASP that the error occurs. I've been at this for several hours. I can't see the server or the machine.config so I'm not going to point fingers. But I've come to the conclusion that I can't take a chance on MySQL in this environment. I've put in a ticket to swap MySQL for SQL Server 2008. I know that it plays nice with my stack.

In short, I do not recommend MySQL in this environment if you're using WCF RIA Services and EF.

 

Despite my setback the folks here at DASP got me set up with SQL Server 2008 R2 the very next day I requested it and today refunded my MySQL order. Awesome service. All I had to do was change EF to point to SQL Server, migrate my data to the new database, and I was up and running in no time. Check out TruthWidget.com and rate your favorite assertions!

 

Awesome! ;-)

 

Works for Me

I was able to get Ria Services/ EF4 / MySQL working here on DASP.

The key lines in the config are:

<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

</DbProviderFactories>
</system.data>

Note that you need to make sure the version is correct with the connector you have.

<add name="JetEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=mysql501.discountasp.net;User Id=*****;password=*****;Persist Security Info=True;database=MYSQL5_717577_JET&quot;" providerName="MySQL Data Provider" />

Then make sure that MySql.Data.dll AND MySqlData.Entity.dll are both in the bin directory. You can do this by adding a reference to them in your web project, and then setting CopyLocal to true. Also make sure the usual EF dlls are present.

Also of course make sure your site is using .Net 4.0.

That's it! GoMySql!

 

Lots of good info here, thanks.