Mysql 5.5 PURPOSE DATA POSITION Permissions

Question

ERROR The command used is not allowed with this version of MySQL.

I am having problems porting some mysqlclient C ++ code from Mysql 5.1 to 5.5 (using soci). The C ++ part is not so important - the problem is writing mysqlclient code that can successfully execute LOAD DATA INFILE in MySQL 5.5.

Here are my notes (LOAD DATA INFILE does not work, but normal queries are fine):

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

• The same code crashes on Mysql 5.5, gcc 4.7.2, Quantal

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

• mysql> show variables of type '% local_infile%'; leads to ON

It would be great if it was a SOCI or configuration solution, but if someone managed to get it to work with libmysqlclient, it would be nice to know ...

---

#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 1

Answer: 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 the SOCI mysql backend can be fixed with this line of code to make it 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 ); }