MySQL Bugs: #48081: Unable to use DataSet with MySQL NET Connector without installation

Bug #48081	Unable to use DataSet with MySQL NET Connector without installation
Submitted:	15 Oct 2009 12:54	Modified:	23 Feb 2010 19:54
Reporter:	Tomáš Polák	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.7	OS:	Windows (Windows 7 Professional)
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	connector, dataset, factory, install, NET, no, provider

Description:
I want to deploy my project to a server, where the installation of Connector .NET is not possible. So I have taken the MySQL.Data.dll, MySQL.Web.dll and MySQL.VisualStudio.dll and put them to Bin folder of my project. Then I have edited web.config following the machine.config content, when the Connector is installed. I am using Connector 5.2.7

But I have still problems with DataSet: I am not able to select the MySQL datasource, I can not choose the MySQL connection string defined in Web.config although it's there.
If I have an existing dataset, which was working with Connector installed I get errors like this: Configure TableAdapter "table" failed. Unable to find connection 'myConnectionString' for object 'Web.config'. The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded. OR MainDataSet.mytable.GetById could not be previewed. Cannot obtain provider factory for data provider named 'MySql.Data.MySqlClient'.

I think there is a problem with definition of DBProvider in system.data region, but I have it defined exactly like in machine.config, when the Connector is installed.

Here follows my web.config(some parts missing)
<?xml version="1.0"?>
<configuration>
<configSections>
<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere"/>
<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
</sectionGroup>
</sectionGroup>
</sectionGroup>
</configSections>
<appSettings/>

<connectionStrings>
<add name="projectexchangeConnectionString" connectionString="server=localhost;user id=root;database=projectexchange"
providerName="MySql.Data.MySqlClient" />

<!--This connection string is set in machine.config and it should be called this way-->
<remove name="LocalMySqlServer"/>
<add name="LocalMySqlServer" connectionString="server=localhost;user id=root;persist security info=True;database=projectexchange"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
<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=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>

<system.web>
<globalization enableClientBasedCulture="true" culture="en-US" uiCulture="en-US"
requestEncoding="utf-8" responseEncoding="utf-8" fileEncoding="utf-8" />
<roleManager enabled="true" defaultProvider="MySQLRoleProvider" cookieProtection="All">
<providers>
<add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
</roleManager>
<membership defaultProvider="MySQLMembershipProvider">
<providers>
<add connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="true"
applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
passwordStrengthRegularExpression="" name="MySQLMembershipProvider"
type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</providers>
</membership>
<anonymousIdentification enabled="true"/>
<profile automaticSaveEnabled="true">
<providers>
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
<properties>
<add name="Language" allowAnonymous="true" type="System.String" defaultValue="en-US"/>
</properties>
</profile>

How to repeat:
Please try to replicate:
1. Install connector
2. Make project in VS2008 with connection string pointing at MySQL database
3. Make dataset, which is connected through this connection string
4. Copy MySQL related parts of machine config into web.config and MySQL Connector dll's into Bin folder.
5. Uninstall connector
6. Try to access TableAdapters in DataSet and you will get the errrors from above

Suggested fix:
Dunno

Hi Tomáš and thanks for your report.

I believe we have one exactly the same in bugsdb, let me search.

Tomáš, here's what I did...

  o Primary box: W2K8SEx64 with c/NET 6.1.2
  o Build Web site fetching data from remote MySQL 5.1.31 server on OpenSolarisx64 host
  - Precompile and publish web site in VS2008Pro:
------ Build started: Project: C:\...\Connector612Test\, Configuration: Debug Any CPU ------
Pre-compiling Web Site

Building directory '/Connector612Test/App_Code/'.
Building directory '/Connector612Test/'.
Pre-compilation Complete
------ Publish started: Project: C:\...\Connector612Test\, Configuration: Debug Any CPU ------
Connecting to site C:\Users\Administrator\Documents\Visual Studio 2008\Projects\Connector612Test\PrecompiledWeb\Connector612Test...
  o *Copy* by hand all three c/NET libraries to BIN folder
  o Install and configure IIS on Win7 ultimate VM
  o Copy pre-compiled site onto Windows 7 VM with absolutely no trace of c/NET
  o Browse site on Win7 VM

Now, this works without any problems (ie. data from MySQL server is displayed correctly). Could you please test c/NET 6.1.2 and see if that solves the problem? Also, notice I did not play with Windows config files, I just copied the site.

Sample website with DataSet and connector dll's in Bin folder throwing described errors

Attachment: ConnectorTestWebSite.zip (application/x-zip-compressed, text), 145.75 KiB.

Hi Tonci

thanks for your interest.
I have made a sample website, which should display content of ENGINES table in MySQL. I made the corresponding DataSet in dataset designer, copied the dll's and tested with Connector not installed. The errors came up again. Please use VS 2008 and try to edit something in DataSet, it will keeps displaying errors.
You are right, the connector works in this mode but not with the damn DataSet.

I am using version 5.2.7 because with 6.x branch I was not able to add INNODB tables connected by foreign keys into DataSet.

Ummm, don't really know what to say... Error is thrown way before c/NET comes in play:
Server Error in '/' Application.
Parser Error 
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. 

Parser Error Message: Failed to generate code. Exception of type 'System.Data.Design.InternalException' was thrown.

Source Error: 

Line 1:  <?xml version="1.0" encoding="utf-8"?>
Line 2:  <xs:schema id="MainDataSet" targetNamespace="http://tempuri.org/MainDataSet.xsd" xmlns:mstns="http://tempuri.org/MainDataSet.xsd" xmlns="http://tempuri.org/MainDataSet.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
Line 3:    <xs:annotation>

Source File: /App_Code/DataLayer/MainDataSet.xsd    Line: 1

I also can't build your project on my primary box (after removing c/NET) due to same error:
App_Code\DataLayer\MainDataSet.xsd(1): Build (web): Failed to generate code. Exception of type 'System.Data.Design.InternalException' was thrown.

So, I'll leave this as verified and let Reggie decide if this is a bug since it could be that you're just missing some reference.

Any progress guys???

What I think we are seeing here is a slight misunderstanding as to how the bin folder works.  The bin folder is where assemblies can be drawn from *at runtime*.  If you notice when a website is being developed the bin folder is not there even when the site is built.  It is only created when the site is *published*.  This gives you an idea that the bin folder is not referenced during design.

There are two things to fix this:
1.  Install the connector in the GAC on your development box.  The assembly in the bin folder will still work once you deploy to your remote host.

2.  Add a reference to the assembly in the bin folder.

Hope this helps.