MySqlException: Data too long for column 'Id' at row 1

.net-core asp.net-core asp.net-core-mvc c# entity-framework-core

Question

My ASP.NET Core 2.2 / Entity Framework core code first model looks like this:

public class Book
{
    [Column("Id", TypeName = "varchar(8)")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string Id { get; set; }

    [Required, StringLength(100, MinimumLength = 1)]
    public string Title { get; set; }
}

My MySQL trigger to create a unique 8 character Id looks like this:

CREATE DEFINER=`root`@`localhost` TRIGGER Books_BEFORE_INSERT BEFORE INSERT ON `Books` FOR EACH ROW
BEGIN
  DECLARE CNT INT DEFAULT 1;
  DECLARE UUID8 VARCHAR(8) DEFAULT null;

  WHILE CNT > 0 DO
    SET UUID8 = UPPER((SELECT LEFT(UUID(), 8)));
    SET CNT = (SELECT count(*) FROM Books WHERE Id = UUID8);

    IF CNT = 0 THEN
      SET new.Id := UUID8;
    END IF;
  END WHILE;
END

I added [StringLength(8, MinimumLength = 8)] to the Id hoping EF would keep the column in character range, but it doesn't work.

If I do a regular INSERT query ("INSERT INTO Books (Title) VALUES ('Book Name');") there's no problem, but when I try adding a record on my razor page (scaffold), I get the error: MySqlException: Data too long for column 'Id' at row 1. I thought with [DatabaseGenerated(DatabaseGeneratedOption.Identity)] I wouldn't have to worry about EF doing anything with the column on insert..?

Why is this not working?

EDIT

I'm using the Pomelo.EntityFrameworkCore.MySql NuGet package.

1
2
12/2/2019 3:05:15 PM

Accepted Answer

Change column type to LONGTEXT

public class Book
{
    [Column("Id", TypeName = "LONGTEXT")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string Id { get; set; }

    [Required, StringLength(100, MinimumLength = 1)]
    public string Title { get; set; }
}

Or run this code in your phpadmin

SET @@global.sql_mode= '';
1
9/5/2019 4:03:32 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow