Dapper fails on MySql nullable bool

[RobRolls]

Dapper is throwing an error when I try to use a nullable bool with MySql.

Error parsing column 1 (IsBold=0 - SByte)

It only breaks when a statement follows the null value insert statement.

An additional note, it works fine if I manually change the size of IsBold from tinyint(1) (the default created by BOOLEAN) to 2 or greater.

        [Fact]
        public void TestSuccess()
        {
            using (var conn = GetMySqlConnection(true, true, true))
            {
                try { conn.Execute("drop table boolTest_Test"); } catch { }
                conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
                conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");
                conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");

                var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

                Assert.True(rows[1].IsBold);
                Assert.Null(rows[2].IsBold);
            }
        }

        [Fact]
        public void TestFail()
        {
            using (var conn = GetMySqlConnection(true, true, true))
            {
                try { conn.Execute("drop table boolTest_Test"); } catch { }
                conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
                conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");
                conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");

                var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

                Assert.True(rows[1].IsBold);
                Assert.Null(rows[2].IsBold);
            }
        }

        class BoolTest
        {
            public int Id { get; set; }
            public bool? IsBold { get; set; }
        }

[mgravell]

OK; I will have a look this morning and see if I can spot the problem. It
seems especially odd that this seems to be dependent on row order, but ...
well, I'll see what I can find :)
On 8 Jul 2016 1:22 a.m., "RobRolls" [email protected] wrote:

Dapper is throwing an error when I try to use a nullable bool with MySql.

Error parsing column 1 (IsBold=0 - SByte)

It only breaks when a statement follows the null value insert statement.

An additional note, it works fine if I manually change the size of IsBold
from tinyint(1) (the default created by BOOLEAN) to 2 or greater.

    [Fact]
    public void TestSuccess()
    {
        using (var conn = GetMySqlConnection(true, true, true))
        {
            try { conn.Execute("drop table boolTest_Test"); } catch { }
            conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");

            var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

            Assert.True(rows[1].IsBold);
            Assert.Null(rows[2].IsBold);
        }
    }

    [Fact]
    public void TestFail()
    {
        using (var conn = GetMySqlConnection(true, true, true))
        {
            try { conn.Execute("drop table boolTest_Test"); } catch { }
            conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");

            var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

            Assert.True(rows[1].IsBold);
            Assert.Null(rows[2].IsBold);
        }
    }

    class BoolTest
    {
        public int Id { get; set; }
        public bool? IsBold { get; set; }
    }

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#552, or mute the
thread
https://github.com/notifications/unsubscribe/AABDsNptrcnHUUi41tFNMeW8V5Pmau0Kks5qTZhJgaJpZM4JHn0N
.

[mgravell]

It looks like it should work fine. I can look, but I will need to install
some extra pieces first. Have you tried with the most recent version of
dapper (with pre-release enabled)?

Install-Package Dapper -Pre

Marc
On 8 Jul 2016 7:46 a.m., "Marc Gravell" [email protected] wrote:

OK; I will have a look this morning and see if I can spot the problem. It
seems especially odd that this seems to be dependent on row order, but ...
well, I'll see what I can find :)
On 8 Jul 2016 1:22 a.m., "RobRolls" [email protected] wrote:

Dapper is throwing an error when I try to use a nullable bool with MySql.

Error parsing column 1 (IsBold=0 - SByte)

It only breaks when a statement follows the null value insert statement.

An additional note, it works fine if I manually change the size of IsBold
from tinyint(1) (the default created by BOOLEAN) to 2 or greater.

    [Fact]
    public void TestSuccess()
    {
        using (var conn = GetMySqlConnection(true, true, true))
        {
            try { conn.Execute("drop table boolTest_Test"); } catch { }
            conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");

            var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

            Assert.True(rows[1].IsBold);
            Assert.Null(rows[2].IsBold);
        }
    }

    [Fact]
    public void TestFail()
    {
        using (var conn = GetMySqlConnection(true, true, true))
        {
            try { conn.Execute("drop table boolTest_Test"); } catch { }
            conn.Execute("create table boolTest_Test (Id int not null, IsBold BOOLEAN null );");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (2,null);");
            conn.Execute("insert boolTest_Test (Id, IsBold) values (1,1);");

            var rows = conn.Query<BoolTest>("select * from boolTest_Test").ToDictionary(x => x.Id);

            Assert.True(rows[1].IsBold);
            Assert.Null(rows[2].IsBold);
        }
    }

    class BoolTest
    {
        public int Id { get; set; }
        public bool? IsBold { get; set; }
    }

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#552, or mute
the thread
https://github.com/notifications/unsubscribe/AABDsNptrcnHUUi41tFNMeW8V5Pmau0Kks5qTZhJgaJpZM4JHn0N
.

[RobRolls]

Marc,

I agree, this stuck me as odd as well. I have tried it with the most current version. Currently have dapper.1.50.0-rc3 installed.

I can provide a stripped down solution if it helps.

Thanks for your help!

[mgravell]

