Bug #56859 DatabaseExists() and CreateDatabase() use wrong database name
Submitted: 19 Sep 2010 14:38 Modified: 26 Dec 2010 21:38
Reporter: Tanguy Gilmont Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.5 OS:Microsoft Windows (Windows7)
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: connection string, connector

[19 Sep 2010 14:38] Tanguy Gilmont
Description:
Environment:
- Windows 7
- MySql.Data.MySqlClient (connector 6.3.4)
- Visual Studio 2010
- .NET4 (4.0.30319 RTMRel), application is .NET4 Client profile.

The two following problems occur, and are most probably related:

1) A call to System.Data.Objects.ObjectContext.DatabaseExists() returns false, even if the database exists. 

2) A call to System.Data.Objects.ObjectContext.CreateDatabase() creates a database but with another name than the one specified in the connection string, then fails to use it when EDM objects are processed.

How to repeat:
Piece of code used:

1) EDM generated automatically from database:

    public partial class projectdbEntities : ObjectContext
    {
[...]
    }

2) Main code:

    public partial class MainWindow : Window
    {
        projectdbEntities Context;
[...]
        void Connect()
        {
                Context = new projectdbEntities(ConnectionString);
                if (!Context.DatabaseExists()) {
                    Context.CreateDatabase();
                }
                CurrentNbr = Context.users.Count();
        }
[...]
    }

3) Connection string set as follows:

ConnectString = string.Format("metadata=res://*/UserModel.csdl|res://*/UserModel.ssdl|res://*/UserModel.msl;provider=MySql.Data.MySqlClient;provider connection string=\"server=localhost;User Id={0};password={1};Persist Security Info=True;database={2}\"",
  DBUser, DBPassword, DBName);

4) If the database exists, DatabaseExists() returns false but the following code works fine:

CurrentNbr = Context.users.Count()

5) If the database doesn't exist and CreateDatabase() is called, it will actually create the database but under the wrong name. Moreover,

CurrentNbr = Context.users.Count();

will fail.

Suggested fix:
The Context.CreateDatabaseScript() returns a creation script that begins with this (when DBName="projectdb1" in connection string described above):

-- MySql script
-- Created on 19/09/2010 16:14:55
DROP DATABASE IF EXISTS `projectdbModelStoreContainer`;
CREATE DATABASE `projectdbModelStoreContainer`;
USE `projectdbModelStoreContainer`;

So obviously the database name is not taken from the connection string (here it comes from the EntityContainer Name attribute of the model edmx file.

The same probably happens with DatabaseExists().

=> suggestion: take the correct database name from the connection string in DatabaseExists() and CreateDatabase() methods.
[19 Sep 2010 14:40] Tanguy Gilmont
The same code used with SQLExpress works perfectly, for both methods.
[28 Sep 2010 18:15] Tanguy Gilmont
Small VS2010 project that illustrates the problem

Attachment: DBExistsTest.zip (application/zip, text), 7.45 KiB.

[28 Sep 2010 18:18] Tanguy Gilmont
Still no feedback, I've decided to add a small project example for VisualStudio 2010, and a database dump.

The program does two tests, one with an existing MySQL database, one with a non-existing one. In both case you can see that DatabaseExists() returns false. The program tries to read the database records no matter what DatabaseExists() returned, and you can see that it can read normally in the first case, and of course it fails with the non-existing database.

Example of output with the database provided in Database\mysql.sql:

    MySQL: DatabaseExists(DBExistsTest) = False
    User #1: Jack
    User #2: John
    MySQL: DatabaseExists(dummy) = False
    Could not read from database

If you add a line

    Console.WriteLine(context.CreateDatabaseScript());

in the code, or if you actually try to create the database with

    context.CreateDatabase();

you will see that the wrong database name is put in the SQL command, hence the error. In the case of this little test application, the name I got was "dbexiststestModelStoreContainer", and should have been "DBExistsTest".

If you do the same with an SQLExpress or SQL database, it works.
[4 Oct 2010 18:14] Reggie Burnett
Fixed in 6.3.5
[6 Oct 2010 15:15] Tony Bedford
An entry has been added to the 6.3.5 changelog:

MySQL Connector/NET experienced two problems as follows:

A call to System.Data.Objects.ObjectContext.DatabaseExists() returned false, even if the database existed.

A call to System.Data.Objects.ObjectContext.CreateDatabase() created a database but with a name other than the one specified in the connection string. It then failed to use it when EDM objects were processed.
[25 Oct 2010 9:54] Tanguy Gilmont
Actually, the problem is not fixed in version 6.3.5.

Now the database part is omitted from the SQL script, which defaults to the MySQL database.

1) If you run the provided example, you will see that it crashes and the exception is clear enough: 

MySql.Data.MySqlClient.MySqlException was unhandled
  Message=Access denied for user 'test'@'localhost' to database 'mysql'

2) If you use the commented line that shows the database creation script, you will see the missing part:

-- MySql script
-- Created on 25/10/2010 11:50:56
CREATE TABLE `users`(
        `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` varchar (80));

=> no CREATE DATABASE, no USE <database name> commands.
[25 Oct 2010 9:57] Tanguy Gilmont
(re-opened the case and updated version)
[25 Oct 2010 10:05] Tanguy Gilmont
I tried a work-around by calling void ObjectContext.DbConnection.ChangeDatabase(string databaseName), but unfortunately this method is not implemented and throws an exception.
[11 Nov 2010 17:41] Tanguy Gilmont
Could you identify the problem?
[17 Dec 2010 15:19] Daniel Lidström
I'm having the same issue. Calling ObjectContext.DatabaseExists() results in MySql.Data.MySqlClient.MySqlException : Access denied for user 'root'@'localhost' (using password: NO). I'm using 6.3.5 with EntityFramework CTP 5.
[21 Dec 2010 14:14] Tanguy Gilmont
Any progress on this? Will you fix the bug?
[24 Dec 2010 12:47] Tanguy Gilmont
Should I create a new PR?
[25 Dec 2010 15:20] Tanguy Gilmont
Apparently nobody would fix this bug, so closing the PR and creating a new one...
[26 Dec 2010 21:38] Tanguy Gilmont
Just noticed that revision 932 referenced this bug as bug #56589 instead of #56859, in case you need this reference for another tool:

"- fixed database methods in EF4 provider services to use the database name from the connection string (bug #56589)"
[29 Dec 2010 16:09] Alex Vi
The current published Connector.NET version (6.3.5) still presents this bug. This makes Connector.NET unusable in most environments (because usually in hosting sites the application does not have access to 'mysql' database).

msi package also has a dependency mismatch between two dll's. A new release should be published solving this errors (maybe 6.3.6?).