Mysql::Error: Data too long for column

Ruby
1

Pleas i need help:
I’m deploying a ror application in a win server (apache + mongrel +
mysql).
A model has a binary field.

win2003 server
mysql 5.0.45 community ed
ruby 1.8.6

When i upload a file (very small) i obtain the “Data Too Long for
column”
error.
The same code works perfectly with SQLite.
Where’s the problem?

2

On Dec 9, 2009, at 12:50 PM, rabarama wrote:

column" error.
The same code works perfectly with SQLite.
Where’s the problem?

Are you storing session information in the database? If so, one of the
columns (the data column IIRC) may not be large enough. This happens
with postgres.

Cheers–

Charles

3

Are you storing session information in the database? If so, one of the
columns (the data column IIRC) may not be large enough. This happens
with postgres.

Cheers–

no, session is at the moment in cookies.
Searching seems that the problem is db encoding, but I’m not sure how to
get
everything working.
This only happends when i try to store to the db a file in a binary
(blob in
mysql) field.
It works well if i don’t upload anything.
In SQLite I don’t have this problem (same code and environment, same
test).

4

On Wed, Dec 9, 2009 at 10:50 AM, rabarama
[email protected] wrote:

I’m deploying a ror application in a win server (apache + mongrel + mysql).
A model has a binary field.

When i upload a file (very small) i obtain the “Data Too Long for column”
error.

So how big exactly is “very small” and what is the column definition?

5

So how big exactly is “very small” and what is the column definition?

“very small” is a 80Kb file, and the column is a “binary” field in the
schema that becames a BLOG column in mysql.

6

On Wed, Dec 9, 2009 at 12:45 PM, rabarama
[email protected] wrote:

So how big exactly is “very small” and what is the column definition?

“very small” is a 80Kb file, and the column is a “binary” field in the
schema that becames a BLOG column in mysql.

Last time I looked a MySQL BLOB field was 64k. Hmm. 80k, 64k –
you might want to take a look at that discrepancy :slight_smile:

7

El Miércoles, 9 de Diciembre de 2009, Hassan S.
escribió:> On Wed, Dec 9, 2009 at 12:45 PM, rabarama

[email protected] wrote:

So how big exactly is “very small” and what is the column definition?

“very small” is a 80Kb file, and the column is a “binary” field in the
schema that becames a BLOG column in mysql.

Last time I looked a MySQL BLOB field was 64k. Hmm. 80k, 64k –
you might want to take a look at that discrepancy :slight_smile:

Yes, better using MEDIUMBLOB.

8

probably, this is the problem, tomorrow I’ll try with mediumblog.
Thanks a lot.

9

Marnen Laibow-Koser wrote:

rabarama wrote:

probably, this is the problem, tomorrow I’ll try with mediumblog.
Thanks a lot.

Better to use the filesystem. Sizable binaries in the DB are not a good
idea.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

I think its more accurate to say mixing meta and binary data in the same
MySQL table is not a good idea.

Storing binary data in a separate table (and even database) from meta
data minimizes the binary data’s performance impact and allows one to
use MySQL to manage referential integrity across the meta and binary
data.

10

I confirm!
That was the problem, now it works.
Thanks a lot for solution and suggestions.

11

Steve W. wrote:

Marnen Laibow-Koser wrote:

rabarama wrote:

probably, this is the problem, tomorrow I’ll try with mediumblog.
Thanks a lot.

Better to use the filesystem. Sizable binaries in the DB are not a good
idea.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

I think its more accurate to say mixing meta and binary data in the same
MySQL table is not a good idea.

Storing binary data in a separate table (and even database) from meta
data minimizes the binary data’s performance impact and allows one to
use MySQL to manage referential integrity across the meta and binary
data.

As far as I can see, the only advantage of storing binarie in RB tables
is to be able to store them with their metadata. If you split them into
their own table, then you’re just reinventing a filesystem on top of the
existing filesystem.

In other words, if you use binary fields, you will either have
performance problems or an abstraction inversion. Just don’t.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

12

rabarama wrote:

probably, this is the problem, tomorrow I’ll try with mediumblog.
Thanks a lot.

Better to use the filesystem. Sizable binaries in the DB are not a good
idea.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]