Wrong version of mysql what must I change?

[Pedroski55]

I run a little homework webpage. I use GoDaddy as my webhost. Because I am in China, GoDaddy is often a bit slow. So now I have a Chinese webhost.

I regularly import csv files into the database in GoDaddy. For that I have a little web page. I enter the name of the table to import into and the name of the csv file, click import and it works great! (Got the code from a book.)

Now, on my new, Chinese webhost, I get the following error: (this is the output to my little “output.php”, so I can see what happened. Usually it just says “Success”)

file name is 19BEallstudents.csv
table name is 19BEallstudents
The command for mysql is: LOAD DATA LOCAL INFILE ‘/var/www/vhosts/mywebpage.com/httpdocs/includes/mycsvfiles/19BEallstudents.csv’ REPLACE INTO TABLE 19BEallstudents CHARACTER SET utf8 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES ;

Did we manage to populate a table?
Error importing 19BEallstudents.csv into 19BEallstudents This problem: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version

The only way I could find to discover which version they use is ssh into the webhost:

-bash-4.1$ mysqladmin -V
mysqladmin Ver 8.42 Distrib 5.1.73, for redhat-linux-gnu on x86_64
-bash-4.1$

So it seems I have MySQL version 5.1.73
How can I alter this command for this version??

LOAD DATA LOCAL INFILE '/var/www/vhosts/mywebpage.com/httpdocs/includes/mycsvfiles/19BEallstudents.csv' REPLACE INTO TABLE 19BEallstudents CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES ;
(I can and did import the file from phpMyAdmin, so I am not completely stuck, it’s just, I like to use my little webpage.)

[phdr]

Assuming you are using the PDO extension, you must enable LOAD LOCAL INFILE. From the php.net PDO MySql driver documentation https://www.php.net/manual/en/ref.pdo-mysql.php -

PDO::MYSQL_ATTR_LOCAL_INFILE (integer)
Enable LOAD LOCAL INFILE.

Note, this constant can only be used in the driver_options array when constructing a new database handle.

[Pedroski55]

Thanks for your reply!

This is my DB connector. I got it from a PDO login source code example on sourcecodesters.com.

Where should I put the stuff you mention above? In options I presume.

<?php

$host = 'localhost';
$db   = 'allstudentsdb';
$user = 'myusername';
$pass = 'mypassword;
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $options);

?>

On stackoverflow I saw:

`PDO::MYSQL_ATTR_LOCAL_INFILE => true,`

Or should that be

`PDO::MYSQL_ATTR_LOCAL_INFILE (1)`