Can't load records with text fields with a length of more than 19455 characters in Django using pyodbc

Question

I have a Note model like so (this is pulling from a legacy MS SQL Server database so most of these records were not created by Django):

class Note(models.Model):
    id = models.AutoField(primary_key=True, db_column="note_id")
    content = models.TextField(db_column="note_content", blank=True, null=True)
    date_created = models.DateTimeField(db_column="date_created", auto_now_add=True)
    date_modified = models.DateTimeField(db_column="date_modified", null=True, blank=True)
    date_removed = models.DateTimeField(db_column="date_deleted", null=True, blank=True)

Running a .get on some records was returning a DoesNotExist even though they existed in the database.

Turns out this happens for when the length of the content in a MS SQL Server TEXT (as in, CREATE TABLE Foo ( content TEXT null)) field exceeds a certain amount; specifically 19455 characters.

Here's what it looks like in operation:

>>> note = Note.objects.get(pk=1)
>>> note.content = "x" * 19455
>>> note.save()
>>> note = Note.objects.get(pk=1)
>>> note.content = "x" * 19456
>>> note.save()
>>> note = Note.objects.get(pk=1)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/path/to/django/db/models/query.py", line 366, in get
    % self.model._meta.object_name)
DoesNotExist: Note matching query does not exist.

I'm using FreeTDS, and have text size set to 2147483647 which appears to be the upper limit for the version of MS SQL Server I'm using.

According to this other question on truncation, you should prepend the instruction SET TEXTSIZE n where n is a value in bytes, and this will resolve the issue of data being truncated. So I wondered if that was happening in my case, and if it would fix it.

So, I went ahead and wrote some code that uses just the cursor and the SET TEXTSIZE command.

First, let's take a look at what should be in the record:

print "Length: %d; Last 40 characters: %s" % (len(note.content), note.content[-40:]) Length: 19456; Last 40 characters: rVEF1cCJeRaTtcdkXMqqQUxEVLZapMGVGSxMfJ2T

And now we loop through. Each time we increment the TEXTSIZE setting and displaying the record if one is fond. We also display the length and last 10 characters of the returned record field.

>>> for xx in xrange(19450, 19460):
...     cursor = connection.cursor()
...     try:
...         qrys = 'SET TEXTSIZE %d SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1' % xx
...         print qrys
...         qry = cursor.execute(qrys)
...         record = qry.fetchone()
...         if record:
...             record_id, record_content = record
...             print record_id, len(record_content), record_content[-10:]
...         else:
...             print "No record found after TEXTSIZE set to %d" % xx
...             break
...     except Exception, inst:
...         print "Error: %s (%s)" % (inst, type(inst))
...         break
...     finally:
...         cursor.close()
... 
SET TEXTSIZE 19450 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19450 VLZapMGVGS
SET TEXTSIZE 19451 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19451 LZapMGVGSx
SET TEXTSIZE 19452 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19452 ZapMGVGSxM
SET TEXTSIZE 19453 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19453 apMGVGSxMf
SET TEXTSIZE 19454 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19454 pMGVGSxMfJ
SET TEXTSIZE 19455 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
1 19455 MGVGSxMfJ2
SET TEXTSIZE 19456 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1
No record found after TEXTSIZE set to 19456
>>> 

So as soon as we try to retrieve a record with TEXTSIZE set to a number larger than 19456, no records are returned. And you'll notice that the last 10 characters of the string match up to the string above minus the characters it missed for being too short. For example, for the last record found, the final 10 characters are MGVGSxMfJ2. It's missing the T from the real record because the TEXTSIZE of 19455 is one less than the length of the field in question.

So now of course I am wondering, what is going on??? Is there any further troubleshooting I can do to determine if this is a problem with django-pyodbc, pyodbc, or FreeTDS? It possibly could also be SQL Server, but running SET TEXTSIZE 19456 SELECT [Notes].[note_id], [Notes].[note_content] FROM [Notes] WHERE [Notes].[note_id] = 1 directly in the Server Management Studio seems to work correctly and returns the right number of characters.

Also note that saving works:

>>> note.content = (note.content * 10)[:65536] # 65536 is max length allowed for TEXT, apparently
>>> len(note.content)
65536
>>> note.save()
>>> cursor = connection.cursor()
>>> qry = cursor.execute( 'SELECT [Notes].[note_id], DATALENGTH([Notes].[note_content]) FROM [Notes] WHERE [Notes].[note_id] = 1')
>>> record = qry.fetchone()
>>> record
(1, 65536)
>>>