MySQL Error inserting blob

Rails
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:inexecute’
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:ininsert_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:ininsert’
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:ineach’
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:inimport_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:inimport’
/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:insilence’
/app/models/document.rb:149:in index' /app/models/document.rb:19:inintegrate_import_record’
/app/models/import_set.rb:44:in integrate' /app/models/import_set.rb:33:ineach’
/app/models/import_set.rb:33:in integrate' /app/controllers/import_sets_controller.rb:47:inintegrate’
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:inperform_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:inperform_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:inmeasure’
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:inperform_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:incache’
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:inperform_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:inprocess_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:inprocess’
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:inhandle_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:indispatch’
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:indispatch’
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:indispatch’
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:insynchronize’
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:inprocess_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:inprocess_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:ininitialize’
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:inrun’
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:innew’
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:inrun’
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:inrun’
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:inrun’
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:inload’
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:inload’
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:inrequire’
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:innew_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:ingem_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

On Wed, 10 Dec 2008 17:04:25 +0900, Farmer Schlutzenberg

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:

On Wed, 10 Dec 2008 18:02:32 +0900, Farmer Schlutzenberg

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.