CODEWITHSUNDEEP
mysqlruby
FlyingV
FlyingV

Reputation: 3535

Plain Old Ruby data load local in File

Problem: MYSQL and RUBY (not with rails?) gives error:

The used command is not allowed with this MySQL version (Mysql::Error)

Dear Friends,

  1. I can Issue this from the command line but NOT from my ruby (WITHOUT rails).

    mysql> Load data local infile 'remediation_rewrite.csv' into table report.remediation IGNORE 1 lines (MACHINE_NAME , TYPE_OF_ACTION , SCORE , FILE_PATH , MD5, REGKEY , VThit, action , action_result, action_result_detail, date); Query OK, 455168 rows affected, 65535 warnings (22.35 sec)

2) Ruby command that fails for the following reason: 

column = "(MACHINE_NAME , TYPE_OF_ACTION , SCORE , FILE_PATH , MD5,  REGKEY , VThit, action , action_result, action_result_detail, date)"
    str = "Load data local infile " + "'" + "remediation_rewrite.csv" + "'" + " into table report.remediation" + " IGNORE 1 lines " + "#{column}" + ";";
    p str
    @con = Mysql.new("172.16.10.193", "myusername","xxx", "reporttdatabase")
    @con.options(Mysql::OPT_LOCAL_INFILE, true)
    rs = @con.query(str)

Upvotes: 0

Views: 331

Answers (1)

PinnyM
PinnyM

Reputation: 35531

As per the 'mysql2' gem documentation, you need to specify :local_infile as true for security reasons (explained here, and referenced here).

Substitute this for your @con client object:

@con = Mysql2::Client.new(
  host: "172.16.10.193", 
  username: "myusername",
  password: "xxx", 
  database: "reporttdatabase",
  local_infile: true
)

Upvotes: 2

Related Questions