LOAD DATA LOCAL INFILE broken in some platforms
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Brian Fraser |
Bug Description
Imported from http://
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/
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-
Related branches
- Daniel Nichter: Approve
-
Diff: 512 lines (+132/-45) (has conflicts)14 files modifiedbin/pt-archiver (+29/-1)
bin/pt-upgrade (+25/-1)
lib/DSNParser.pm (+5/-1)
lib/PerconaTest.pm (+7/-1)
lib/Sandbox.pm (+13/-0)
sandbox/test-env (+1/-1)
t/lib/CompareResults.t (+0/-2)
t/lib/DSNParser.t (+38/-2)
t/pt-archiver/basics.t (+0/-1)
t/pt-archiver/bulk_insert.t (+2/-5)
t/pt-archiver/bulk_regular_insert.t (+2/-5)
t/pt-upgrade/basics.t (+3/-15)
t/pt-upgrade/warnings.t (+1/-8)
util/check-load-data (+6/-2)
tags: | added: wrong-sql |
Changed in percona-toolkit: | |
status: | New → Triaged |
summary: |
- pt-archiver --bulk-insert uses invalid SQL parameter + LOAD DATA LOCAL INFILE broken in some platforms |
Changed in percona-toolkit: | |
assignee: | nobody → Brian Fraser (fraserbn) |
Changed in percona-toolkit: | |
status: | Triaged → In Progress |
Changed in percona-toolkit: | |
milestone: | none → 2.1.5 |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
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.