MySQL Error inserting blob

rubyonrails-talk
1

Hi,

I'm trying to insert a blob into a MySQL database with a Rails app. I'm getting the error:

ActiveRecord::StatementInvalid (Mysql::Error: MySQL server has gone away: INSERT INTO `index_entries` (`legal_case_id`,`document_id`,`sword`,`word_count`,`map_data`,`created_at`,`updated_at`) VALUES (3,1368,'f',208720,'\0\0........blob data........\0Œ1\0 Œ1\0','2008-12-10 05:17:29','2008-12-10 05:17:29') ON DUPLICATE KEY UPDATE `index_entries`.`updated_at`=VALUES(`updated_at`)):

I believe the blob itself should be about 820K long. Cutting it back to 800K, it works alright. But I've checked my MySQL config, and it claims that max_packet_size=1M. I've also tried setting it to 32M, and that didn't make any difference. I've also tried replacing the data with a sequence "01010101..." of the same length, and the same thing occurs: at 800K it works, but the same error occurs at 820K. Does anyone know what's going on?

Any help most appreciated!

Here is the stack trace:

    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/mysql_adapter.rb:309:in `execute'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:171:in `insert_sql'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/mysql_adapter.rb:319:in `insert_sql'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dirty'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:18:in `insert'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/adapters/abstract_adapter.rb:46:in `insert_many'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/adapters/abstract_adapter.rb:43:in `each'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/adapters/abstract_adapter.rb:43:in `insert_many'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/import.rb:282:in `import_without_validations_or_callbacks'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/import.rb:243:in `import_with_validations'     C:/Ruby/lib/ruby/gems/1.8/gems/ar-extensions-0.8.0/lib/ar-extensions/import.rb:202:in `import'     /app/models/document.rb:150:in `index'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/buffered_logger.rb:25:in `silence'     /app/models/document.rb:149:in `index'     /app/models/document.rb:19:in `integrate_import_record'     /app/models/import_set.rb:44:in `integrate'     /app/models/import_set.rb:33:in `each'     /app/models/import_set.rb:33:in `integrate'     /app/controllers/import_sets_controller.rb:47:in `integrate'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `send'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:1253:in `perform_action_without_filters'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:617:in `call_filters'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:610:in `perform_action_without_benchmark'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'     C:/Ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/rescue.rb:136:in `perform_action_without_caching'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:13:in `perform_action'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract/query_cache.rb:34:in `cache'     C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/query_cache.rb:8:in `cache'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/caching/sql_cache.rb:12:in `perform_action'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `send'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:524:in `process_without_filters'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/filters.rb:606:in `process_without_session_management_support'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/session_management.rb:134:in `process'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/base.rb:392:in `process'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:183:in `handle_request'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:110:in `dispatch_unlocked'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:123:in `dispatch'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `synchronize'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:122:in `dispatch'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:132:in `dispatch_cgi'     C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.2.2/lib/action_controller/dispatcher.rb:39:in `dispatch'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/bin/../lib/mongrel/rails.rb:76:in `process'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/bin/../lib/mongrel/rails.rb:74:in `synchronize'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/bin/../lib/mongrel/rails.rb:74:in `process'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:159:in `process_client'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:158:in `each'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:158:in `process_client'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in `initialize'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in `new'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in `initialize'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in `new'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel/configurator.rb:282:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel/configurator.rb:281:in `each'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel/configurator.rb:281:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/bin/mongrel_rails:128:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel/command.rb:212:in `run'     C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/bin/mongrel_rails:281     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load_without_new_constant_marking'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:142:in `load'     C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/commands/servers/mongrel.rb:64     C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'     C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:521:in `new_constants_in'     C:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.2.2/lib/active_support/dependencies.rb:153:in `require'     C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/commands/server.rb:49     C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'     C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'     script/server:6

- Farmer

2

Actually, the variable in my my.ini file is max_allowed_packet, not max_packet_size.

3

we have had the same problem and it turned out to be the size of the column in mysql

whenever we create a blog column we go through and change the type to LONGBLOB using the following in the migration.

execute "ALTER TABLE pgcs CHANGE pgc pgc LONGBLOB;"

Simon

4

Hi Simon, I have it set on MEDIUMBLOB, which the MySQL documentation says has a limit of 2^24 bytes, so well above 820K. It was previously on BLOB, and I was getting a different error (something like "data too big for column"). I just used :limit => size_limit as a condition in a rails migration, and this changed the column type from BLOB to MEDIUMBLOB. Thanks tho.

5

I just know that when we had that 'mysql has gone away' error, the mysql logs had something like 'value too large for col' etc...

But if that isn't your problem, then I have nothing else to offer :frowning:

6

I've now worked it out. I eventually decided to restart my computer on the off chance that MySQL wasn't actually reconfiguring after I set max_allowed_packet (even though it seemed this shouldn't actually affect things since I was only changing from 1M to 32M, and my query seems to be less than 1M). This fixed the problem.

It seems odd to me that I should need to restart my computer to get MySQL to recognise the change - I did restart my server after the config change to 32M, so I expected the connection made to MySQL to use the 32M packet size. If anyone knows how to configure MySQL to not require computer restarts (this is in Vista), that would be good to know.

Even stranger is that with a supposed limit of 1M, the following code -

puts blobstring.class puts blobstring.length Record.new(:field_1 => integer_1, :field_2 => integer_2, :field_3 => word, :field_4 => integer_4, :blob_field => blobstring).save

outputs:

String 834880

and causes a "MySQL has gone away" error, on response to the MySQL insert command caused by the "save" call above, yet with max_allowed_packet=32M, it works fine. Maybe there are is more than just the query string itself contributing to the 1 megabyte?

- Farmer

- Farmer

7

And, in field_3 it was just word="f", so that shouldn't have added too much. And, as I mentioned earlier, cutting the blob size down to 800K also made it work OK.