# Store data from one form in two separate mysql tables? **URL:** \ **Category:** Rails\ **Created:** [August 22, 2009, 11:34am UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202 "2009-08-22T11:34:27Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 11:34am UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/1 "2009-08-22T11:34:27Z")
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. ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 3:28pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/2 "2009-08-22T15:28:33Z")
On Aug 22, 10:34 am, Jim B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 3:45pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/3 "2009-08-22T15:45:48Z")
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. ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 4:34pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/4 "2009-08-22T16:34:24Z")
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. ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 4:07pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/5 "2009-08-22T16:07:11Z")
On Aug 22, 2:45 pm, Jim B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 4:39pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/6 "2009-08-22T16:39:23Z")
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](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 ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 23, 2009, 11:24pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/7 "2009-08-23T23:24:32Z")
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](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](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. ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 22, 2009, 4:46pm UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/8 "2009-08-22T16:46:40Z")
> 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) ---
**Author:** ![jimb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f4b2a3/32.png) [@jimb](https://www.ruby-forum.com/u/jimb)\ **Post date:** [August 24, 2009, 9:20am UTC](https://www.ruby-forum.com/t/store-data-from-one-form-in-two-separate-mysql-tables/174202/9 "2009-08-24T09:20:10Z")
2009/8/23 Jim B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > 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](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