Bug #17651 mysql & C-API different behavior on LOAD DATA

• Submitted: 22 Feb 2006 15:42
• Modified: 2 May 2006 11:25
• Reporter: Wolfgang Schrecker
• Status: No Feedback
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux (Linux)
• CPU Architecture: Any

[22 Feb 2006 15:42] Wolfgang Schrecker

Description:
on the mysql client

LOAD DATA LOCAL INFILE 'TRX01' INTO TABLE TRX01 FIELDS TERMINATED BY '|' 

works just fine.

using the C-API, I get:

MYSQL: 1148 (The used command is not allowed with this MySQL version)

How to repeat:
s.a.

[23 Feb 2006 10:08] Valeriy Kravchuk

Thank you for a problem report. Please, send the exact C API test case you had used.

[23 Feb 2006 10:37] Wolfgang Schrecker

I was not sure about the ';' at the end of the statement.
tried it with and without

result:
~/bin/testcase
Database 'DataPlus' at 'localhost' successfully opened!
QUERY: LOAD DATA LOCAL INFILE 'TRX01' INTO TABLE TRX01 FIELDS TERMINATED BY '|';
LOAD FAILED

MYSQL: 1148 (The used command is not allowed with this MySQL version)
closing database!
-------------------------------------------------------------------------
here is the code:

#include <stdio.h>

#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>

#define _GNU_SOURCE

#include <my_global.h>
#include <mysql.h>

#include <getopt.h>
#include <string.h>

int	verbose = 5;

void print_mysql_error( MYSQL* conn, char* message )
{
	fprintf( stderr, "%s\n", message );
	if( conn != NULL )
		fprintf( stderr,"MYSQL: %u (%s)\n",
			(unsigned long) mysql_errno(conn), mysql_error(conn ) );
}

/************************************************
 ebc2mysql : Konverter & loader
 ************************************************/

static MYSQL*	conn	=	NULL;
static char*	host	=	"localhost";
static char*	user	=	"dpi";
static char*	pass	=	"XXXX";
static char*	sock	=	NULL;
static unsigned int port = 0;
static unsigned int flags = 0;

static const char* client_groups[] = { "client", NULL };

main(argc,argv)
int argc;
char *argv[];
{
	register int i=0,f=0;
	//FILE *fpein;
	int fd = -1 ;
	char *ps,*tb;

	tb	= "######";

	int num_of_records = 0;
  int	digit_optind = 0;
	int	option_index = 0;
	char	filename[100] = "\0" ;
	char	outfilename[100] = "TRX01" ;

	// first we pick up addition parameters from ~/.my.cnf
	my_init();
	// here get the first error
	//load_defaults( "my", client_groups, &argc, &argv );

				conn = mysql_init( NULL);
				if( conn == NULL )
				{
					fprintf( stderr, "DB init failed\n" );
					exit( -4 );
				}
				if( mysql_real_connect(	conn,	host,user,pass,"DataPlus",port,sock,flags) == NULL )
				{
					fprintf( stderr, "connect to DB failed\t %u (%s)\n",
							mysql_errno( conn ), mysql_error( conn ) );
					exit( -3 );
				}
				else if( verbose )
					fprintf( stderr, "Database '%s' at '%s' successfully opened!\n", "DataPlus", host );

	if( conn != NULL )
	{
		if( strlen(outfilename) > 0 ) 
		{
			char querystring[200];
			sprintf( querystring,
				"LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY '|';",
					outfilename, outfilename );

			if( verbose > 2 )
				fprintf( stderr, "QUERY: %s\n", querystring );

			if( mysql_query( conn, querystring ) != 0 )
				print_mysql_error( conn, "LOAD FAILED\n" );
			else
				fprintf( stderr, "LOAD succeeded: %lu rows affected",
					(unsigned long) mysql_affected_rows( conn ) );
		}

		mysql_close(conn) ;

		if( verbose )
			fprintf(stderr,"closing database!\n" );
	}

	exit(0);
}

[27 Feb 2006 8:57] Wolfgang Schrecker

after consulting the newest DuBois Mysql book,
I found I have to explicitly set the option with

mysql_options( conn, MYSQL_OPT_LOCAL_INFILE , (const char*) &option );

which reduces the issue to the question, why are such defaults different for the C-API and the mysql client ???

[2 Apr 2006 11:25] Valeriy Kravchuk

C-API is not for client-side programs only, but also for, say, application servers. Why should add security risks for the application server by default? mysql is a client-side utility. It can be one of the reasons...

Anyway, for me it looks like everything really needed to solve this problem is documented at http://dev.mysql.com/doc/refman/5.0/en/load-data.html and http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html. Your ideas on what should be changed/added in any of this manual pages are welcomed, though.

[2 May 2006 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".