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 3:04	Modified:	3 Apr 21:50
Reporter:	Petr Vones
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	6.0 alpha - revision 1519	OS:	Microsoft Windows
Assigned to:	Tonci Grgin	Target Version:
Tags:	entity framework, schema, database

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