:limit text mysql

Rails
1

I have a rails 3.rc app that I’m developing. I have a text entry in one
my models that can sometimes be quite big. I tried setting :limit =>
4294967296 on the text column in my migration file but this doesn’t seem
to have any effect. The column is till created as a TEXT column rather
than MEDIUMTEXT or LONGTEXT.

Does anyone have any idea why this might be happening? Is there a better
way of ensuring that
an entry won’t default to TEXT in a mysql database?

2

Conor,
I think this is relevant - I had similar issue when I was saving reports
to
a db field. To work around, I changed the datatype of the field to
:longtext. You can create or change the field to :longtext in your
migration
and everyone should be happy.

3

David K. wrote:

Conor,
I think this is relevant - I had similar issue when I was saving reports
to
a db field. To work around, I changed the datatype of the field to
:longtext. You can create or change the field to :longtext in your
migration
and everyone should be happy.

Hi David,

Thanks a million for your reply. I will give that a go. I had seen that
that is possible but I was a bit worried that it would mean that my
migrations wouldn’t be database agnostic

4

Conor N. wrote:

David K. wrote:

Conor,
I think this is relevant - I had similar issue when I was saving reports
to
a db field. To work around, I changed the datatype of the field to
:longtext. You can create or change the field to :longtext in your
migration
and everyone should be happy.

Hi David,

Thanks a million for your reply. I will give that a go. I had seen that
that is possible but I was a bit worried that it would mean that my
migrations wouldn’t be database agnostic

Is there a reason that you’re not using VARCHAR? That should hold the
data (in less space, I believe!) and keep your migrations DB-agnostic.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

5

Thanks a million for your reply. I will give that a go. I had seen that
that is possible but I was a bit worried that it would mean that my
migrations wouldn’t be database agnostic

Is there a reason that you’re not using VARCHAR? That should hold the
data (in less space, I believe!) and keep your migrations DB-agnostic.

Doesn’t mysql limit varchar to 65kb or so?

http://dev.mysql.com/doc/refman/5.1/en/char.html

Not very pretty, but you could create it as :text and then…

if connection.adapter_name == 'MySQL'
  execute("ALTER TABLE tablename MODIFY fieldname MEDIUMTEXT")
end

That keeps it a bit more db-agnostic.

-philip

6

If you really need to store big stuff, maybe you could just use the
db to store a filename? It will add overhead to make sure files get
deleted when the record goes away, but it could work.

Or maybe use a document-oriented database?

-Dave

–
Specialization is for insects. -RAH | Have Pun, Will Babble! -me
Programming Blog: http://codosaur.us | Work: http://davearonson.com
Leadership Blog: http://dare2xl.com | Play: http://davearonson.net

7

Doesn’t mysql limit varchar to 65kb or so?

From mysql doc:
"Values in VARCHAR columns are variable-length strings. The length can
be specified as 1 to 255 before MySQL 4.0.2, 0 to 255 as of MySQL
4.0.2, and 0 to 65,535 as of MySQL 5.0.3. (The maximum actual length
of a VARCHAR in MySQL 5.0 is determined by the maximum row size and
the character set you use. The maximum effective length is 65,532
bytes.) "

That was the problem I was trying to get over - I found the :LONGTEXT
workaround at 10 at night after 14 hour day so very well could be a
better solution, but I do remember specifically that I was being cut
off at ~65k chars. I don’t recall trying varchar… but looks like at
least in my case would not have worked.

8

maybe you could just use the
db to store a filename

From experience I would second this approach… in fact that is what I
did on the app I referred to, once I put it off Heroku onto its own
environ (as at least stand-alone on Heroku there is no file storage)

On Fri, Aug 13, 2010 at 3:12 PM, Dave A.

9

Philip H. wrote:

Thanks a million for your reply. I will give that a go. I had seen that
that is possible but I was a bit worried that it would mean that my
migrations wouldn’t be database agnostic

Is there a reason that you’re not using VARCHAR? That should hold the
data (in less space, I believe!) and keep your migrations DB-agnostic.

Doesn’t mysql limit varchar to 65kb or so?

http://dev.mysql.com/doc/refman/5.1/en/char.html

Quite right. I was looking for size limits on VARCHAR and somehow
missed that. Thanks!

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

10

Marnen Laibow-Koser wrote:

Philip H. wrote:

Thanks a million for your reply. I will give that a go. I had seen that
that is possible but I was a bit worried that it would mean that my
migrations wouldn’t be database agnostic

Is there a reason that you’re not using VARCHAR? That should hold the
data (in less space, I believe!) and keep your migrations DB-agnostic.

Doesn’t mysql limit varchar to 65kb or so?

http://dev.mysql.com/doc/refman/5.1/en/char.html

Quite right. I was looking for size limits on VARCHAR and somehow
missed that. Thanks!

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Thanks a million for all the suggestions. I’ll go with using longtext
for now and look into moving over to a solution that stores the files on
s3 in the near future.

This was a my first post to forum and I am really appreciate all the
help that I got, thanks

11

On Aug 13, 2010, at 12:22 PM, David K. wrote:

maybe you could just use the
db to store a filename

From experience I would second this approach… in fact that is what I
did on the app I referred to, once I put it off Heroku onto its own
environ (as at least stand-alone on Heroku there is no file storage)

If you went this route… use paperclip and store it on S3… which
would let you stay on Heroku…

The drawback here is if you keep it on a local disk, you can’t easily
expand to multiple servers… just something to keep in mind.