Correct attribute for MySQL MEDIUMTEXT or LONGTEXT

ServiceStack.OrmLite MySql
1

I need a string column that is more than 64k.

As far as I know, I can use

class MyTable
{
  [StringLength(int.MaxValue)]
  public string MyField {get; set;}
}

which in SQLServer creates an “nvarchar(max)”, which is about 1 billion characters.

However, in MySQL, [StringLength(int.MaxValue)] creates a TEXT column, which is only 64k.

I tried setting the values to 65536,

  [StringLength(65536)]
  public string MyField {get; set;}

but get the following error when it tries to create the table

Column length too big for column ‘Data’ (max = 21845); use BLOB or TEXT instead

So what is it referring to when it says use BLOB or TEXT (or is it just a MySQL error) ? Or how do I annotate for a MEDIUMTEXT or LONGTEXT field?

edit:

I’ve discovered I can use

[CustomField("MEDIUMTEXT")]
public string MyField {get; set;}

But this obviously isn’t compatible across database and fails when creating tables in SQL Server. Is there a generic way?

2

It’s an error returned from MySql server. Note MySql has a maximum row limit of 65,535 bytes.

What’s wrong with using TEXT?

3

What’s wrong with using TEXT?

Too small. Needs to be more than 64k.

Note MySql has a maximum row limit of 65,535 bytes.

This excludes BLOB/TEXT/MEDIUMTEXT/LONGTEXT etc. They only take up to 8 bytes of the row as actual data is stored elsewhere.

4

ok thanks, I’ve changed the Max String definition for MySql to use LONGTEXT in this commit. This change is available from v4.0.53 that’s now on MyGet.

There’s also the [Text] attribute to specify to use TEXT column and to use MEDIUMTEXT you’ll need to use the [CustomField] attribute to specify a custom column definition, e.g:

class MyTable
{
    [StringLength(int.MaxValue)] //= LONGTEXT
    public string MaxText {get; set;}

    [Text] //= TEXT
    public string Text { get; set; }

    [CustomField("MEDIUMTEXT")] //= MEDIUMTEXT
    public string MediumText { get; set; }
}
5

Thank you. That looks like it should solve the issue.