MySQL Bugs: #43274: Generated SQL commands ignores provider connection's database parameter

Bug #43274	Generated SQL commands ignores provider connection's database parameter
Submitted:	28 Feb 2009 2:04	Modified:	3 Apr 2009 19:50
Reporter:	Petr Vones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0 alpha - revision 1519	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	database, entity framework, schema

Description:
Generated Entity Framework model can not be use for different database (schema) name just by changing provider connection string parameter database. SQL commands generator composes the table object name from Schema attribute value (EntitySet elements) in generated SSDL file and table name. The MySQL provider connection string parameter database is being ignored. 

How to repeat:
1. Create database test1 with a table Table1 and few columns, insert a data to the table Table1
2. Create database test2 with the same structure as test1, insert different data to the table Table1
3. Create Entity Framework model for database test1
4. Create a sample application reading a value from Table1
5. Run the sample aplication
6. The application reads data from database test1 as configured.

7. Change parameter "database" value to "test2" in "provider connection string" of entity connection string in the .config file.
8. Run the sample aplication again

Expected: Generated SQL commands will read data from database test2
Actual: Generated SQL commands reads data from database test1

Suggested fix:
Allow empty Schema attribute value in SSDL file for EntitySet elements. In such case, SQL generator will compose the table object name by using the name only without prefixing it by (SSDL file hardcoded) schema name. 

So instead of generated SQL command:
SELECT * FROM `Test1`.`Table1`

It should generate:
SELECT * FROM `Table1`

Note: Microsoft SQL Server provider uses database owner (typically "dbo.") as schema attribute value that does not affect generated SQL commands database name.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70971

An entry was added to the 6.0.1 changelog:

The generated Entity Framework model could not be used for a different database (schema) name by changing the provider's database connection string parameter. The SQL commands generator composed the table object name from the Schema attribute value (EntitySet elements) in the generated SSDL file and the table name. The MySQL provider database connection string parameter was being ignored.

Please reopen. It is partial fix only. It does not work for stored procedures. They are still being looked in original model schema name ignoring connection string database parameter. There is the exception:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. 
---> MySql.Data.MySqlClient.MySqlException: Procedure or function '`FindUserLockUpdate`' cannot be found in database '`databasemodelschemaname`'.
   at MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName)
   at MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName)
   at MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

stored procedure fix in 6.0.2