MySQL Bugs: #59168: DatabaseExists() and CreateDatabase() generate incorrect MySQL script

Bug #59168	DatabaseExists() and CreateDatabase() generate incorrect MySQL script
Submitted:	25 Dec 2010 15:19	Modified:	1 Jun 2011 23:26
Reporter:	Tanguy Gilmont	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.3.5	OS:	Windows (Windows 7)
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	connector, Contribution

Description:
Environment:
- Windows 7
- MySql.Data.MySqlClient (connector 6.3.5)
- 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() triggers a MySqlException(Access denied), even though the connection string is correct and works with accessing / modifying an existing database.

2) A call to System.Data.Objects.ObjectContext.CreateDatabase() triggers a MySqlException(Access denied), even though the connection string is correct and works with accessing / modifying an existing database.

How to repeat:
1) Download and unzip DBExistsTest.zip file.
2) Create a database from the following script, with enough credentials for user 'test', password 'test':

  DBExistsTest\Database\mysql.sql

3) Either recompile the project or directly execute the binary:

  DBExistsTest\DBExistsTest\bin\Debug\DBExistsTest.exe

=>

- error visible in script returned by CreateDatabaseScript(), no CREATE DATABASE, no USE <database name> commands;
- error visible in call to DatabaseExists();
- error visible in call to CreateDatabase();
- listing 'users' records works fine.

Suggested fix:
Fix for PR 56859 obviously broke something else, the database is not specified when the DatabaseExists() or CreateDatabase() methods are called.

Re-introducing the 'USE' and 'CREATE DATABASE' MySQL commands will definitely help.

Suggesting also enough testing before issuing new version, the provided example should help.

Example to reproduce the problems and to test a possible fix

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

Simple example to reproduce the problems and to test a possible fix

Attachment: DBExistsTest.zip (application/x-zip-compressed, text), 13.14 KiB.

Hi Tanguy,

Thank you for the detailed description of the problem and the test case.
I tried running your project and here is the output that I got:
------------------------------------------------------
context.CreateDatabaseScript() output:
-- MySql script
-- Created on 30/12/2010 7:37:57 PM
CREATE TABLE `users`(
        `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` varchar (80));

MySQL: DatabaseExists(DBExistsTest) = True
CreateDatabase() triggered an exception:
MySql.Data.MySqlClient.MySqlException (0x80004005): Can't create database 'dbexi
ststest'; database exists
..............
User #1: Jack
User #2: John
------------------------------------------------------

As you see, DatabaseExists(DBExistsTest) = True, which is correct and therefore CreateDatabase() resulted in exception. This is the expected behavior, so I set the status "cannot repeat".

Please let me know if you have any comments or additional details.

What are the server settings for the user 'test', regarding the mysql database access? I did the test again, and if this time I grant total r/w access to 'test', I get the same correct results as yours, only this is not a security scheme we can allow in the field.

Aren't there some similar assumptions in the connector test cases? I've seen they were pretty exhaustive but that kind of difference could explain a few things.

After downloading the sources I've also noticed that I could be wrong in assuming the error in CreateDatabaseScript() was perhaps related to the errors in the other two methods. From the connector code, one can see in EF4ProviderServices.cs (DbCreateDatabaseScript method) that it doesn't write those 'CREATE DATABASE' / 'USE' commands in the DbCreateDatabaseScript() method but in the DbCreateDatabase() method.

That being said, I don't see where this file is used (apparently it is not part of the connector VS solution and is not compiled) so I can't be 100% sure.

I granted the test user all privileges to all databases.
What permission set you had for your test user?

EF4ProviderServices.cs is the part of the entity provider (MySql.Data.Entity.dll), which is loaded when the application uses the entity framework.

The 'test' user only has full access to the test database, in this case 'dbexiststest'. No particular access is granted regarding the 'mysql' database which is the main server configuration database, and from the error I got it seems the connector tries to do something there.

Something else is troubling, the database name is not available from the context object...

You can verify that by checking the following value with the debugger, in the DBExistsTest.Program.Test method:

    context.Connection.Database

So if for example you want to do something like this, it will not work:

using (var context = new dbexiststestEntities()) {
    string script = string.Format(
        "CREATE DATABASE IF NOT EXISTS `{0}`;\nUSE `{0}`;\nDROP TABLE IF EXISTS `{1}`;\n{2}",

        context.Connection.Database,  // *** will not work ***

        context.users.EntitySet.Name,
        context.CreateDatabaseScript()
    );
    ctx.ExecuteStoreCommand(script);
}

Has this PR been forgotten?

Re-opening through close / open.

If some unrealistic credentials have to be given for the unit tests to work, it looks like those tests should be reviewed asap because it doesn't guarantee the correct functioning in the field.

Strange, now the program finishes with the following exception:

Unhandled Exception: System.Data.ProviderIncompatibleException: CreateDatabaseScript is not supported by the provider.
   at System.Data.Common.DbProviderServices.DbCreateDatabaseScript(String providerManifestToken, StoreItemCollection storeItemCollection)
   at System.Data.Objects.ObjectContext.CreateDatabaseScript()
   at DBExistsTest.Program.Test(String dbName, String dbUser, String dbPassword, SqlType sqlType) in C:\Projects\bugs\59168\DBExistsTest\DBExistsTest\
DBExistsTest\Program.cs:line 23
   at DBExistsTest.Program.Main(String[] args) in C:\Projects\bugs\59168\DBExistsTest\DBExistsTest\DBExistsTest\Program.cs:line 14

Strange, the program does not even try to connect when the above exception occurs. This all happens with the current GA Connector/NET 6.3.6.

Installing 6.3.5 fixed the situation, so first we have to find out what has been broken in 6.3.6.

The MySqlProviderServices class would need to override the DbCreateDatabaseScript(string,
StoreItemCollection) method to support the CreateDatabaseScript method. As far as I can see, v6.3.6 doesn't override this method, which is why you're getting the exception.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed versions: 6.3.7, 6.4.1+

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html