1

I'm switching the database of a rails 3.0.3 app I have developed from postgres to mysql so that I can avail of amazon's rds. Before I make the change I have been running my test code using mysql on my dev machine with the mysql2 adaptor . My test code is throwing up some errors that I haven't quite been able to get to the bottom of yet. Basically I have a model that is used to store large xml uploads. My test code looks something like this 

test "xml upload for large file" do 
  file = File.new("test/files/lib/upload_sample.xml") 
  upload = XmlUpload.create(:xml_contents => contents = file.read) 
  ..... 
  ..... 
end

The create line is throwing up the following error 

ActiveRecord::StatementInvalid: Mysql2::Error: SAVEPOINT active_record_1 does not exist:     ROLLBACK TO SAVEPOINT active_record_1 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/mysql2-0.2.6/lib/ active_record/connection_adapters/mysql2_adapter.rb:314:in `execute' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/mysql2-0.2.6/lib/ active_record/connection_adapters/mysql2_adapter.rb:358:in `rollback_to_savepoint' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/connection_adapters/abstract/database_statements.rb: 149:in `rescue in transaction' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/  active_record/connection_adapters/abstract/database_statements.rb: 127:in `transaction' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/transactions.rb:204:in `transaction' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/transactions.rb:287:in `with_transaction_returning_status' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/transactions.rb:237:in `block in save' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/transactions.rb:248:in `rollback_active_record_state!' 
/Users/conor/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/ active_record/transactions.rb:236:in `save'  
....

I have been storing the file contents in a text field. I realise that I should seriously look at storing the files in s3 but this is the setup that I have at the moment. In postgres everything worked fine but in order to get things to work with mysql I had to set the :limit variable so that LONGTEXT was used instead of the standard text field. The files can be quite large but when I test using small files there are no problems

I could be barking up the wrong tree entirely but I suspect that the problem may be caused by the database connection being dropped based on the errors thrown up when I try uploading a file in the development mode. I did some checking on this error and I'm not sure what could be dropping the connection, the file isn't taking 8 hrs (the default connection drop time) to insert

Mysql2::Error: MySQL server has gone away: INSERT INTO xml_uploads ........

My database.yaml settings are the following. 

test: 
   adapter: mysql2 
   encoding: utf8 
   reconnect: true 
   database: app_test 
   username: username 
   password: password 
   host: localhost

Does anyone have any clues as to what the problem is and how it can fixed? Any help with this would be greatly appreciated.

Nakilon
  • 34,866
  • 14
  • 107
  • 142
Conor
  • 775
  • 3
  • 10
  • 23
  • Conor, I used to have this problem some years ago with java and I can't remember properly what was the problem. But I remember that we were overloading the server (maybe the transaction was too big for the server) and it stopped responding with that message "server has gone away". Have you tried this with a small dataset? or a small file? – Augusto Feb 10 '11 at 11:57
  • Hi Augusto, thanks for your reply. I have tried testing with small files and everything runs smoothly but with the test files which are representative of the kind of data that could be uploaded it fails. These files are big (1.5-2.5mb) but nothing crazy. I think connection is timing out and being dropped before the insert but I'm not sure why it would be – Conor Feb 10 '11 at 13:49
  • try set a very LARGE timeout in mysql - details can be found here - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html – ajreal Feb 10 '11 at 14:37
  • hi ajeal, thanks for your reply. I had a look at that too and tried playing around with the timeout variable alright. The default is 28800 sec (8hrs) though so my guess is that something else is causing the problem but I'm not sure what. I also played with what the default was being set to within my rails app but I still didn't have any luck – Conor Feb 10 '11 at 15:37

1 Answers1

2

I decided to go with the storing the data in S3 anyway but a friend did point me in the direction of the solution to this issue, I tested it and it worked, so I thought I should post it here in case anyone else runs into the same problem.

Basically the problem is caused by the max_allowed_packet variable being set to something smaller than the blog/text field size. The query can't be executed so the connection gets dropped. Here are some details about the max_allowed_packet variable

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

and also some info on adjusting it on rds instances

http://www.henrybaxter.ca/?p=111

Conor
  • 775
  • 3
  • 10
  • 23