MySQL Bugs: #71874: MySql 5.7 EF6 Error

Bug #71874	MySql 5.7 EF6 Error
Submitted:	28 Feb 2014 3:45	Modified:	11 Jul 2014 17:01
Reporter:	Thomas Atwood	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.7	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Similar to the comment made in the MySql 5.7 EntityFramework 6 documentation, EF6 support seems not be working using the code example provide.  Similar to commenter EC KONG, when attempting to write the objects using the AddRange method, the following exception occurs: InnerException = {"Authentication to host 'localhost' for user 'root' using method 'mysql_native_password' failed with message: Access denied for user 'root'@'localhost' (using password: NO)"}"

I followed the comments below EC KONG's comment that suggested adding Persist Security Info = True.  That did not fix the error.  This seems to be a bug or something is overlooked in the documentation.

How to repeat:
Follow the steps and use the code provided in Chapter 10 EF 6 Support online documentation.  The code correctly creates the parking schema and cars table.  However, when the code hits the AddRange method, the above noted exception is thrown.  This is either a bug or the documentation is missing a necessary step for proper execution.

As an update, the code works great with 5.6.16.  This seems to be a 5.7 specific issue.

Hello Thomas.

In the example code the Database Context object is created from an existing connection which is a new feature added on EF6, the "problem" in the code is the way Connector/Net handles the connection used to create the Database Context object, for security reasons a Connection used in Connector/Net has no password, the password is encrypted and saved internally so you will be able to use a connection because it has a reference to the encrypted password. 

But in the code when the Database Context object is created it receives an opened connection which has no password, so when the code reach the "AddRange" method the Database Context tries to initialize the objects in the Database but the connection given to perform the actions has no reference to the already encrypted password, so it tries to access the database without using a password, that's why the error "Authentication to host 'localhost' for user 'root' using method 'mysql_native_password' failed with message: Access denied for user 'root'@'localhost' (using password: NO)" is present.

I was able to reproduce the issue as well as solve it with two different ways:
1- Adding "persist security info" attribute to the connection string as Roberto Ruiz Del Valle suggested in the comments section of the documentation: 
string connectionString = "server=localhost;port=3305;database=parking;uid=root;password=pass;persist security info=true";

2- Changing the code to open the connection after create the Database Context object:
. . .
string connectionString = "server=localhost;port=3305;database=parking;uid=root;password=pass;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
  using (Parking context = new Parking(connection, false))
  {
    connection.Open();
    MySqlTransaction transaction = connection.BeginTransaction();

    // Interception/SQL logging
    context.Database.Initialize(false);
    context.Database.Log = (string message) => { Console.WriteLine(message); };

    // Passing an existing transaction to the context
    context.Database.UseTransaction(transaction);

    // DbSet.AddRange
    try
    {
      List<Car> cars = new List<Car>();

      cars.Add(new Car { Manufacturer = "Nissan", Model = "370Z", Year = 2012 });
      cars.Add(new Car { Manufacturer = "Ford", Model = "Mustang", Year = 2013 });
      cars.Add(new Car { Manufacturer = "Chevrolet", Model = "Camaro", Year = 2012 });
      cars.Add(new Car { Manufacturer = "Dodge", Model = "Charger", Year = 2013 });

      context.Cars.AddRange(cars.ToArray());
      context.SaveChanges();
      transaction.Commit();
    }
    catch (Exception)
    {
      transaction.Rollback();
      throw;
    }
  }
}
. . .

Nevertheless we are going to update the documentation.
Thanks for your time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".