Can't use several databases using the same data model file

[krypta]

I have been evaluating your dotConnect for MySQL since 2 days now but I have a problem.

I have a web application which needs to connect to several MySQL databases which have the same structure by using the same Entity Data Model file. This is working on SQL Server and on MySQL through Connector/Net but not with your dotConnect.

Steps to reproduce the issue:

On MySQL create two identical databases "a" and "b" with a "test" table inside

Create "userA" with SELECT privileges on database "a"
Create "userB" with SELECT privileges on database "b"

With Visual Studio create the Entity Data Model for database "a".

In web.config create a second connection string with same model references but different database and credentials as follows

Code: Select all

In a web page put a dropdownlist and a gridview

Code: Select all

	a
	b

In code behind

Code: Select all

protected void Page_Load(object sender, EventArgs e)
{
	if (!IsPostBack)
	{
		using (var ctx = new aEntities1())
		{
			GridView1.DataSource = ctx.test;
			GridView1.DataBind();
		}
	}
}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
	string sel = DropDownList1.SelectedItem.Text;
	
	//use the selected item to point to "aEntities" or "bEntities"
	//call the constructor with the connection string or an EntityConnection object  
	//using (var ctx = new aEntities(WebConfigurationManager.ConnectionStrings[sel + "Entities"].ConnectionString))
	using (var ctx = new aEntities1(new EntityConnection(WebConfigurationManager.ConnectionStrings[sel + "Entities"].ToString())))
	{
		GridView1.DataSource = ctx.test;
		GridView1.DataBind();
	}
}

If you select "a" everything works, if you select "b" you get this error

SELECT command denied to user 'userB'@'localhost' for table 'test'

Exception Details: Devart.Data.MySql.MySqlException: SELECT command denied to user 'userB'@'localhost' for table 'test'

If you give "userB" SELECT privileges on database "a" then it works but still retrieves data from database "a" which probably means that "Database=b" from the bEntities connection string is not properly used.

If I am doing something wrong or you need further details please let me know.

Best regards.
K.

Edit -> sel + "Entities1" is actually sel + "Entities"

[Shalex]

It seems like the problem is the usage of schema "a" in the queries when connection string is for user "b". You can check this with dbMonitor:
http://www.devart.com/dbmonitor/dbmon3.exe (download link)
http://www.devart.com/dotconnect/mysql/ ... nitor.html (documentation)

Possible solutions:
1. http://www.devart.com/blogs/dotconnect/ ... qlite.html:

Code: Select all

      Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfig.Instance;  
      config.Workarounds.IgnoreSchemaName = true;

2. Remove the Schema attributes and DefiningQueries from the SSDL part of your model.