I wanted to integrate a .net chat application for my Mauritius Chat project and came across Jabbr which has most if not all of the features I wanted. However it works with SQL Server Express by default but I wanted to use MySql instead. The reason for that is that I’ve got a dedicated where I’m running MySql because it’s free compared to SQL Server 2008/2012 and I didn’t want another service for SQL Server Express to run just for one application – I have to use the limited RAM carefully. So this post is all about how I finally managed to do that.
First thing first – you have to download the source code from Jabbr repository on Github, There’s a Download Zip button on the left hand side.
Next you’ll need to download the MySql .Net Connector 6.9.3 and install it. If you have previous versions of the MySql .Net connector, like I did, you’ll have to uninstall that first.
These are the changes you’ll need in your web.config file:
Connection String
<connectionStrings> <add name="Jabbr" providerName="MySql.Data.MySqlClient" connectionString="Database=Jabbr;Port=3203;Data Source=localhost;User Id=YourUserName;Password=YourPassword;"/> </connectionStrings>
Entity Framework
<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6"> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" /> <providers> <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" /> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> </providers> </entityFramework>
You will also need to reference the MySql.Data.Entity.EF6.dll in your project. You will find that where the MySql .Net Connector has been installed and you will have to choose the assembly according to the .net version you’re using, in my case .net 4.5.
Problems I encountered
When you recompile the project, you’d think it will work but for me it didn’t because it kept complaining about this error:
Schema specified is not valid. Errors:
(15,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘When’ in type ‘JabbR.Models.Attachment’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘When’ in type ‘CodeFirstDatabaseSchema.Attachment’.
(40,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=True,DefaultValue=,Precision=]’ of member ‘RequestPasswordResetValidThrough’ in type ‘JabbR.Models.ChatUser’ is not compatible with ‘MySql.timestamp[Nullable=True,DefaultValue=,Precision=0]’ of member ‘RequestPasswordResetValidThrough’ in type ‘CodeFirstDatabaseSchema.ChatUser’.
(66,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘When’ in type ‘JabbR.Models.ChatMessage’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘When’ in type ‘CodeFirstDatabaseSchema.ChatMessage’.
(95,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘LastActivity’ in type ‘JabbR.Models.ChatClient’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘LastActivity’ in type ‘CodeFirstDatabaseSchema.ChatClient’.
(96,12) : error 2019: Member Mapping specified is not valid. The type ‘Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]’ of member ‘LastClientActivity’ in type ‘JabbR.Models.ChatClient’ is not compatible with ‘MySql.timestamp[Nullable=False,DefaultValue=,Precision=0]’ of member ‘LastClientActivity’ in type ‘CodeFirstDatabaseSchema.ChatClient’.
Basically in the Jabbr code, some of the properties (especially for DateTime) are marked as DateTimeOffset which is stores the date/time as an offset of UTC, rather than the server date/time. However the DateTimeOffset type was being mapped to datetime in MySql and Entity Framework realised it was not a proper mapping. I tried to check for solutions online but couldn’t find any and since this was not a big deal for me (storing the date/time as UTC), I ended up replacing the properties marked as DateTimeOffset as DateTime. When you do this, you also have to modify some parts of the code where it uses DateTimeOffset.UtcNow with just DateTime.Now. I’ve used replace/find and there are less than 5 occurrences if I remember correctly.
Okay at this point, the application compiles but when browsing the site, the application tells me that there’s not database/matching tables yet so I would need to use the Package Manager Console and run the command Update-Database. When I did that, it failed at one specific migration. So I tried to see what was happening by issuing a Update-Database -verbose command and I noticed MySql was complaining about a variable was not declared. That specific migration to was to rename a column from Proivder to Provider, something like that. So I thought I could just exclude that particular migration and do the rename manually but the migration still failed at another point.
I therefore decided to delete the whole Migrations folder so that the schema is created from what’s in the code without having to run it any migrations (this is only good if you’re just setting up the website and you don’t have any data yet). I had to comment out the Migration calls in Startup.cs and run the following command in Package Manager Console: Update-Database
I also issued Enable-Migrations so that if I make any changes, then these are tracked.
In the end, I was able to run Jabbr chat with a MySql database. Now I need to create a custom membership provider so that logins details are checked against the Users table which resides in another project, so I might need to create an API for that.