dannyg280
asked on
"The used command is not allowed with this MySQL version" on LOAD DATA LOCAL INFILE on a shared server
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/htd ocs/import d.php:32 Stack trace: #0 /homepages/1/d23232323/htd ocs/import d.php(32): PDO->exec('? LOAD DATA ...') #1 {main} thrown in /homepages/1/d23232323/htd ocs/import d.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?
"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/htd
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";
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ANyway, when I look at my PHP configuration it appears mysqlnd is installed - see screenshot.
Screenshot-2016-09-01-14.46.42.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the info Ray, I may check them out. Does anyone know why this error message would appear if mysqlnd is indeed installed?
ASKER
Is there a way to replace the driver without command line access?