MySQL Bugs: #56859: DatabaseExists() and CreateDatabase() use wrong database name

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:	Windows (Windows7)
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	connection string, connector

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.

The same code used with SQLExpress works perfectly, for both methods.

Small VS2010 project that illustrates the problem

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

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.

Fixed in 6.3.5

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.

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.

(re-opened the case and updated version)

I tried a work-around by calling void ObjectContext.DbConnection.ChangeDatabase(string databaseName), but unfortunately this method is not implemented and throws an exception.

Could you identify the problem?

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.

Any progress on this? Will you fix the bug?

Should I create a new PR?

Apparently nobody would fix this bug, so closing the PR and creating a new one...

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)"

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?).