I'm trying to use dapper with mysql in .net core 1.0. I'm using this mysql connector: https://github.com/bgrainger/MySqlConnector
I know the connector is in alpha but I was wondering if anyone had a similar issue when using it together with dapper.
This is my simple model:
public List<GeneralModel> GetAllLists()
{
try
{
using (DbConnection connection = new MySqlConnection("Server=localhost;Database=lists;Uid=Unnamed;Pwd=lol;"))
{
return connection.Query<GeneralModel>("SELECT * FROM lists.general").ToList();
}
}
catch (Exception)
{
throw;
}
}
And this is the controller:
public IActionResult Index()
{
GeneralModel GenRepo = new GeneralModel();
return View(GenRepo.GetAllLists());
}
When I go to the index page for the first time, it works. If I refresh, I get an "Access denied for user...". I have no idea what could be causing the error. I don't think the problem is in my code.
Edit:
I guess the problem is in the connector, since this also returns the error after a refresh:
List<GeneralModel> lists = new List<GeneralModel>();
using (DbConnection connection = new MySqlConnection("Server=localhost;Database=lists;Uid=Unnamed;Pwd=lol;"))
{
using (DbCommand myCommand = connection.CreateCommand())
{
myCommand.CommandText = "SELECT * FROM lists.general";
connection.Open();
using (DbDataReader myReader = myCommand.ExecuteReader())
{
while (myReader.Read())
{
GeneralModel tmpGen = new GeneralModel();
tmpGen.name = myReader["name"].ToString();
tmpGen.description = myReader["description"].ToString();
tmpGen.language = myReader["language "].ToString();
lists.Add(tmpGen);
}
}
connection.Close();
}
}
return lists;
This bug was caused by MySqlConnector not correctly handling the fast path for a COM_CHANGE_USER
packet.
MySQL Server (versions 5.6 and 5.7) doesn't appear to immediately accept the user's credentials, but always returns an Authentication Method Switch Request Packet. MariaDB (which you are using) does implement the fast path and immediately returns an OK packet.
The connector has now been updated to handle this response and should stop throwing the spurious "Access Denied" exceptions. The fix is in 0.1.0-alpha09.