PDA

View Full Version : MySQL and .Net connection and provider



crosswaycc.org
September 1st, 2011, 09:38 AM
I am having difficulty connection my .NET application to a MySQL database. It seems that the documentation is missing or out of date for making this connection. I've attempted the following connection strings and providers (passwords blanked), with the following results:

Connection String: "SERVER=10.10.11.3;USER=browser;PASSWORD=********;D ATABASE=calendar"
Provider: MySql.Data.MySqlClient
Error Result: Unable to find the requested .Net Framework Data Provider. It may not be installed.

Connection String: "DSN=calendar;USER=browser;PASSWORD=********"
Provider: System.Data.Odbc
Error Result: ERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'browser'@'10.10.1.4' (using password: YES)

The database "calendar" exists with user "browser" set up for access. DSN "calendar" has been set up as well.

davidd
September 1st, 2011, 10:56 AM
Connection String: "SERVER=10.10.11.3;USER=browser;PASSWORD=********; DATABASE=calendar"
Provider: MySql.Data.MySqlClient
Error Result: Unable to find the requested .Net Framework Data Provider. It may not be installed.

In order to resolve this error, please ensure that you have uploaded the "mysql.data.dll" file to your /bin folder. Then add the following to your web.config file anywhere within the 'configuration' element:


<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, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
NOTE: Replace the version number (Version=6.1.2.0) with the actual version of the 'mysql.data.dll' you are using.




Connection String: "DSN=calendar;USER=browser;PASSWORD=********"
Provider: System.Data.Odbc
Error Result: ERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'browser'@'10.10.1.4' (using password: YES)

I would recommend using the first method to connect as using the MySQL Connector/Net driver will give you better performance than using a DSN. However, this error generally indicates that the password used to connect was incorrect. I would recommend resetting the password at all locations (database, dsn, and connection string) to ensure they are all matching.

crosswaycc.org
September 2nd, 2011, 10:56 AM
Thank you, this has been done. I would rather go through the .NET-native MySQL provider than the slower ODBC connection.

However, now I'm getting the following message:


Security Exception

Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Is this something I can adjust?

Brian
September 2nd, 2011, 11:29 AM
Yes, you can set the site to use Full Trust by logging into the control panel. Then choose the ASP.NET option and enable Full Trust.

crosswaycc.org
September 2nd, 2011, 11:51 AM
Hmm... I don't see an ASP.NET option in my Helm control panel. Am I missing something, or am I in the wrong place?

Brian
September 2nd, 2011, 12:01 PM
We need to get you moved over to our new WCP control panel that has this and other additional features. Please submit a ticket (http://support.hostek.com) with your domain name and request to be moved to the WCP control panel and we'll get that completed for you.

crosswaycc.org
October 7th, 2011, 02:04 PM
WCP is installed and the proper "Full Trust" setting enabled. My applications can connect and get data from the MySQL database tables OK, however they are occasionally receiving one or both of the following errors upon the first connection:


Access to the registry key 'Global' is denied.
at Microsoft.Win32.RegistryKey.Win32Error(Int32 errorCode, String str)
at Microsoft.Win32.RegistryKey.InternalGetValue(Strin g name, Object defaultValue, Boolean doNotExpand, Boolean checkSecurity)
at Microsoft.Win32.RegistryKey.GetValue(String name)
at System.Diagnostics.PerformanceMonitor.GetData(Stri ng item)
at System.Diagnostics.PerformanceCounterLib.GetPerfor manceData(String item)
at System.Diagnostics.PerformanceCounterLib.get_Categ oryTable()
at System.Diagnostics.PerformanceCounterLib.CategoryE xists(String machine, String category)
at System.Diagnostics.PerformanceCounterCategory.Exis ts(String categoryName, String machineName)
at System.Diagnostics.PerformanceCounterCategory.Exis ts(String categoryName)
at MySql.Data.MySqlClient.PerformanceMonitor..ctor(My SqlConnection connection)
at MySql.Data.MySqlClient.MySqlConnection.Open()

Connection unexpectedly terminated.
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadOk(Boolean read)
at MySql.Data.MySqlClient.NativeDriver.SetDatabase(St ring dbName)
at MySql.Data.MySqlClient.MySqlConnection.ChangeDatab ase(String database)
at MySql.Data.MySqlClient.MySqlConnection.Open()

The workaround in place is a limited retry, since one or both of these errors usually only occur once during the session. The application does this automatically, so the end user doesn't notice, but I'd rather not have to do this.

davidd
October 7th, 2011, 03:56 PM
I was checking into this and noticed that you are currently using a very old version of the MySQL Connector/Net (mysql.data.dll). Since these issue may have been resolved in later releases of MySQL Connector, I would recommend updating your site to use the latest release (currently 6.4.4) available from MySQL at http://dev.mysql.com/downloads/connector/net/.

NOTE: In addition to updating to the new DLL, you will also need to update the version number within the web.config's 'DbProviderFactories' section.

If you continue to notice any issues after updating the Connector version, please let us know; and we can continue trouble-shooting from there.

crosswaycc.org
October 10th, 2011, 08:05 AM
I think I tried using the latest version of the DLL before, but received an error (I forget what). Looking through PHPMyAdmin, the MySQL version is reported as "Server version: 5.0.77-log", so I installed version 5 of the DLL and it worked. I can try again if you recommend it, but I'll need to revert it immediately if it doesn't work. The database is now live on the site.

davidd
October 10th, 2011, 09:46 AM
I do recommend updating the MySQL connector to the latest version since there is a good chance that will resolve the current issues. For a live site, the best way to test these types of changes is to create a sub-domain (such as "dev.domain.com") that has a copy of the website and use it as a development version of the site. That way, you can make changes to the development site and test them without affecting the live site.

crosswaycc.org
October 13th, 2011, 01:22 PM
It's working properly now on the updated DLL with no errors. Thank you.