:limit text mysql

rubyonrails-talk
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 Kahn 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 Nugent wrote:

David Kahn 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,

5

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.

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

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.

7

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

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)

9

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.

10

Philip Hallstrom 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,

11

Marnen Laibow-Koser wrote:

Philip Hallstrom 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 marnen@marnen.org

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