I'm trying to upload a CSV file into MySQL using a PowerShell script. I'm working on a Windows 10 machine and using MySQL 8.0. Every time I run the script from a PowerShell console, I get the following error:
Exception calling "ExecuteNonQuery" with "0" argument(s): "Loading local data is disabled; this must be enabled on both the client and server sides"
The PowerShell script is as follows:
[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$mysqlConn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$mysqlConn.ConnectionString = "SERVER=localhost;DATABASE=sql_store;UID=root;PWD=password"
$mysqlConn.Open()
$MysqlQuery = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$MysqlQuery.Connection = $mysqlConn
$MysqlQuery.CommandText = "LOAD DATA LOCAL INFILE 'C:\\Users\\Mau\\Desktop\\test_01.csv' INTO TABLE sql_store.store_list FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (campo1, campo2, campo3);"
$MysqlQuery.ExecuteNonQuery()
I have added the option OPT_LOCAL_INFILE=1 in Edit_Connection -> Advanced and the LOAD DATA command works fine if executed in the workbench.
But, when I try to run the same SQL command using PowerShell, it returns the above error. I've tried to set local_infile= in file my.ini but it doesn't work or maybe I've set it in the wrong place.
Could you please explain me how to enable LOAD DATA on both the server and client sides? what are the options I have to set in the my.ini file to make the PowerShell script run?