Rails - Aug 2009 - Store data from one form in two separate mysql tables?

Is it possible to store data from one form in two separate mysql tables?
I need to do this because the amount of data which can be submitted via
the form, exceeds the maximum mysql row size.
If this is not possible, has anyone got any ideas how one can get round
this problem?
Thanks in advance.
-- 
Posted via http://www.ruby-forum.com/.

On Aug 22, 10:34 am, Jim Burgess
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Is it possible to store data from one form in two separate mysql tables?
> I need to do this because the amount of data which can be submitted via
> the form, exceeds the maximum mysql row size.
That''s a lot of data given that the size limit is 65535 (and blobs/
texts only count a few bytes towards that)!
You pretty much need to just do it. Put some of the data from the
params hash into one object and the rest into another. Probably create
an association between these two models.

Fred
> If this is not possible, has anyone got any ideas how one can get round
> this problem?
> Thanks in advance.
> --
> Posted viahttp://www.ruby-forum.com/.

Thanks for the answer Fred.
I am understanding the definition of row size correctly, aren''t I?
What is happening is that I have a form (with loads of fields - some 
allowing up to 4000 characters).
When a user enters an average amount of data, then the form submits and 
everything is good.
When the user however enters more than an average amount of data and 
presses submit, the following error message is thrown:
ActiveRecord::StatementInvalid (Mysql::Error: Got error 139 from storage 
engine: ...
I did a lot of Googling for "maximum size of a mysql record" and kept 
finding references to the max. row size (which is, as you say 65535).
I took row size to mean the size of one data set (i.e. everything that 
gets submitted with the form).
Is this correct or am I missing something obvious?
Thanks very much for your help.
-- 
Posted via http://www.ruby-forum.com/.

On Aug 22, 2:45 pm, Jim Burgess
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Thanks for the answer Fred.
> I am understanding the definition of row size correctly, aren''t I?
> What is happening is that I have a form (with loads of fields - some
> allowing up to 4000 characters).
> When a user enters an average amount of data, then the form submits and
> everything is good.
> When the user however enters more than an average amount of data and
> presses submit, the following error message is thrown:
> ActiveRecord::StatementInvalid (Mysql::Error: Got error 139 from storage
> engine: ...
> I did a lot of Googling for "maximum size of a mysql record" and
kept
> finding references to the max. row size (which is, as you say 65535).
> I took row size to mean the size of one data set (i.e. everything that
> gets submitted with the form).
Row size is to do with the size of one row in the database. If what
you submit in your form ends up in multiple activerecord objects then
you''re dealing with multiple rows, each one of which could be as big
as the row size limits permit. googling around also suggests that 139
can mean that mysql ran out of memory

Fred
> Is this correct or am I missing something obvious?
> Thanks very much for your help.
> --
> Posted viahttp://www.ruby-forum.com/.

Yeah, I found it a bit bizarre as I have reprogrammed this form in RoR.
Previously it was in PHP and could deal with data of this magnitude 
without problems.
I guess I''ll keep Googling and see if I come up with anything as 
splitting the data into two tables seems an ugly solution.
-- 
Posted via http://www.ruby-forum.com/.

Hi Jim,

I wouldn''t recommend storing a single object in two tables or
splitting it across multiple rows. You''ll be fighting this complexity
elsewhere in your code and it will become a real headache.

Are you including images or something in your object? If so, you''d be
much better off attaching them to your object with something like
Paperclip (http://github.com/thoughtbot/paperclip/tree/master). This
will just add a reference to the file to your object and store the
data elsewhere (filesystem or Amazon S3). The filesystem is  much
better at storing large amounts of data than the database.

Amazon S3 doesn''t take much more effort to set up and has the
advantage of making your attachment available across multiple servers
that don''t share the same filesystem.

-
Bill

> I wouldn''t recommend storing a single object in two tables or
> splitting it across multiple rows. You''ll be fighting this
complexity
> elsewhere in your code and it will become a real headache.
I agree there, but I''m really not sure what to do. I can literally 
isolate the individual character that causes this error (seems to be 
around the 15,000 mark)
> Are you including images or something in your object? 
No, not at all. It''s a form used to submit an application to a uni 
program. Just plain text (although lots of it)

As I said, i am quite confused by this as I have the new form in RoR and 
the old form in PHP running in parallel. The same input that causes the 
RoR form to crash, causes no probs at all for the PHP form.

I also get the identical error on my home machine and the web server 
(hosting company)


-- 
Posted via http://www.ruby-forum.com/.

I''ve managed to understand / solve the problem and just wanted to post 
back here in case it helps anyone else.
Having read this: http://bugs.mysql.com/bug.php?id=10035
it appears that this error is caused when a MySQL table has more than 10 
columns of the type "text".
I tested this by creating a form with 11 text fields. I filled ten of 
them with input of 15,000 chrs and submitted it - everything fine. I 
stuck 15,000 characters in the eleventh text field and sure enough the 
same error was thrown. I started deleting the characters in the eleventh 
text field and at some point I was able to successfully submit the form.
This only appears to be an issue upwards of MySQL 4.1.11 which also 
explains why my old form (PHP version) didn''t crash (it''s
operating with
MySQL 4.0.24)
If I understand the advice given on 
http://bugs.mysql.com/bug.php?id=10035 correctly then the solution to 
this is to save one''s data in two tables, which is what I have done.
-- 
Posted via http://www.ruby-forum.com/.

2009/8/23 Jim Burgess
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>:
>
> I''ve managed to understand / solve the problem and just wanted to
post
> back here in case it helps anyone else.
> Having read this: http://bugs.mysql.com/bug.php?id=10035
> it appears that this error is caused when a MySQL table has more than 10
> columns of the type "text".
> I tested this by creating a form with 11 text fields. I filled ten of
> them with input of 15,000 chrs and submitted it - everything fine. I
> stuck 15,000 characters in the eleventh text field and sure enough the
> same error was thrown. I started deleting the characters in the eleventh
> text field and at some point I was able to successfully submit the form.
> This only appears to be an issue upwards of MySQL 4.1.11 which also
> explains why my old form (PHP version) didn''t crash (it''s
operating with
> MySQL 4.0.24)
> If I understand the advice given on
> http://bugs.mysql.com/bug.php?id=10035 correctly then the solution to
> this is to save one''s data in two tables, which is what I have
done.
Yet another reason to use PostgreSQL?

Colin