The example you have should be fine; my blocker is simply that I've just
flattened all my OSes (to get a clean VS install), and don't have mysql
yet. SQL Server doesn't have this data type. Will look.

On Fri, 8 Jul 2016 09:06 RobRolls, [email protected] wrote:

Marc,

I agree, this stuck me as odd as well. I have tried it with the most
current version. Currently have dapper.1.50.0-rc3 installed.

I can provide a stripped down solution if it helps.

Thanks for your help!

—
You are receiving this because you commented.

Reply to this email directly, view it on GitHub
#552 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AABDsEFomsbwfWkSF_BkwTV-ol0giRCnks5qTgTygaJpZM4JHn0N
.

[unkusr007]

Hi,

I have exactly the same issue, but I use byte? instead of bool.
I have a lot a row with null in this column, and when the first line with 0 comes, Dapper throw the same Exception.
I put 0 in every row, and there is no exception.
It seems that the problem happens when the previous row is null.

Thanks

[mgravell]

@bastiflew can you confirm the exact error message when it happens to you? In particular, the bit in brackets

[mgravell]

(the good news is that I have MySQL up and running on my new OS build, so I can actually investigate this now)

[unkusr007]

@mgravell the message : "Error parsing column 10 (svc_desynchro=0 - SByte)"}

[mgravell]

Well shoot, the problem here is that MySQL lies initially, and changes its mind about the schema in the middle of iterating it. I cannot stress just how horribly broken this is - an epic bug in the mysql data provider. It is not meant to do this. I'll have to see whether dapper can work around it somehow. But to illustrate with what the reader reports 1) initially, 2) after the first row (null), and 3) after the second row (non-null):

> reader.GetFieldType(0).FullName + " | " + reader.GetFieldType(1).FullName
"System.Int32 | System.Boolean"
> reader.Read()
true
> reader.GetFieldType(0).FullName + " | " + reader.GetFieldType(1).FullName
"System.Int32 | System.Boolean"
> reader[0] + " | " + reader[1]
"1 | "
> reader.Read()
true
> reader.GetFieldType(0).FullName + " | " + reader.GetFieldType(1).FullName
"System.Int32 | System.SByte"
> reader[0] + " | " + reader[1]
"2 | 0"

The thing to call out there is where the schema changes from "System.Int32 | System.Boolean" to "System.Int32 | System.SByte". If it had reported SByte initially, dapper would have used an alternative code branch (specifically, FlexibleConvertBoxedFromHeadOfStack instead of a simple OpCodes.Unbox_Any). The problem is that the decisions dapper makes at IL emit time are based on the BOF schema, i.e. before Read() has been called.

[mgravell]

I've had a suggestion (from someone else equally frustrated by the mysql connector) to try using the mysql connector of devart instead; apparently it is far less broken! I cannot vouch for this myself, as I am not a mysql user (except for debugging things): https://www.devart.com/dotconnect/mysql/

[mgravell]

I've logged this as a bug against the mysql connector: http://bugs.mysql.com/bug.php?id=82292

[rykr]

I've forwarded this to the correct person. We'll look at this right away. This should be happening.

[unkusr007]

Thanks for your efforts. I will change from MySQL .NET to DevArt Express until this issue is fixed.

[bgrainger]

Shameless plug: You can also avoid this error by switching to https://github.com/mysql-net/MySqlConnector. When I was developing its code, I ran across MySQL bug 78917 which seems to be the same underlying issue. (But just to be sure, I added a new test to verify the behaviour doesn't repro.)

[mgravell]

@bgrainger thanks; fully agree that this is a duplicate of 78917 - good catch. Will update on my bug. The existing one deserves to be primary.

[xavierpena]

@mgravell I understand that the error is coming from the MySQL connector, but are there any news regarding this error? Any workaround whatsoever?

I have tried this StackOverflow solution (changing from bool? to Byte?, which is the tinyint equivalent in c#) but it hasn't worked for me.

I tried @bgrainger solution, but I had problems using it (see issue here), plus it lacked the Compress=True; parameter compatibility, so I made a rollback.

Edit: added MySqlConnector issue link

[344303947]

connectionString ：Treat Tiny As Boolean=false;

[bgrainger]

Treat Tiny As Boolean=false

I would advise against this, because now any BOOL columns created in MySQL will get returned to .NET as byte values (then you must perform a != 0 check if you want an actual bool).

Instead, my advice remains to switch to https://github.com/mysql-net/MySqlConnector (NuGet).

[xavierpena]

@bgrainger I can confirm. I've been using MySqlConnector since I had the problem, and it works like a charm.

In the beginning I was wary because of Compress=True; not working in that particular connector, but I've been using it in multiple projects and overall the experience has been great.

[NickCraver]

Closing this out since it's a provider issue, and changing providers resolves it.

[bgrainger]

For anyone affected by this who wants to stick with MySql.Data, they just announced that this will be fixed in 6.10.8.

[bgrainger]

The fix is also in 8.0.12, according to the release notes.