Databases
StackOverflow2013
Postsdbo
POWhy LOAD DATA LOCAL INFILE will work from the CLI but not in application?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy LOAD DATA LOCAL INFILE will work from the CLI but not in application?
    primarykey
    data
    text
    <p>The problem:</p> <p>My C++ application connects to a MySQL server, reads the first/header line of each db export.txt, makes a create table statement to prepare for the import and executes that against the database (no problem with that, the table appears just as intended) -- but when I try and execute the LOAD DATA LOCAL INFILE to import the data into the newly created table, I get the error "The used command is not allowed with this MySQL version". But, this works on the CLI! When I execute this command on the CLI using <code>mysql -u &lt;user&gt; -p&lt;password&gt; -e "LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';"</code> it works flawlessly?</p> <p>The Situation:</p> <p>My company gets a large quantity of database exports (160 files/10gb of .txt files that are '|' delimited) from our vendors on a monthly basis that have to replace the old vendor lists. I am working on a smallish C++ app to deal with it on my work desktop. The application is meant to set up the required tables, import the data, then execute a series of intermediate queries against multiple tables to assemble information in a series of final tables, which is then itself exported and uploaded to the production environment, for use in the companies e-commerce website.</p> <p>My Setup:</p> <p>Ubuntu 12.04</p> <p>MySQL Server v. 5.5.29 + MySQL Command Line client</p> <p>Linux GNU C++ Compiler</p> <p>libmysqlcppconn is installed and I have the required mysqlconn library linked in.</p> <p>I have already overcome/tried the following issues/combinations:</p> <p>1.) I have already discovered (the hard way) that LOAD DATA [LOCAL] INFILE statements must be enabled in the config -- I have the "local-infile" option set in the configuration files for both client and server. (fixed by updating the /etc/mysql/my.cnf with "local-infile" statements for the client and server. NOTE: I could have used the --local-infile=1 to restart the mysql-server, but this is my local dev environment so I just wanted it turned on permanently)</p> <p>2.) LOAD DATA LOCAL INFILE seems to fail to perform the import (from the CLI) if the target import file does not have execute permissions enabled (fixed with chmod +x target_file.txt)</p> <p>3.) I am using the mysql root account in my application code (because its my localhost, not production and this particular program will never run on a production server.)</p> <p>4.) I have tried executing my compiled binary program using the sudo command (no change, same error "The used command is not allowed with this MySQL version")</p> <p>5.) I have tried changing the ownership of the binary file from my normal login to root (no change, same error "The used command is not allowed with this MySQL version")</p> <p>6.) I know the libcppmysqlconn is working because I am able to connect and perform the CREATE TABLE call without a problem, and I can do other queries and execute statements</p> <p>What am I missing? Any suggestions? Thanks in advance :)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShypervisor666
    1. UShypervisor666
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CODid you try to pass the full real path of file `myfile.txt` ? Did you look into the MySQL server's log files?
      singulars
      1. POWhy LOAD DATA LOCAL INFILE will work from the CLI but not in application?
      1. USBasile Starynkevitch
    2. COMore importantly, and since in practice deployed MySQL servers run on different machines than the MySQL client application, can't you rewrite your application to issue plain `INSERT` statements? Also, are you sure that you did not reach some limit on the size of the request's data?
      singulars
      1. POWhy LOAD DATA LOCAL INFILE will work from the CLI but not in application?
      1. USBasile Starynkevitch
    3. CO@Basile Starynkevitch : Actually yes I did use the full path to the myfile.txt but still didn't work... although I should look through the MySQL logs and see if I can find anything. I understand what you are saying about in practice MySQL servers run on different machines -- however this is a support task to a web server environment -- it will not run in production, it only runs on my local machine. And yes the first thing I tried was parsing the files and creating inserts -- which takes FOREVER compared to the LOAD DATA INFILE method.
      singulars
      1. POWhy LOAD DATA LOCAL INFILE will work from the CLI but not in application?
      1. UShypervisor666
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload