Re: [Activewarehouse-discuss] problems using bulk_import (to mysql on win xp)
Check out this:
http://rubyforge.org/pipermail/activewarehouse-discuss/2007-December/000224.html
On Feb 15, 2008 4:06 PM, Paul Cortens <[EMAIL PROTECTED]> wrote:
> are you using the Ruby MySQL gem or the native gem?
>
> I had the exact same error when using the Ruby gem.
>
> Paul
>
> On Feb 15, 2008 3: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 "
Re: [Activewarehouse-discuss] problems using bulk_import (to mysql on win xp)
are you using the Ruby MySQL gem or the native gem?
I had the exact same error when using the Ruby gem.
Paul
On Feb 15, 2008 3: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 ','
Re: [Activewarehouse-discuss] problems using bulk_import (to mysql on win xp)
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/>
>
