MYSQL secure-file-priv set to blank, but when running DbDataReader ExecuteReader it is acting like it is not

Question

I have set secure-file-priv="" in the my.ini file And if I run my SQL chunk inside MySQL WorkBench it works perfectly.

However when C# actually runs the DbDataReader ExecuteReader I get an error back in return. The error is telling me that that file doesn't exist, but that is because it is appending the default path on the begining of the path ("'C:\ProgramData\MySQL\MySQL Server 8.0\Data") (The command text is the exact same as the SQL below)

Example string that works

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

Error that comes back after Execute Reader, now with the added section

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

I do not want it to include "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DBNAME" and I don't see why it is. Any help would be appreciated!

Answer 1

This is not an answer to my question for why it is doing something strange. However I solved my problem (with doing the Load-Infile) using a different method all together.

Rather than using a dbdatareader I have opted to instead using MySQL Client - MySQL Bulk Loader

        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 would still would like to know the answer to my question, this has worked for me.

Answer 2

The reason why the default path is being appended to the file path in your C# code is because the MySQL server is using the default value for the secure-file-priv option. This option restricts the location from which files can be loaded using the LOAD DATA INFILE statement, and if it is not set, the server uses the default value of C:\ProgramData\MySQL\MySQL Server 8.0\Data\ on Windows.

Setting secure-file-priv to an empty string in the my.ini file removes this restriction and allows you to load files from any location on the server. However, this change may not have taken effect in your C# code because the MySQL server needs to be restarted for the changes in the my.ini file to take effect.

To verify that the secure-file-priv option has been correctly set to an empty string, you can use the following SQL statement in MySQL Workbench:

`SHOW VARIABLES LIKE 'secure_file_priv';`

This should return an empty value for the Value column.

If the secure-file-priv option has been correctly set and the MySQL server has been restarted, but you are still encountering the same error in your C# code, you may want to verify that the file path you are using in your LOAD DATA INFILE statement is correct, and that the file exists in that location. You can also try specifying an absolute file path in your C# code, rather than a relative file path, to ensure that the correct file is being loaded.