kfmfe04 kfmfe04 - 6 months ago 19
MySQL Question

Mysql 5.5 LOAD DATA INFILE Permissions

ERROR The used command is not allowed with this MySQL version

I am having problems migrating some mysqlclient C++ code from Mysql 5.1 to 5.5 (using soci). The C++ part is not so relevant - the problem is writing some mysqlclient code which can successfully do a LOAD DATA INFILE on MySQL 5.5.

Here are my notes (LOAD DATA INFILE fails, but normal queries are ok):


  1. The code below works fine on Mysql 5.1, gcc 4.6.1, Oneiric

  2. The same code fails on Mysql 5.5, gcc 4.7.2, Quantal

  3. If I LOAD DATA INFILE from mysql (the command-line client), it works fine (I have updated my.cnf with local-infile=1)

  4. mysql> show variables like '%local_infile%'; results in ON



It would be great if there were a SOCI or a configuration solution to this, but if someone has managed to get this to work with libmysqlclient, that would be great to know, too...




#include <soci.h>
#include <mysql/soci-mysql.h>
#include <string>
#include <iostream>

using soci::use;

using namespace std;
using namespace soci;

main()
{
string val =
"mysql://" +
"host=127.0.0.1" +
" dbname=tmp_db" +
" user=root" +
" password=open_sasame";

int sum;

session sql( val );
sql << "SELECT 1+1", into( sum );
cerr << "RESULT=" << sum << endl; // works fine

// NEXT LINE FAILS WITH:
// The used command is not allowed with this MySQL version
sql << "LOAD DATA LOCAL INFILE '/tmp/junk3.txt' INTO TABLE tmp_db.example_tbl FIELDS TERMINATED BY '|' LINES TERMINATED BY '\\n'";
}

Answer

The answer is, we need the following line of code:

mysql_options( &mysql, MYSQL_OPT_LOCAL_INFILE, 0 );

inserted between mysql_init() and mysql_real_connect().

Below is a snippet of C code for reference. Note that SOCI's mysql backend can be patched with this line of code for it to work.

Tested and works on Mysql 5.5, gcc 4.7.2, Quantal.

#include <mysql.h>
#include <stdio.h>

main()
{
  MYSQL mysql;

  mysql_init( &mysql );
  mysql_options( &mysql, MYSQL_OPT_LOCAL_INFILE, 0 );
  if ( !mysql_real_connect( &mysql,"127.0.0.1","root","open_sasame","tmp_db",0,NULL,0 ))
  {
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
      mysql_error( &mysql ));
  }

  if ( mysql_query( &mysql, "LOAD DATA LOCAL INFILE '/tmp/junk4.txt' "
    "INTO TABLE tmp_db.example_tbl FIELDS TERMINATED BY '|' "
    "LINES TERMINATED BY '\\n'" ))
  {
    fprintf( stderr, "ERROR DURING LOAD DATA LOCAL INFILE\n" );
  }

  mysql_close( &mysql );
}