ServiceStack ORMLite blobbed columns in MySQL

Question

I am using OrmLite for MySql (from nuget) and have some objects that I'm persisting that will result in the contents being serialized and blobbed. What I'm finding is that the schema for these fields is defaulting to varchar(255), which works for small blobs only. Even relatively small lists are too large for 255 characters.

What is the best approach to ensure the blob tables are sized correctly when using OrmLite?

Example:

public class Foo : IHasId<long>
{
  [AutoIncrement]
  public long Id { get; set; }

  public Dictionary<string, string> TestSize { get; set; }
}

The approach I'm taking now is to annotate with [StringLength(6000)] for each blobbed field. While this works, I'm not sure if there is a better way to ensure enough space.

Below is a full unit test that illustrates the sizing issue:

using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.DesignPatterns.Model;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.MySql;
using System;
using System.Collections.Generic;
using System.Configuration;

namespace OrmLiteTestNamespace
{
    [TestFixture]
    public class BlobItemTest
    {
        [Test]
        public void TableFieldSizeTest()
        {
            var dbFactory = new OrmLiteConnectionFactory(
                  ConfigurationManager.AppSettings["mysqlTestConn"],
                  MySqlDialectProvider.Instance);
            using (var db = dbFactory.OpenDbConnection())
            {
                db.CreateTableIfNotExists<Foo>();
                var foo1 = new Foo()
                    {
                        TestSize = new Dictionary<string, string>()
                    };

                // fill the dictionary with 300 things
                for (var i = 0; i < 300; i++)
                {
                    foo1.TestSize.Add(i.ToString(), Guid.NewGuid().ToString());
                }
                // throws MySql exception "Data too long for column 'TestSize' at row 1"
                db.Insert(foo1);

            }
        }

    }
    public class Foo : IHasId<long>
    {
        [AutoIncrement]
        public long Id { get; set; }

        public Dictionary<string, string> TestSize { get; set; }
    } 
}

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

The Varchar datatype is a variable sized datatype whose space is only determined by the the contents of the field, not the size of the column definition. E.g. In MySQL it only takes up the size of the contents + 2 bytes for the length (up to 65535 length).

edited May 23 '17 at 12:28

Community

1
1

answered Oct 12 '13 at 16:25

mythz

141,670
29
246
390

Understood re varchar datatype, the question was meant to be about best approach to set the upper limit of the size, it defaults to varchar(255) for blobbed types with no annotations. Manually sizing seems to be the way to go, but wanted to see if there are better or alternative options. – Steve Oct 13 '13 at 17:45

ServiceStack ORMLite blobbed columns in MySQL

1 Answers1