ExecuteReaderAsync leads to a "No columns were selected" error

[ibuchan72390]

I've been attempting to implement asynchronous execution methods in my repository layer with a custom data write using the IDataReader to map some reference values. This entire piece relies on the ExecuteReaderAsync functionality so that I can access the IDataReader for the post process writing. All of my other methods work fine with the synchronous version of this execution; however, the asynchronous version seems to raise the "No columns were selected" error as seen in a previous issue. I am currently using MySQL 5.7.17 and the latest stable release of MySQL.Data.

It appears a similar issue was occurring with QueryAsync that was fixed in the 1.50.5 release; however, ExecuteReaderAsync is now raising a similar issue when using the exact same query as the functioning ExecuteReader method. I have attempted to use the MyGet prerelease versions as well under the hope that they may have fixed the issue and I'm experiencing the exact same problem on all of them. I have a public repository demonstrating my issue if necessary.

Is there any chance a fix for this was implemented on QueryAsync from the previous issue that did not carry over to the ExecuteReaderAsync method?

[bgrainger]

@ibuchan72390 I suspect this might be a MySql.Data bug. If I switch your project to use MySqlConnector then it works as expected.

I'm not entirely sure why MySql.Data would be broken here, since it doesn't actually implement asynchronous operations. If you want to keep using MySql.Data you may wish to file this as an issue at bugs.mysql.com.

(When switching to MySqlConnector, I also had to switch to conn.Close since CloseAsync isn't implemented yet: mysql-net/MySqlConnector#467. However, there's not much point in asynchronously closing since it just returns the connection to the connection pool, which is synchronous. Also, it's unnecessary since the using statement will also return the connection to the pool.)

[bgrainger]

Your test also passes if I revert MySql.Data to 6.9.11 (both 6.10.7 and 8.0.12 fail), which strongly indicates a regression in MySql.Data.

[bgrainger]

If you add reader.Read(); to your sample code, you'll get MySql.Data.MySqlClient.MySqlException : Invalid attempt to Read when reader is closed. That makes me think this is a duplicate of #758.

The underlying problem appears to be MySQL bug 89159: "MySqlDataReader cannot outlive parent MySqlCommand since 6.10".

Dapper disposes the MySqlCommand that creates the MySqlDataReader before returning the reader: https://github.com/StackExchange/Dapper/blob/master/Dapper/SqlMapper.Async.cs?utf8=%E2%9C%93#L1150

In MySql.Data 6.10.x and later, this puts the MySqlDataReader into a closed state, causing these issues.

IMO this is a MySql.Data bug (indeed, one that's been open and known since January) since every other ADO.NET library works just fine with Dapper. I would recommend switching to MySqlConnector to get a fix for this (and many other known issues).

[bgrainger]

SO answer demonstrating that Dapper's usage works fine with other connectors: https://stackoverflow.com/a/47806069

[ibuchan72390]

Sorry for the response delay. Ran into this issue just before I had to head home on vacation for a week.

Thank you so much for the detailed response and analysis. I've been banging my head against a wall for about a day until I finally wrote this test to prove my concern. I was feeling it may have been an issue with MySQL.Data simply because of some of the issues I've seen with their somewhat poorly supported library in the past.

I was unaware of an alternate MySQL Connector for .NET Core. Honestly, I had been getting rather frustrated with the support for MySQL.Data. Their release cycles are rather slow and I've had some difficulties getting it to work in the AWS Labmda environment particularly because their releases are so few and far between. I will gladly check that out the alternate connector, thank you so much for the suggestion! Hopefully this will alleviate more issues than just the asynchronous support!

[bgrainger]

@ibuchan72390 There are a couple of known issues using MySqlConnector with AWS Lambda but my understanding is that in general it works great. Please let me know if you run into any problems, though!

[ibuchan72390]

All my async functionality tests appear to be up and running green lights with the simple swap of a library and adjusting a few lines. Thankfully neither of those Lambda issues should be an issue with my current implementation.

This entire fix could not have been easier! Cheers @bgrainger! Thanks a ton!

[smbecker]

I'm guessing that this will be an issue with Sqlite as well, correct? And it doesn't seem that the ASP.Net team is willing to change that behavior.

[bgrainger]

Based on the linked issue, I'm guessing it's very likely to also be a problem with Sqlite. (However, it doesn't support async I/O either, so I think there's very little benefit in using ExecuteReaderAsync with Sqlite.)

[smbecker]

But it is an issue if you are supporting multiple providers. Looking at the code, I see that it's not an issue for ExecuteReader because it returns an instance of WrappedReader that handles disposing the reader and command together. Out of curiosity, why doesn't ExecuteReaderAsync use that class as well?