3

I am saving a raw email in a TEXT column in MySQL with Ruby on Rails. It keeps getting truncated to 65535 characters, does anyone know what causes this?

MySQL is running with max_allowed_packet=64M

Using InnoDB for the storage engine.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
Greg
  • 765
  • 2
  • 9
  • 17

4 Answers4

13

It gets truncated to that length because... well, that's what will fit in a TEXT column.

You need MEDIUMTEXT or LONGTEXT if you want to store more than that.

Michael Madsen
  • 54,231
  • 8
  • 72
  • 83
  • Can you quote what you read on that page? I cant see where it gives this as the limit... – Greg Feb 15 '10 at 15:24
  • Under "Storage Requirements for String Types in MyISAM", there's a table stating that BLOB and TEXT require L + 2 bytes of storage space, where L "represents the actual length in bytes of a given string value", and, according to the table, is less than 2^16 (65,536). Consequently, you can't store more than that. MEDIUMTEXT will give you 16,777,215 bytes of storage, while LONGTEXT gives you just short of 4.3 billion bytes. – Michael Madsen Feb 15 '10 at 15:30
  • Note that even though it says MyISAM, the same applies to InnoDB, as the two storage engines don't have any difference as far as BLOB/TEXT storage limits are concerned. – Michael Madsen Feb 15 '10 at 15:32
  • I'm getting my string truncated to 27700 characters. I'm guessing this is because I'm using UTF-8. Even with LONGTEXT (I've checked the DB, that's the type), it still gets truncated. Any ideas? – Ponny Feb 26 '12 at 11:37
  • @Ponny: If LONGTEXT won't take your string, then it sounds like the issue is somewhere else. Ask a new question and give details; you'll have more luck that way. – Michael Madsen Feb 26 '12 at 11:42
  • Solved it. Had a better look at my data - there was a bad character that I'm guessing was causing my string to be null terminated (or something). – Ponny Feb 26 '12 at 11:58
2

Ruby on Rails didn't truncate, MySQL did.

The TEXT type is limited to 2^16 - 1 = 65535 characters, see the documentation.

instanceof me
  • 38,520
  • 3
  • 31
  • 40
1

65535 is one of those "magic numbers" - it's 2^16 - 1. That's just what the maximum limit is for a TEXT column in MySQL.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
1

I'm not a Ruby expert, but the number 65535 caught my attention -- that's 16 bits (minus 1, which typically is special). You're probably running up against a wall of the size limit of the type you're using.

hackerhasid
  • 11,699
  • 10
  • 42
  • 60