#1415 Insert data from a textfile into table ERROR (docs!)

[Anonymous]

Here is the error I get now with the new stable version
that the older versions never had when loading the data
tables from a text file:

Error

SQL-query :

LOAD DATA LOCAL
INFILE 'C:\\PHP\\uploadtemp\\php4F.tmp' REPLACE INTO
TABLE `colors`
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

MySQL said:

#1148 - The used command is not allowed with this
MySQL version

lwgreyhounds@lwgreyhounds.com

[Alexander M. Turek]

Logged In: YES
user_id=418833

This is not a bug, your MySQL server settings just don't
allow you to use the LOCAL keyword.

I think, we need some documentation about this issue.

[Marc Delisle]

Logged In: YES
user_id=210714

Alexander,

maybe we could intercept error 1148 and put a reference to
some FAQ.

[Bob Hairgrove]

Please reopen this -- I am having the same trouble with LOAD DATA LOCAL INFILE on my local MySQL server getting this error #1148, even though the server is showing "local_infile=ON". It ONLY happens in phpMyAdmin. Thank you!

Problem: Cannot import CSV files through phpMyAdmin with LOAD DATA LOCAL INFILE

1. My local MySQL server has "local_infile=1" (set in /etc/mysql/my.cnf for [mysqld] and [mysql] and also for [client]).
2. I have the schema and table selected, then I click on "Import".
3. The file is in ISO-8859-1 character encoding. However, I cannot import it with anything but UTF-8 because PMA gives me an error about "compressed files" (the file isn't compressed).
4. When I convert the file encoding in a text editor to UTF-8, it builds the "LOAD DATA..." command but fails with error "#1148 - The used command is not allowed with this MySQL version"
5. Attempting to run a LOAD DATA LOCAL INFILE from a query window which runs successfully on the school's database (with an older version of phpMyAdmin), it also fails with the same error message.
6. Executing the same LOAD DATA query from the command-line client "mysql" works perfectly, as expected.
7. In config.inc.php I have added the line:
   $cfg['Import']['ldi_local_option'] = TRUE;
   But it doesn't make any difference.

=================================================
System: Ubuntu 12.04.4 LTS (64-bit)
Kernel: Linux 3.6.11-030611-generic x86_64
(newer kernel is necessary due to problems with the wireless adapter
on my HP-envy laptop)
MySQL:
Server: Localhost via UNIX socket
Server version: 5.5.35-0ubuntu0.12.04.2
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
Web server:
Apache/2.2.22 (Ubuntu)
MySQL client version: 5.5.35
PHP extension: mysqli Documentation
=================================================

[Marc Delisle]

Bob,
which phpMyAdmin version?

[Bob Hairgrove]

Sorry, I missed that important bit.
Here it is:

Version information: 3.4.10.1deb1

[Marc Delisle]

Sorry, this is an outdated version, no longer supported.

[Bob Hairgrove]

OK, I am now running this version:

Version information: 4.0.9deb1.precise~ppa.1

Same problem. :(

[Bob Hairgrove]

It is the one in the Ubuntu repositories for 12.04 LTS. Oh well ... I am going to replace it with the latest version and see if the problem goes away.

Thanks for looking at the problem!

[Bob Hairgrove]

Is there a way I can debug how phpMyAdmin is connecting to the server when it tries to execute the LOAD DATA query? I looked in the file /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php and found this (starts on line 148):

$link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
$client_flags = 0;

After that, mysqli_real_connect() is being called (somewhere else). So it SHOULD work ... in php.ini I have error reporting E_ALL | E_STRICT, so I should be seeing a warning if that constant were being used, but not defined. But somehow I suspect that the connection is being made differently without setting the option first.

EDIT: I also checked my php.ini file which has these lines:
mysql.allow_local_infile = On
...
mysqli.allow_local_infile = On

[Bob Hairgrove]

It was suggested to me that this might be a PHP bug, but I can use LOAD DATA LOCAL INFILE in a script called from a terminal:

<?php
// file: load_test.php
$sql = <<<SQLLOAD
LOAD DATA LOCAL INFILE '[some local file...]' 
INTO TABLE [...] 
/* etc. */
SQLLOAD;

$link = mysqli_init();
if ($link) {
  if (mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true)
   && mysqli_real_connect($link,"localhost","myuser","mypwd","iuImport")) {
    if (mysqli_query($link, $sql)) {
      echo mysqli_info($link) . "\n";
    } else {
      printf("Error #%d: %s\n", mysqli_errno($link), mysqli_error($link));
    }
  }
}
?>

Calling it like this in a terminal shows:

bob@bobs-HP-ENVY:~$ php load_test.php
Records: 17963  Deleted: 0  Skipped: 0  Warnings: 0

Therefore, the bug cannot be in PHP but must be in phpMyAdmin or else perhaps in the Apache PHP module.

[Marc Delisle]

Bob,
I tried with phpMyAdmin 4.0.9, MySQL 5.5.31, PHP 5.5.7, mysqli. I have no problem importing with the "CSV using LOAD DATA" format. The "Use LOCAL keyword" is ticked. I ensure that "Columns separated with" contains "," because that's the separator in my source file.

The generated statement is "LOAD DATA LOCAL INFILE '/opt/php-upload-tmp/phpWsIMrk' INTO TABLE actor FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'".

[Bob Hairgrove]

And your OS was/is...?

[Marc Delisle]

On the server? this is an old Linux Mandriva 2010.2.

[Marc Delisle]

Bob,
I assume that the hostname, username and password you used in your test script are the same that in your phpMyAdmin configuration, and that this user has the FILE privilege.

[Bob Hairgrove]

No:

"you do not need the FILE privilege to load local files."
(Quote from http://dev.mysql.com/doc/refman/5.5/en/load-data.html)

My user does not have the FILE privilege, which is why we NEED to use the "LOCAL" keyword.

[Marc Delisle]

Sorry, you're right. My test user for this bug does not have the FILE privilege and it still works.

So: does your hostname+username+password in the test script match what you're using in phpMyAdmin?

[Bob Hairgrove]

Yes, of course! But I have changed them in these public message exchanges (for obvious reasons).

[Marc Delisle]

Maybe related: I am using mysqlnd.

[Bob Hairgrove]

I really don't know ... what about this one?
https://bugs.php.net/bug.php?id=54158

Does phpmyadmin use PDO? Or mysqli?

At any rate, mysqli seems to work OK (just not in phpmyadmin).

[Marc Delisle]

In my home page of phpMyAdmin, in the Web server section, I see:
"Database client version: libmysql - mysqlnd 5.0.11-dev"

Do you see something mentionning mysqlnd?

[Marc Delisle]

phpMyAdmin does not use PDO.