Skip to content

Loading…

LOAD DATA LOCAL command #43

Closed
seouri opened this Issue · 30 comments

10 participants

@seouri

I have a rake task that load data in text files to tables, but got the following error:

Mysql2::Error: The used command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE '/path/to/articles.dat' INTO TABLE articles

Will this command be added in the future or never by design?

@brianmario
Owner

Interesting, I'll take a look at why this is failing soon

@seouri

Thanks!! The same rake task works with mysql gem as well as in the mysql client. So it's not a mysql configuration issue.

@brianmario
Owner

Good to know, thanks

@brianmario
Owner

It's disabled in mysql2 at the moment. There's a way for me to enable it, but you should definitely read this while I think it over: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

Closing for now, I may add this in the near future

@erolosty

Please Do! Or could you direct me to where I can change it? I've just upgraded my app to rails 3 and this is the only thing thats not working (and we all know I'm not going back to mysqlplus after using mysql2)

@brianmario
Owner

As of this commit a6b5e9c you should be able to specify a default set of connect flags for connections by updating:

Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES

Let me know if that works for you ;)

@jpfuentes2

Just wanted to let ya know it fixed my problem. Thanks.

@brianmario
Owner

Awesome thanks for letting me know!

@erolosty

Thanks Mario sorry for not replying, haven't actually have a chance to use it yet (not changed from hackey os() call). But it seems it will work :) Thanks for the quick response! much obliged

@trezero

Sorry, I'm a bit new to rails. I'm having the same issue. I tried adding the line above:

Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES

But then I get the error: Mysql2::Error: Malformed packet: LOAD DATA LOCAL INFILE

Am I missing something simple here?

-- Jason

@allentsai

I'm having the same issue as Jason, sad face.

@trusche

If you get this error:

Mysql2::Error: Malformed packet: LOAD DATA LOCAL INFILE

try uninstalling and then re-installing the mysql2 gem. I had the same issues, turns out I had installed mysql2 with one version of MySQL (compiled and installed from source) which was later upgraded to another version (installed with homebrew). Since the gem uses native extensions, it must be compiled with the MySQL version it is used for.

@sodabrew
Collaborator

I just found this issue, after filing issue 233 #233, but whereas my code solution worked fine (adding code to ext/mysql2/mysql2.c to make a mysql_options call), the solution in this ticket results in the malformed packet error. I have verified that the MySQL headers and libs I compiled against are the same my code is using when it runs. Stumped at the moment.

@thbar

In case someone comes here: used the Mysql2::Client::LOCAL_FILES trick and it resulted in packet errors.

I will attempt to use #242 for the most likely fix for this (which looks more similar with mysql 1).

@brianmario
Owner

Curious why this worked for some and not for others. What version of mysql2 are you running? Did you try reinstalling it?

@thbar

I'm curious as well - if I can help you find out let me know!

I tried uninstalling then reinstalling a couple of minutes ago; I'm running mysql2 0.3.11 if I'm right.

The previous trick went a bit further so in a sense it worked, but each attempt to LOAD DATA LOCAL INFILE resulted in packet error.

Do you want me to run something specific? Let me know!

@thbar

To be accurate, I'm looking at supporting mysql2 in activewarehouse-etl and its component adapter_extensions: https://github.com/activewarehouse/adapter_extensions/blob/rails-3/lib/adapter_extensions/adapters/mysql_adapter.rb#L49

@brianmario
Owner

Have you recently upgraded MySQL (and/or libmysql) on the box your testing this on? For reinstalling, make sure you completely remove any versions you might have installed first. If you're using Bundler try wiping the bundle directory where gems are being installed into. That way you'll be sure to be rebuilding the C extension from scratch.

From what I can tell, that pull request that fixed it for you isn't really changing anything other than refactoring the mysql_options call into a single method in the C extension. I don't see how it could be changing the behavior of the call other than the fact that you recompiled the C extension in order to test the new code.

Let me know if recompiling it fixes it, and we'll go from there.

@brianmario brianmario reopened this
@thbar

Ah - I thought the pull request was doing something different (ie: relying on MYSQL_OPT_LOCAL_INFILE instead of CLIENT_LOCAL_FILES) - but then I'm just discovering mysql2.

I'll nuke my install as much as possible and will report in an hour max.

@sodabrew
Collaborator

Hi Brian, buried in the refactor, I added another option :local_infile. Added a note on the pull request explaining.

@thbar - I think Brian misunderstood the purpose of my pull request, which is to resolve the protocol error caused by this incorrect line of code:
Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES

I don't know what versions of MySQL that might work for, but none that I've used.

This does work, after applying my pull request:

@mysql = Mysql2::Client.new(:host => @host, :username => @username, :database => @schema, :local_infile => true)

@brianmario
Owner

Also, you guys are setting Mysql2::Client.default_query_options[:connect_flags] |= Mysql2::Client::LOCAL_FILES before connecting right?

@sodabrew
Collaborator

I think I was, I'll try again to double check... Ok, now I cannot reproduce the error, neither setting the flag before connecting nor between connecting and issuing a query.

It looks like all of my client libraries are now compiled with local files enabled by default -- removing this flag entirely does not lock me out. Tested on Debian Lenny with both MySQL 5.0 and 5.1 and database located on a separate host.

@thbar

I nuked my rvm gemset and went in there to double check and re-ran bundle install, but this didn't fix the issue.

As well although it's a bit complicated (I'm running a bin command which in turn load AR etc), I put the require 'mysql2' and the flag tweak at the very top of the first loaded file and I get the same Mysql2::Error: Malformed packet error.

This can be reproduced using the following project:

https://github.com/activewarehouse/activewarehouse-etl-sample

(but then you'll have to revert the latest commit which uses @sodabrew fork).

@brianmario do you want me to work on a smaller repro maybe?

@brianmario
Owner

A repro would be amazing! Maybe a gist or even just a simple repo

@thbar

(note: if someone tries to reproduce using my sample, do not fully revert the last commit, it won't work unless with gem 'adapter_extensions',:git => 'https://github.com/activewarehouse/adapter_extensions.git', :branch => 'rails-3')

@thbar

@brianmario working on it - I'll drop the gist here.

@thbar

@brianmario here's the gist ready to clone https://gist.github.com/1936957

@thbar

And here's a related conversation: http://stackoverflow.com/questions/8790874/load-data-local-infile-causes-malformed-packet-error-with-mysql2-gem

Maybe some installations of mysql do not exhibit the same default behaviour in this case.

@pospischil

Looks like this can also raise a different error: "Mysql2::Error: Lost connection to MySQL server during query".

(see also #69).

@sodabrew
Collaborator

Hey everyone, my pull request #252 was just integrated into mysql2 master. Please check that this resolves your load data infile issues. @brianmario I think you can close this ticket!

@brianmario brianmario closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.