Hi Jesper,
My understanding is that this is an issue with how ActiveRecord works
with MySQL. I believe there was a change in how MySQL did bulk update
and that ActiveRecord's way of calling this no longer works. Sorry, I
don't have more technical information on the issue. Our work around
has been to turn off bulk uploads. Maybe someone else who has more
detailed information on this can speak up.
Cheers,
Marty
On Fri, Feb 15, 2008 at 4:52 PM, Jesper Rønn-Jensen <[EMAIL PROTECTED]> wrote:
> Hi there
> I have been trying for days to get bulk import working with
> Activewarehouse-ETL
> Here is my rule in employees.ctl:
>
> post_process :bulk_import, {
> :file => 'output/employees.out.txt',
> :columns => EMPLOYEE_COLS,
> :truncate => true,
> :field_separator => ',',
> :field_enclosure => '"',
> #:line_separator => '\n',
> :ignore => 1,
> :target => :development,
> :table => 'employees'
> }
>
> ====This is the error I get when running:====
> > C:\Documents and Settings\Administrator\My
> Documents\rails\ourpeople\import>etl -c ../config/database.yml --limit 200
> employees.ctl
> > Using AdapterExtensions
> > Starting ETL process
> > initializing ETL engine
> > Processing employees.ctl
> > Source: employees.csv
> > Limiting enabled: 200
> > Reached limit of 200
> >
> > Executing screens
> > Screens passed
> >
> > Executing post processes
> >
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:150:in
> `log': Mysql::Error: #42000The used command is not allowed with this MySQL
> version: LOAD DATA LOCAL INFILE 'C:/Documents and Settings/Administrator/My
> Documents/rails/ourpeople/import/output/employees.out.txt' INTO TABLE
> employees FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES
> (id,cgid,longid,firstname,lastname,displayname,middleinitial,initials,phone,mobile,fax,subcellphone,email,englishtitle,localtitle,secretary_id,mentor_id,substitute_id,manager_id,comment,licenseplate,employment_cgemployee,employment_cgexternal,employment_cgtemporary,employeework_employeeid,employeework_workpercentage,employeework_actualworkpercentage,company_id,grade_id,location_id,organisation_id,profession_id,resourcetype_id,skillsunit_id,staffingunit_id)
> (ActiveRecord::StatementInvalid)
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/mysql_adapter.rb:281:in
> `execute'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/adapter_extensions-0.4.0/lib/adapter_extensions/connection_adapters/mysql_adapter.rb:46:in
> `do_bulk_load'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/adapter_extensions-0.4.0/lib/adapter_extensions/connection_adapters/abstract_adapter.rb:18:in
> `bulk_load'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/processor/bulk_import_processor.rb:73:in
> `process'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in
> `transaction'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/processor/bulk_import_processor.rb:62:in
> `process'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/engine.rb:485:in
> `post_process'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/../lib/etl/engine.rb:484:in
> `each'
> > ... 13 levels...
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:496:in
> `require'
> > from
> c:/ruby/lib/ruby/gems/1.8/gems/activewarehouse-etl-0.9.0/bin/etl:28
> > from c:/ruby/bin/etl:19:in `load'
> > from c:/ruby/bin/etl:19
> >
> ====end of error====
>
> I have tried a lot of different combinations of flags in that bulk_import
> directive. Am i getting it right? Could you please show me some working
> examples.
>
> Also, for adapter_extensions there seems to be a patch adding this line to
> the mysql adapter
> >
> > --- activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
> (revision 8484)
> > +++ activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
> (working copy)
> > @@ -467,6 +467,7 @@
> > if encoding
> > @connection.options(Mysql::SET_CHARSET_NAME, encoding) rescue
> nil
> > end
> > + @connection.options(Mysql::OPT_LOCAL_INFILE, true)
> > @connection.ssl_set(@config[:sslkey], @config[:sslcert],
> @config[:sslca], @config[:sslcapath], @config[:sslcipher]) if
> @config[:sslkey]
> > @connection.real_connect([EMAIL PROTECTED])
> > execute("SET NAMES '#{encoding}'") if encoding
> >
>
>
> Well, i did not apply the patch to version 0.4.0 because it seemed like it
> was already in the code (however, not "true" but "1" at the
> end of the line)
>
> The most odd thing is that I can easily run a bulk import via the local sql
> command:
> > /ourpeople/import/>
> > $ mysql ourpeople_development < import.sql --user=root -p
> >
>
> Where the import.sql contains
> > LOAD DATA LOCAL INFILE './output/employees.out.txt' INTO TABLE
> employees CHARACTER SET latin1 FIELDS TERMINATED BY ',' IGNORE 1 LINES
> ;
>
> Any suggestions/ideas to what i might be doing wrong? Any examples of
> working code you can share?
>
>
> --
> ___________________________________________________________
>
> Jesper Rønn-Jensen / Capgemini Denmark
> Tel. +45 3977 8220 / Mob. +45 2373 6220 / Fax +45 7011 2201
> www.dk.capgemini.com / Blog http://justaddwater.dk/
> [EMAIL PROTECTED] (Private e-mail and Google Talk IM)
> ___________________________________________________________
> _______________________________________________
> Activewarehouse-discuss mailing list
> Activewarehouse-discuss@rubyforge.org
> http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
>
>
_______________________________________________
Activewarehouse-discuss mailing list
Activewarehouse-discuss@rubyforge.org
http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
