Using Mysql LOAD DATA LOCAL INFILE with drush

Question

I want to able to load a huge CSV file directly into the database using LOAD DATA LOCAL INFILE like below:

LOAD DATA LOCAL INFILE 'Subscriber.csv' INTO TABLE temp_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'IGNORE 1 ROWS

If I enter into the mysql via command line using drush sql-cli and run the command above I get;

ERROR 1148 (42000): The used command is not allowed with this MariaDB version

After some research I found out that if you access mysql via the command line using the --local-infile flag, I can be able to use the LOAD DATA LOCAL. Below will be the statement that I use to get into mysql :

mysql --local-infile -u root -p -h mysql

Using the command above will make it possible for me to use LOAD DATA in mysql but i want to be able to use it with drush sql-query. How can I apply the --local-infile flag.

Answer 1

drush sql-query has a not-very-well documented option flag called --extra. This flag is used when connecting via the mysql command to add extra options to the command string. So a drush sql-query --extra '--local-infile' should enable Drush to connect the client with your needed option.