Pomelo.entityframeworkcore.mysql: MySQL tinyint(1) value 0 still being deserialized as true, even with latest versions, packages and TreatTinyAsBoolean=true;

[richardu]

Hi,

Values of zero from a MySQL field type tinyint(1) are still being deserialized to true, despite upgrading to all of the latest versions (see below) and also using TreatTinyAsBoolean=true in the connection string (and it appearing to be fixed for everybody else!)

I used code-first from within VS and have the following:

XXXModelSnapshot.cs:
b.Property<bool>("Enabled").HasColumnType("tinyint(1)");
(correct datatype for the latest version of mysql)

ConnectionString:
TreatTinyAsBoolean=true;
(the default but I've specified it explicitly anyway)

MySQL version: 8.0.18
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 3.1.0
Microsoft.AspNetCore.App version: 3.1.1

I was previously using MySQL 5.7.27, .NET Core 2.2 and Pomelo 2.2.6 but i couldn't it to work so I upgraded to the latest versions.

I've also read through every single related issue, post and discussion!

I've also cloned this repo to try to debug it myself, but I find out where the conversion takes place. In which class / file does it happen?

Thanks,
Richard

[lauxjpn]

It is unlikely, that this is an issue with Pomelo and tinyint(1).

Please post the complete model definition for the entity in question and the CREATE TABLE statement of the corresponding table.

[richardu]

Hi @lauxjpn ,

Thanks for the quick response!

Entity model:

public class Exchange : SW.Entity, SW.IAggregateRoot
{
        #region Public Properties
        public string Name { get; private set; }
        public string Code { get; private set; }
        public decimal Fee { get; internal set; }
        public bool Enabled { get; internal set; }
        #endregion

        #region EF Navigation Properties
        public ICollection<Market> Markets { get; private set; }
        #endregion

        public Exchange()
        {
        }
}

CREATE TABLE:

`CREATE TABLE `exchanges` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Token` char(36) NOT NULL,
  `Name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Code` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '0',
  `Fee` decimal(7,4) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_exchanges_Name` (`Name`),
  UNIQUE KEY `IX_exchanges_Token` (`Token`),
  UNIQUE KEY `IX_exchanges_Code` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Thanks,
Richard

[lauxjpn]

Values of zero from a MySQL field type tinyint(1) are still being deserialized to true

@richardu Actually, I can't reproduce your issue with the following code, which runs as expected:

```c#
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
public class Exchange
{
public int ExchangeId { get; set; }
public string Name { get; set; }
public bool Enabled { get; set; }
}

public class Context : DbContext
{
    public virtual DbSet<Exchange> Exchanges { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=Issue1010",
                b => b.ServerVersion(new ServerVersion("8.0.18-mysql")))
            .UseLoggerFactory(LoggerFactory.Create(b => b
                .AddConsole()
                .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Exchange>(entity =>
        {
            entity.Property(e => e.Enabled)
                .HasDefaultValue(false);
        });
    }
}

internal class Program
{
    private static void Main()
    {
        using (var context = new Context())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Exchanges.AddRange(
                new Exchange { Name = "NYSE", Enabled = false },
                new Exchange { Name = "IEX", Enabled = true });

            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var result = context.Exchanges
                .OrderBy(e => e.ExchangeId)
                .ToList();

            Debug.Assert(result.Count == 2);

            Debug.Assert(result[0].Name == "NYSE");
            Debug.Assert(result[0].Enabled == false);

            Debug.Assert(result[1].Name == "IEX");
            Debug.Assert(result[1].Enabled == true);
        }
    }
}

}

It generates the following `CREATE TABLE` SQL, that is similar to the one you posted:

```sql
CREATE TABLE `Exchanges` (
    `ExchangeId` int NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NULL,
    `Enabled` tinyint(1) NOT NULL DEFAULT FALSE,
    CONSTRAINT `PK_Exchanges` PRIMARY KEY (`ExchangeId`)
);

I propose you run the code on your end to ensure, that it works as expected.

[richardu]

Hi @lauxjpn,

So:

• I modified my migration to remove the default - it worked
• I then ran your code - it worked
• I then reverted my migration and re-tested: my original code does work. I guess it must have been fixed by the upgrade.

I assumed it hasn't been fixed because I'm actually having the same problem with another 'Enabled' property on entity 'Market' - this one doesn't have a default.

I now have one test where the value is correctly retrieved as false and one where it's retrieved as true (see screenshots):

Scratching my head...!

Cheers,
Richard

[lauxjpn]

Please ensure, that the test uses the correct database and that ConvertAll() does not alter the property values in any way.

Then post the CREATE TABLE statement generated by MySqlWorkbench for the Market table, the model class and the model definition (FluentAPI), so we can take a look at it.

Of course if you have a self contained test (i.e. by altering the console program I posted above so it uses the Market class) that reproduces the issue, than that would be best.

---

Getting always true values from a non-nullable System.Boolean smells a lot like #989. But that would only be the case if it would have a default value of 1 defined for the column.

[mguinness]

@lauxjpn Considering the ongoing issues storing boolean values, do you have any comments on using BIT(1) versus TINYINT(1) type? Using the former is covered in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/917 but there doesn't appear to be any advantage. I'm not aware of any guidance from either MySQL or MariaDB regarding this matter although the consensus appears to lean towards tinyint(1) - maybe that's due to having the bool/boolean synonyms and nonzero values evaluate to true.

[lauxjpn]

@mguinness I would always use TINYINT(1) for saving bools. It is intended for this purpose which is hinted by the compatibility synonym BOOL, that is mapped to a TINYINT(1).
Also, as @bgrainger can attest to, there are many quirks surrounding the usage of BIT(1) for boolean values when it comes to the actual protocol on the wire.

These are some of the reasons, why Pomelo made the switch in 3.0.0, that MySqlConnector had already made, to use TreatTinyAsBoolean=True as the default.

I did some quick benchmarking on the two store types and they both took 28 seconds on my machine when running the same simple query 1.000.000.000 times.

So if you are deciding what type to use to represent a Boolean for a new project, I would choose TINYINT(1). If you have a legacy project that is still using BIT(1), than there should be no need to change the types, because we will continue to support them as well.

[lauxjpn]

Please ensure, that the test uses the correct database and that ConvertAll() does not alter the property values in any way.

Then post the CREATE TABLE statement generated by MySqlWorkbench for the Market table, the model class and the model definition (FluentAPI), so we can take a look at it.

Of course if you have a self contained test (i.e. by altering the console program I posted above so it uses the Market class) that reproduces the issue, than that would be best.

Getting always true values from a non-nullable System.Boolean smells a lot like #989. But that would only be the case if it _would_ have a default value of 1 defined for the column.

@richardu Any update on this? Otherwise we will close this one.

[richardu]

Hi @lauxjpn,

Apologies for the delay in responding.

I think the problem was caused by stale copies of the Markets (which are owned entities) being cached by EF from when I originally retrieved the parent Exchange (so they retained the original value of the Enabled flag.)

Detaching everything as follows:

_context.Entry(exchange).State = EntityState.Detached;
            foreach (var m in exchange.Markets)
                _context.Entry(m).State = EntityState.Detached;

Then fetching from the DB again returned the correct data.

Thanks for helping me get to the bottom of it!
Richard