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.
On Aug 22, 10:34 am, Jim B. [email protected]
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
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.
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.
On Aug 22, 2:45 pm, Jim B. [email protected]
wrote:
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
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 (GitHub - thoughtbot/paperclip at 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’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.
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)
2009/8/23 Jim B. [email protected]:
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