Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

"The used command is not allowed with this MySQL version" on LOAD DATA LOCAL INFILE on a shared server

Posted on 2016-09-01
7
Medium Priority
?
182 Views
Last Modified: 2016-10-22
I use 1&1 shared hosting on which I have a MySQL DB. I needed to load a CSV file into a table on the database and I used the PHP script below to successfully load it yesterday. (variables are all defined in the script but left out for obvious reasons). Today I deleted the data out of the table and tried to run the same script to upload the exact same file and I got the following errer:

"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version' in /homepages/1/d23232323/htdocs/importd.php:32 Stack trace: #0 /homepages/1/d23232323/htdocs/importd.php(32): PDO->exec('? LOAD DATA ...') #1 {main} thrown in /homepages/1/d23232323/htdocs/importd.php on line 32"

Everything I find on the web says I need to change configuration settings in MySQL, the problem is: 1. I don't think I have access to these settings on a shared server and 2. It worked fine yesterday.
Can anyone think of a reason why this would happen all of a sudden and what I could possibly do to remedy it?

if(!file_exists($csvfile)) {
    die("File not found. Make sure you specified the correct path.");
}

try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
  OPTIONALLY ENCLOSED BY ".$pdo->quote($fieldenclosed)."
	  LINES TERMINATED BY ".$pdo->quote($lineseparator));

echo "Loaded a total of $affectedRows records from this csv file.\n";

Open in new window

0
Comment
Question by:dannyg280
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Assisted Solution

by:TechieMD
TechieMD earned 664 total points
ID: 41780271
Grab (apt-get install) the php5-mysqlnd driver, replacing php5-mysql. This is a known bug.
0
 

Author Comment

by:dannyg280
ID: 41780289
TechieMD - Thank you for the reply. I have seen that as a solution however I am on a shared server, If that require command line access to the server than I do not have it. It also seems strange that if that really was the issue, I don't understand why it worked fine yesterday...

Is there a way to replace the driver without command line access?
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 672 total points
ID: 41780356
It is common practice for ISP's to disable that functionality - they do it on the Linux servers at my ISP but not Windows.

You will need to take it up with them
1
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dannyg280
ID: 41780456
After an hour on the phone with 1&1 support, the only thing I've been able to confirm is that calling support is a waste of time. He made up about 15 different reasons why it didn't work, most of which were ridiculous....I finally hung up the phone after he decided that the 54 MB file I was importing was actually 55 GB. I repeatedly told him I'm looking at the file on the server and it's 54 MB.

ANyway, when I look at my PHP configuration it appears mysqlnd is installed  - see screenshot.
Screenshot-2016-09-01-14.46.42.jpg
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 664 total points
ID: 41780542
I use and am pleased to recomment ChiHost.  Their tech support "gets it" completely.  I have also had good experiences with HostGator and LiquidHost.  Not so much so with GoDaddy and 1&1, where my support experience has been just like what you've described here.
1
 

Author Comment

by:dannyg280
ID: 41780605
Thanks for the info Ray, I may check them out. Does anyone know why this error message would appear if mysqlnd is indeed installed?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question