Forum: Ruby on Rails MySQL Error inserting blob

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sandworth M. (Guest)
on 2008-12-10 10:04
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
Sandworth M. (Guest)
on 2008-12-10 10:36
Actually, the variable in my my.ini file is max_allowed_packet, not
max_packet_size.
Simon M. (Guest)
on 2008-12-10 10:48
(Received via mailing list)
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
Sandworth M. (Guest)
on 2008-12-10 11:02
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.
Simon M. (Guest)
on 2008-12-10 11:13
(Received via mailing list)
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 :(

On Wed, 10 Dec 2008 18:02:32 +0900, Farmer Schlutzenberg
Sandworth M. (Guest)
on 2008-12-10 13:03
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
Sandworth M. (Guest)
on 2008-12-10 13:05
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.
This topic is locked and can not be replied to.