LOAD DATA LOCAL INFILE broken in some platforms

Bug #821715 reported by Daniel Nichter on 2011-08-05
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Brian Fraser

Bug Description

Imported from http://code.google.com/p/maatkit/issues/detail?id=1347

Quoting the original:

In mk-archiver, when I enable --bulk-insert, it generates LOAD DATA LOCAL INFILE statements with a parameter placeholder for the filename. This is not a valid use of parameters for MySQL.

DBD::mysql::st execute failed: The used command is not allowed with this MySQL version [for Statement "LOAD DATA LOCAL INFILE ? INSERT INTO TABLE ..." with ParamValues: 0='/var/folders/wv/wvLOea7cFo4ACQoJYk8I9U+++TI/-Tmp-/LEr7bnwECFmk-archiver'] at /Users/billkarwin/bin/mk-archiver line 4230.

Tested with mk-archiver Ver 1.0.27 Distrib 7540 Changeset 7531.

Tested with MySQL 5.5.12 on Ubuntu Linux 10.04 LTS.

That sounds like DBI is doing a server-side prepared statement, and our assumption is that we should be doing client-side "emulated" prepared statements. What DBI and DBD::mysql version? One version did server-side by default.

perl version 5.10.0
DBI version 1.616
DBD::mysql version 4.019

I did "export DBI_TRACE=2" and ran my mk-archiver script. I saw this in the output:

imp_dbh->use_server_side_prepare: 0

Related branches

tags: added: wrong-sql
Changed in percona-toolkit:
status: New → Triaged
Brian Fraser (fraserbn) on 2012-08-20
summary: - pt-archiver --bulk-insert uses invalid SQL parameter
+ LOAD DATA LOCAL INFILE broken in some platforms
Brian Fraser (fraserbn) on 2012-08-31
Changed in percona-toolkit:
assignee: nobody → Brian Fraser (fraserbn)
Brian Fraser (fraserbn) on 2012-08-31
Changed in percona-toolkit:
status: Triaged → In Progress
Brian Fraser (fraserbn) on 2012-09-28
Changed in percona-toolkit:
milestone: none → 2.1.5
Daniel Nichter (daniel-nichter) wrote :

I want to solve this different. Always adding mysql_local_infile is probably not a good idea because we don't know what affect that my have (http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html) and we just should force optional things on users. Plus, this issue hasn't been widespread. Iirc it just came up when you (Brian) were testing stuff on your Ubuntu 12 box when we were working on stablize-test-suite. Apart from that, I'm not aware if any users or customers have ever reported this. And furthermore, this only affects 2 tools, pt-archiver and pt-upgrade, when they're ran with options that cause them to LOAD DATA (apart from our test suite which we've already conditionalized).

So here's the "fix": simply document in pt-archiver and pt-upgrade that you (the user) needs to make sure that LOAD DATA actually works on your system if you use these options. "These" options are pt-archiver --bulk-insert and pt-upgrade --compare-results-method=rows.

In general, in cases like this we can only auto-detect and auto-warn the user when we have a non-trivial way of actually testing if the thing in question works or note. To test this requires actually doing LOAD DATA (because iirc we found that there's a mix of options and whatnot that ultimately determine if LOAD DATA works), but we can't actually do that because we can't create test tables for such purposes.

Daniel Nichter (daniel-nichter) wrote :

Change of plans due to IRC conversation:

[11:23am] brian: It doesn't just affect ubuntu 12. It affects gentoo and openSUSE, and Windows as well. And the mysql docs say this...
[11:23am] brian: >If you build MySQL from source but do not invoke configure with the --enable-local-infile option, LOAD DATA LOCAL cannot be used by any client unless it is written explicitly to invoke mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0). See Section, “mysql_options()”.

[11:27am] Daniel: so basically the problem is: if mysql was compiled from source without --enable-local-infile, the only way to do "load data local" is by specifying mysql_local_infile=1 in the dsn?

[11:28am] brian: Daniel: From Perl, yes.

[11:32am] Daniel: brian: in general, forcing mysql_local_infile=1 is not a good approach becuase someone or some distro my purposely have mysql without --enable-local-infile. if we force mysql_local_infile=1 then we override and ignore their attempt to thwart this feature. so we need a way to allow the user to include it, which offloads the responsibility from us to them. I don't want yet another option in the tools, so the easiest and best way is probably a new DSN option: l (that's an ell, not eye; i don't think that's used anywhere). then...
[11:33am] Daniel: DSNParser::get_cxn_params() if $info->{l} then append the mysql_local_infile=1 to the dsn
[11:33am] Daniel: brian: on second thought, let's use L instead of l and just make it clear on any system and font

Brian Fraser (fraserbn) on 2012-11-06
Changed in percona-toolkit:
status: In Progress → Fix Committed
Brian Fraser (fraserbn) on 2012-11-16
Changed in percona-toolkit:
status: Fix Committed → Fix Released

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-411

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers