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

[25 Dec 2010 15:19] Tanguy Gilmont
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.
[25 Dec 2010 15:19] Tanguy Gilmont
Example to reproduce the problems and to test a possible fix

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

[27 Dec 2010 13:13] Tanguy Gilmont
Simple example to reproduce the problems and to test a possible fix

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

[30 Dec 2010 8:44] Bogdan Degtyariov
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.
[30 Dec 2010 9:41] Tanguy Gilmont
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.
[30 Dec 2010 11:29] Bogdan Degtyariov
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.
[30 Dec 2010 11:42] Tanguy Gilmont
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.
[31 Dec 2010 14:08] Tanguy Gilmont
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);
}
[30 Jan 2011 10:13] Tanguy Gilmont
Has this PR been forgotten?
[30 Jan 2011 10:13] Tanguy Gilmont
Re-opening through close / open.
[30 Jan 2011 10:16] Tanguy Gilmont
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.
[31 Jan 2011 9:24] Bogdan Degtyariov
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
[31 Jan 2011 9:48] Bogdan Degtyariov
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.
[28 Feb 2011 20:56] Richard Deeming
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.
[1 Jun 2011 23:26] Julio Casal
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