# Detecting width overflow in serialized column with mysql

**URL:** <https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469>\
**Category:** Rails\
**Created:** [October 21, 2008, 10:40pm UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469 "2008-10-21T22:40:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 21, 2008, 10:40pm UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/1 "2008-10-21T22:40:30Z")

</div>

So I’ve got an ActiveRecord model pointing to a MySQL db, with an  
auto-serialized column (“serialize :columnName”).

Thing is, MySQL, depending on how it’s configured (like, by default),  
has a bad habit of just truncating your data if it’s too wide for the  
column, with no error raised. Yeah, I can probably reconfigure MySQL  
and/or my AR connection to it. But I’m wanting to distribute this code  
to all kinds of people who won’t think of that or won’t want to do that,  
I’d really like it to work with the default setup.

But obviously, if a yaml serialization gets truncated, it’s not going to  
unserialize very well.

At the point of insert/update, is there any good way for me to detect  
that the serialization was too big for the column, even though MySQL  
isn’t complaining?

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 22, 2008, 12:40am UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/2 "2008-10-22T00:40:56Z")

</div>

Jonathan R. wrote:

> But obviously, if a yaml serialization gets truncated, it’s not going to  
> unserialize very well.
> 
> At the point of insert/update, is there any good way for me to detect  
> that the serialization was too big for the column, even though MySQL  
> isn’t complaining?

This may do it:

def validate  
if columnName.to\_yaml \> column\_for\_attribute(:columnName).limit  
errors.add(:columnName, ‘columnName is too big to be serialized’)  
end  
end

It’d be good if something like this was added automatically by  
serialize.

–  
Rails Wheels - Find Plugins, List & Sell Plugins -  
[http://railswheels.com](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 22, 2008, 12:52am UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/3 "2008-10-22T00:52:29Z")

</div>

Interesting, thanks, lots of tricks there I wouldn’thave figured out for  
myself. But am I being really inefficient now by calling to\_yaml an  
extra time? I’ll call it once to validate, and then throw away the  
results. Then serialize will call it again when it actually wants to  
save. Calling it twice for no reason. But I guess to\_yaml shouldn’t be  
_that_ expensive?

Now I wonder if I should abandon the automated serialization altogether,  
and just handle it myself manually through callbacks. Or maybe I should  
try monkey-patching the auto-serialization stuff to perform this check  
itself?

Jonathan

Mark Reginald J. wrote:

> Jonathan R. wrote:
> 
> > But obviously, if a yaml serialization gets truncated, it’s not going to  
> > unserialize very well.
> > 
> > At the point of insert/update, is there any good way for me to detect  
> > that the serialization was too big for the column, even though MySQL  
> > isn’t complaining?
> 
> This may do it:
> 
> def validate  
> if columnName.to\_yaml \> column\_for\_attribute(:columnName).limit  
> errors.add(:columnName, ‘columnName is too big to be serialized’)  
> end  
> end
> 
> It’d be good if something like this was added automatically by  
> serialize.
> 
> –  
> Rails Wheels - Find Plugins, List & Sell Plugins -  
> [http://railswheels.com](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 22, 2008, 1:19am UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/4 "2008-10-22T01:19:33Z")

</div>

Jonathan R. wrote:

> itself?  
> Yeah, the double yaml is a waste, but don’t worry about it  
> unless this is being done sufficiently often to slow down  
> your app in a way that generates more work or cost.

See if you can come up for a patch for Rails that automatically  
adds a such a validation for serialized attributes, where this  
validation method and the attributes\_with\_quotes method that  
generates the DB request both draw from a cache of YAMLized  
attribute values.

–  
Rails Wheels - Find Plugins, List & Sell Plugins -  
[http://railswheels.com](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 22, 2008, 4:23pm UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/5 "2008-10-22T16:23:22Z")

</div>

Here’s a different (easier 🙂 ) tack to monkey patching AR to solve this  
problem efficiently. Turns out the actual serialization is done in  
quote\_value (and interestingly, done whether or not the column is  
declared serialized; the serialized decleration only effects  
de-serialization). Does anyone think this following approach is a good  
idea?

Does anyone think it’s a good enough idea I should submit it as a patch  
to AR? I’ve never submitted a patch before, not sure how it’s done. I’m  
still using Rails 1.8.x, not sure if this is still a problem in Rails 2,  
so that might make it hard to submit a patch.

class ActiveRecord::Base  
alias :old\_quote\_value :quote\_value  
def quote\_value(value, column = nil)  
if column && serialized\_attributes[column.name]  
serialization = value.to\_yaml  
raise new ActiveRecord::StatementInvalid(“Can not serialize  
column #{column.name}, length #{serialization.length} is greater than  
column limit of #{column.limit}”) if if serialization.length \>  
column.limit  
“#{connection.quoted\_string\_prefix}‘#{connection.quote\_string(serialization)}’”  
else  
old\_quote\_value  
end  
end  
end

Mark Reginald J. wrote:

> Jonathan R. wrote:
> 
> > itself?  
> > Yeah, the double yaml is a waste, but don’t worry about it  
> > unless this is being done sufficiently often to slow down  
> > your app in a way that generates more work or cost.
> 
> See if you can come up for a patch for Rails that automatically  
> adds a such a validation for serialized attributes, where this  
> validation method and the attributes\_with\_quotes method that  
> generates the DB request both draw from a cache of YAMLized  
> attribute values.
> 
> –  
> Rails Wheels - Find Plugins, List & Sell Plugins -  
> [http://railswheels.com](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 23, 2008, 4:47am UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/6 "2008-10-23T04:47:54Z")

</div>

On 22 Oct 2008, at 10:23, Jonathan R. wrote:

> patch  
> to AR? I’ve never submitted a patch before, not sure how it’s done.  
> I’m  
> still using Rails 1.8.x, not sure if this is still a problem in  
> Rails 2,  
> so that might make it hard to submit a patch.

You’re almost certainly not using Rails 1.8.x because that version has  
never existed (but you are probably running ruby 1.8.x (where x is  
probably 6))  
Patches can be submitted at [rails.lighthouseapp.com](http://rails.lighthouseapp.com) and/or discussed  
on the rubyonrails-core google group mailing list

Fred

---

<div class="post-metadata">

**Author:** ![jrochkind](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@jrochkind](https://www.ruby-forum.com/u/jrochkind)\
**Post date:** [October 22, 2008, 10:41pm UTC](https://www.ruby-forum.com/t/detecting-width-overflow-in-serialized-column-with-mysql/151469/7 "2008-10-22T22:41:37Z")

</div>

Jonathan R. wrote:

> so that might make it hard to submit a patch.  
> “#{connection.quoted\_string\_prefix}‘#{connection.quote\_string(serialization)}’”  
> else  
> old\_quote\_value  
> end  
> end  
> end

Jonathan, that’s a fine solution if a database exception  
is a better way to handle it than an automatic validation.  
The validation route may however be more compatible with  
the current save! idiom.

Also note that ActiveRecord trunk does the YAML conversion  
in the attributes\_with\_quotes method instead.

–  
Rails Wheels - Find Plugins, List & Sell Plugins -  
[http://railswheels.com](http://railswheels.com)