Bug #88472 MySqlCommand.Prepare stops tinyint(1) from being treated as a .NET bool
Submitted: 14 Nov 2017 3:10 Modified: 1 Jun 2018 22:19
Reporter: John Belge Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:8.0.11 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any

[14 Nov 2017 3:10] John Belge
Description:
When reading the result set of a command executed by MySqlCommand.ExecuteReader, tinyint(1) types in the database are not converted into .NET bool types if MySqlCommand.Prepare is used.

Marked as serious because workarounds are available, but research indicates this could be a transport layer issue, which may lead to data corruption elsewhere.

How to repeat:
Attached you will find an sql script to create a test table.

Attached you will also find a sample C# console program. Open it in Visual Studio 2015. Go to Program.cs and fill in the missing values in the connection string.

Run the program. You should see "Bugged behavior." printed out.

Suggested fix:
I narrowed the problem down to MySqlCommand.Prepare. If, in the sample program, you comment this out, "Correct behavior." will be printed out instead.

Through my own research, I have gone through and debugged the source code of the connector. This is the line that causes the problem: https://github.com/mysql/mysql-connector-net/blob/52821a8a0204d4351b3842ac02e7050f489d6f9b...

When MySqlCommand.Prepare is used, for some reason, length == -1 and therefore, an sbyte type is returned, instead of the code below that which sets the TreatAsBoolean property. Since that property is not set, the field is treated as a .NET sbyte instead of bool.

I suggest you investigate why length == -1 when MySqlCommand.Prepare is used. If that is the correct behavior, then move up the TreatAsBoolean property so it can be set before returning a new MySqlByte.
[14 Nov 2017 3:11] John Belge
Files to help with reproducing the bug.

Attachment: Bug #88472 Repro Files.zip (application/zip, text), 994.67 KiB.

[14 Nov 2017 6:15] Chiranjeevi Battula
Hello John Belge,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2015 with Connector/NET 6.10.4 version.

Thanks,
Chiranjeevi.
[14 Nov 2017 6:15] Chiranjeevi Battula
Screenshot

Attachment: Bug_88472.png (image/png, text), 146.76 KiB.

[7 Dec 2017 21:26] John Belge
Chiranjeevi, can you please ask the developers for a status update? I didn't think it would take a month to fix this. Hope to see something before end of year.
[13 Jan 2018 22:36] John Belge
Hi Chiranjeevi, I reported this S2 Serious, and 2 months is too long for a fix for this. Please give me an update on the status of this report as soon as possible.
[11 Mar 2018 17:32] John Belge
Gotta upgrade to S1, this is taking too long and needs to be expedited.

How can I help move things along here? There have been no updates in months!
[20 Apr 2018 0:37] John Belge
Confirmed NOT FIXED as of connector 8.0.11 GA that released today. )':
[10 May 2018 15:27] JOSE RAMIREZ
Hello John,
we've tried to get in contact with you via email but were unsuccessful. Are you able to provide an alternate email address?

Regards.
[12 May 2018 2:08] John Belge
Jose, please email:
bbent67888@mail.com

Thanks for a response by the way!
[31 May 2018 19:05] Mark Guinness
John, an option is to take a look at an alternative library called MySqlConnector (https://mysql-net.github.io/MySqlConnector/).  There is a known issue regarding Prepare statements (https://github.com/mysql-net/MySqlConnector/issues/397), but I've found the maintainers to be very responsive.  Good luck!
[1 Jun 2018 22:19] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.10.8 release, and here's the changelog entry:

The TreatTinyAsBoolean connection option was ignored when the
MySqlCommand.Prepare() method was called.

Thank you for the bug report.