Mysql 5.5加载数据INFILE权限 [英] Mysql 5.5 LOAD DATA INFILE Permissions

问题描述

错误此MySQL版本不允许使用此命令

我有一些mysqlclient C ++代码从Mysql 5.1迁移到5.5 （使用社会）。 C ++部分不那么相关 - 问题是编写一些mysqlclient代码，可以在MySQL 5.5上成功执行LOAD DATA INFILE。

这是我的笔记（LOAD DATA INFILE但是正常的查询是确定的）：

1. 下面的代码适用于Mysql 5.1，gcc 4.6.1，Oneiric




2. 同样的代码在Mysql 5.5，gcc 4.7.2，Quantal

   我从mysql（命令行客户端）LOAD DATA INFILE，它工作正常（我更新了my.cnf与local-infile = 1）




3. mysql >显示变量如'％local_infile％';会导致ON

如果有SOCI或配置解决方案，设置为使用libmysqlclient工作，这将是巨大的知道，太...

---

  #include< soci.h> 
 #include< mysql / soci-mysql.h> 
 #include< string> 
 #include< iostream> 
 
使用soci :: use; 
 
 using namespace std; 
 using namespace soc; 
 
 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; //工作正常
 
 //下一行失败：
 //此MySQL版本不允许使用的命令
 sql<< LOAD DATA LOCAL INFILE/tmp/junk3.txt'INTO TABLE tmp_db.example_tbl由'| \\'终止的'\\ n'终止的字段; 
} 
  

解决方案

代码如下：

  mysql_options（& mysql，MYSQL_OPT_LOCAL_INFILE，0）; $  mysql_init（）和<$>之间插入
  

c $ c> mysql_real_connect（）。

下面是一段C代码片段供参考。请注意，SOCI的mysql后端可以用这行代码修补，以使其工作。

测试并在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 ，无法连接到数据库：错误：％s\\\
，
 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，LOAD DATA LOCAL INFILE\\\
 
} 
 
 mysql_close（& mysql）; 
} 
  

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'";
}

解决方案

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 );
}

这篇关于Mysql 5.5加载数据INFILE权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！