0

In a Joomla 3 component I use the MySQL "LOAD DATA LOCAL INFILE" command to import large CSV files into a Joomla database table.

When moving to a new server, it is no longer possible to configure MySQL via php.ini like this:

mysqli.allow_local_infile = On

The web host suggests to use this PHP command:

mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

What I would like to achieve is to be able to do this with the Joomla database connection object.

A) Adding a $options parameter to the JConfig object in Joomla's configuration.php did not work:

public $options = array('local_infile' => true); // <-- ??

B) Alternatively, I tried creating another database connection like this:


$option['driver']   = 'mysqli'; // Database driver name
$option['host']     = 'host';   // Database host name
$option['user']     = 'user';   // User for database authentication
$option['password'] = 'pass';   // Password for database authentication
$option['database'] = 'name';   // Database name
$option['prefix']   = 'xxx_';   // Database prefix (may be empty)

$option['option']   = array('local_infile' => true); // <-- ???

$db = JDatabaseDriver::getInstance($option);

Experimentation with replacing local_infile with allow_local_infile, mysqli.local_infile, or MYSQLI_OPT_LOCAL_INFILE does not seem to work either.

Will this even be possible with Joomla? Is there any other best practice for importing large CSV files into a Joomla database?

1 Answer 1

1

Updated to include the more detailed example based on the 1st comment.

I have been able to get the follow statement to work, or at least not fail, in a simple Joomla 3 Model;

$db = $this->getDbo();
if ($db->getconnection() instanceof mysqli)
{
    mysqli_options($db->getConnection(), MYSQLI_OPT_LOCAL_INFILE, true);
}

However I haven't look too hard into how to confirm that it has made the required change in the MySQL connection so you will need to do that with your function.

6
  • I have tried to convert this to Joomla 3 like this - but this did not work either: $connection = $db->getConnection(); if ($connection instanceof JDatabaseMySQLi) { $mysqliConnection = $connection->getDriver()->getConnection(); mysqli_options($mysqliConnection, MYSQLI_OPT_LOCAL_INFILE, true); }
    – cappu
    Jun 30, 2023 at 14:13
  • 1
    I have updated the answer to show a working example as per your comment above.
    – Irata
    Jul 1, 2023 at 8:12
  • FYI: In one of my queries, it seems to be working and is loading the CSV data correctly, but is still throwing the error. - Currently I do not see any side effects.
    – cappu
    Jul 1, 2023 at 17:27
  • On Joomla 4 the driver seems to load correctly, but I get this error message if I run my LOAD DATA LOCAL INFILE ... SQL with this code in an try catch block: '$db->setQuery($sql); $result = $db->execute();' This is the error message: This command is not supported in the prepared statement protocol yet Any ideas?
    – cappu
    Jul 28, 2023 at 19:39
  • You might like to open a new question with the message you are now getting and include what is held in $sql. You can refer/link back to this question for context.
    – Irata
    Jul 29, 2023 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.