Exception With DataReader already open when using a Task<int> to read from MySql

Question

I have this code in a method:

Task<int> linksCount = Task<int>.Factory.StartNew(() => { return DatabaseIn.GetUrlsCount(); });
Task<int> imagesCount = Task<int>.Factory.StartNew(() => { return DatabaseIn.GetImagesCount(); });

Later the linksCount and imagesCount.Result is displayed. However, when I run this code the first time. I get the following exception:

There is already an open DataReader associated with this Connection which must be closed first.

The code where the exception is thrown is:

MySqlCommand comm = null;
string cmdString = "SELECT COUNT(*) FROM damocles.imagestoassess;";

comm = new MySqlCommand(cmdString, conn);

if (comm.ExecuteScalar() == null)
{
    comm.Dispose();
    conn.Close();
    return 0;
}

int res = Convert.ToInt32(comm.ExecuteScalar());

comm.Dispose();
conn.Close();
return res;

The exception is thrown on the first call to ExecuteScalar. If I enclose the code in a try..catch clause the code continues but it throws another error in another part of the code.

Question:

Why does it throw the exception? It doesn't throw any exception when run synchronously.
How do I fix the code to work Asynchronously?

Thanks.

Does this answer your question? Exception: There is already an open DataReader associated with this Connection which must be closed first — devNull
– devNull, Commented May 9, 2020 at 14:42
It would appear you are using "async" and "parallel" interchangeably. You are launching two threads (in a wrong way too) that try to use the same connection object. In an async scenario, you would stay on the same thread, and the second query would start only after the first one completes (which would be perfectly async, but not in parallel). Connection objects do not like several async commands being in flight against them at the same time, this is not specific to mysql. If you absolutely want to run two queries in parallel, give each a separate connection object. — GSerg
– GSerg, Commented May 9, 2020 at 14:49
As a side note, you call comm.ExecuteScalar() twice, so you hit the database twice. You could optimize this by storing the result of the first call in a local variable. — Theodor Zoulias
– Theodor Zoulias, Commented May 9, 2020 at 14:49
Also please see stackoverflow.com/a/40065501/11683 for why async it's not going to work anyway even if you were to properly implement Task<int> GetUrlsCountAsync() and do int linksCount = await GetUrlsCountAsync(). — GSerg
– GSerg, Commented May 9, 2020 at 15:11

Bradley Grainger · Accepted Answer · 2020-05-09 17:57:32Z

1

A MySqlConnection can only be used by one thread at a time. Your exception is happening because both Tasks are using the same conn object. For more details, see https://mysqlconnector.net/troubleshooting/connection-reuse/.

Your code will work if you create and use a new MySqlConnection inside each Task.

Additionally, it's a longstanding bug that async operations in MySql.Data aren't actually asynchronous. You'll need to switch to MySqlConnector to get asynchronous I/O for MySQL operations.

answered May 9, 2020 at 17:57

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exception With DataReader already open when using a Task<int> to read from MySql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related