从postgres迁移到mysql导致奇怪的错误Mysql2 :: Error:MySQL服务器已经消失 [英] migrating from postgres to mysql causing strange errors Mysql2::Error: MySQL server has gone away
问题描述
我正在切换我从中开发的Rails 3.0.3应用程序的数据库 postgres到mysql，以便我可以利用Amazon的rds.在我做之前 我一直在我的开发人员上使用mysql运行测试代码的更改 带有mysql2适配器的计算机.我的测试代码抛出了一些 我还无法完全解决的错误. 基本上，我有一个用于存储大型xml上传文件的模型.我的 测试代码看起来像这样
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
创建行引发以下错误
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'
....
我一直在将文件内容存储在文本字段中.我意识到 我应该认真考虑将文件存储在s3中，但这是 我现在有的设置.在postgres中，一切正常 但是为了使事情能与mysql一起使用，我必须设置:limit 变量，以便使用LONGTEXT代替标准文本 场地.文件可能很大，但是当我使用小文件进行测试时 没问题
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
我可能完全将错误的树吠叫，但我怀疑 问题可能是由于基于 我尝试在开发中上传文件时抛出的错误 模式.我对此错误进行了一些检查，但不确定是什么原因断开了连接，文件没有花费8个小时(默认的连接断开时间)插入
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服务器已消失:INSERT INTO xml_uploads ........
Mysql2::Error: MySQL server has gone away: INSERT INTO xml_uploads ........
我的database.yaml设置如下.
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.
推荐答案
无论如何，我还是决定将数据存储在S3中，但是有一位朋友向我指出了解决此问题的方法，我对其进行了测试并对其进行了测试.工作，所以我认为我应该将其发布在这里，以防其他人遇到相同的问题.
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.
基本上，此问题是由于将max_allowed_packet变量设置为小于博客/文本字段大小的值引起的.该查询无法执行，因此连接被删除.以下是有关max_allowed_packet变量
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
以及有关在rds实例上进行调整的一些信息
and also some info on adjusting it on rds instances
http://www.henrybaxter.ca/?p=111
这篇关于从postgres迁移到mysql导致奇怪的错误Mysql2 :: Error:MySQL服务器已经消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
