LOAD DATA LOCAL INFILE file request rejected due to restrictions on access (ODBC)-CodePudding

Oh my, how do I even start asking this question.... I have a feeling this has been answered more than ones but I'm too dumb to understand the answers.... So I'm sure I'll be roasted for lots of reasons, but I'm feeling brave today.

I'm trying to automatically update my Database with a .csv file I receive daily. Biggest issue I have is that I have no coding experience and my SQL is extremely limited so when I search for help regarding this issue I .... don't really know what to do with the information.

In order to get Power Automate (desktop) to connect to the server I had to install ODBC Data Sources to create a connection string. Then I tried a simple query to make sure i have connection, and this works fine.

Now, for the actual query I want to run;

"LOAD DATA LOCAL INFILE 'C:\Automation\Lagerførte Artikkler.csv' REPLACE INTO TABLE ......

I get this error;

Error in SQL statement ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.5.18-MariaDB-1:10.5.18 maria~ubu2004-log
LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

Detailed Error;

ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.5.18-MariaDB-1:10.5.18 maria~ubu2004-log]LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.: Microsoft.Flow.RPA.Desktop.Modules.SDK.ActionException: Error in SQL statement ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.5.18-MariaDB-1:10.5.18 maria~ubu2004-log]LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. ---> System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.5.18-MariaDB-1:10.5.18 maria~ubu2004-log]LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.DatabaseActions.ExecuteSQLStatement(Variant connectionString, Variant sqlConnectionVariable, Variant sqlCommand, Variant& result, Int32 timeout, Int32 getConnection)
   --- End of inner exception stack trace ---
   at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.ExecuteSqlStatement.Execute(ActionContext context)
   at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

If I change the path to something non existing I get same error.

If I run this query in HeidiSQL it works fine. Also tried to run it through SQL plugin for VS, and this also works fine. (The pathing then is C:\Users\UNRAID~1\DOCUME~1\AUTOMA~1\LAGERF~1.CSV)

If I delete the LOCAL part in the query I get this error;

Access denied for user 'cncportalenno01'@'%' (using password: YES)

What I don't understand is how this user has no access when it clearly has access when going through heidi or vs ? (This might be a dumb take, but as I said, I'm .... lost)

Any help, or guidance wound be greatly appreciated, spent 20h on this f... issue already.

Also tried to put the csv file in Public (Users) folder and just on C:

There is also this checkbox in ODBC Data source config which states "Enabled LOAD DATA LOCAL INFILE statements" but this is an option which do not save. How to overwrite this setting I have no clue. ODBC Settings

CodePudding user response：

I managed to get this bloddy thing fixed by using "MariaDB ODBC 3.1" Driver rather than "MySQL ODBC 8.0 Driver".....

Relief and anger at same time sure feels wierd.

CodePudding user response：

The server needs local_infile=1 enabled. Given HeidiSQL worked we can assume this is set.

The second requirement is the client connection needs to connect with a local_infile flag set.

In ODBC, this is the OPTIONS with bit 7 set aka 128 (2^7).