MYSQL secure-file-priv is set to blank, but when running DbDataReader ExecuteReader it behaves as if it is not

Question

I have set secure-file-priv="" in my.ini file If I run the SQL block in MySQL WorkBench, it runs perfectly.

However, when C# actually runs the DbDataReader ExecuteReader, I get an error. The error tells me that the file doesn't exist, but that's because it appends the default path ("'C:\ProgramData\MySQL\MySQL Server 8.0\Data") to the beginning of the path< /strong> (The command text is exactly the same as the SQL below)

Valid string examples

LOAD DATA INFILE '_FILEPATH.txt_' INTO TABLE tablename FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES (xxx, yyy, zzz)

Error returned after executing Reader, now with added section

MySqlException: File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\DBNAME_FILEPATH.txt_' not found (OS Error 2 - No such file or directory)

I don’t want it to contain“C:\ProgramData\MySQL\MySQL Server 8.0\Data\DBNAME", I don't understand why this is happening. Any help would be greatly appreciated!

Answer 1

This is not the answer to my question why it does weird things. However, I solved my problem using a different approach (by executing a Load-Infile).

I chose to use the MySQL client - MySQL Bulk Loader instead of using dbdatareader

string connectionString = Context.Database?.GetDbConnection().ToString();
        connectionString = "MY_CON_STRING"


        using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
        {
            connection.Open();
            MySql.Data.MySqlClient.MySqlBulkLoader loader = new MySql.Data.MySqlClient.MySqlBulkLoader(connection);
            loader.TableName = "TABLENAME";
            loader.FieldTerminator = "\t";
            loader.LineTerminator = "\r\n";
            loader.NumberOfLinesToSkip = 1;
          
            // skip header row 
            loader.FileName = filePath;
            int rowsInserted = loader.Load();
            connection.Close();
        }

So, while I still want to know the answer to my question, this worked for me.