Dapper fails on MySql nullable bool

Created on 8 Jul 2016 · 22Comments · Source: StackExchange/Dapper

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; }
        }

Source

RobRolls

Most helpful comment

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.)

bgrainger on 20 Jul 2016

👍4

All 22 comments

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
https://github.com/StackExchange/dapper-dot-net/issues/552, or mute the
thread
https://github.com/notifications/unsubscribe/AABDsNptrcnHUUi41tFNMeW8V5Pmau0Kks5qTZhJgaJpZM4JHn0N
.

mgravell on 8 Jul 2016

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" marc.[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
https://github.com/StackExchange/dapper-dot-net/issues/552, or mute
the thread
https://github.com/notifications/unsubscribe/AABDsNptrcnHUUi41tFNMeW8V5Pmau0Kks5qTZhJgaJpZM4JHn0N
.

mgravell on 8 Jul 2016

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!

RobRolls on 8 Jul 2016

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
https://github.com/StackExchange/dapper-dot-net/issues/552#issuecomment-231300093,
or mute the thread
https://github.com/notifications/unsubscribe/AABDsEFomsbwfWkSF_BkwTV-ol0giRCnks5qTgTygaJpZM4JHn0N
.

mgravell on 8 Jul 2016

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

bastiflew on 20 Jul 2016

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

mgravell on 20 Jul 2016

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

mgravell on 20 Jul 2016

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

bastiflew on 20 Jul 2016

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 on 20 Jul 2016

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 on 20 Jul 2016

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

mgravell on 20 Jul 2016

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

rykr on 20 Jul 2016

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

bastiflew on 20 Jul 2016

bgrainger on 20 Jul 2016

👍4

@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.

mgravell on 20 Jul 2016

@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

xavierpena on 6 Sep 2016

connectionString ：Treat Tiny As Boolean=false;

344303947 on 10 Nov 2017

👍1

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).

bgrainger on 10 Nov 2017

👍1

@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.

xavierpena on 11 Nov 2017

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