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!

*Example string that works* Always provide complete `drive:/path/filename`. *What comes back after Execute Reader* Pay attention - the filename in the error message does not match those in the query. — Akina, Apr 14 '23 at 05:12
I have reworded my question a little, but that is exactly my point. "The filename in the error message does not match those in the query" - That is the issue, I have not touched the string and the C# Reader is appending the default secure-file-priv path to it — Mi-krater, Apr 14 '23 at 05:38
Where your reader takes the path `C:\ProgramData\MySQL\MySQL Server 8.0\Data\DBNAME` from? it is specified in some settings? if so then add final slash to this setting.. — Akina, Apr 14 '23 at 05:50
As far as I know it doesn't take it from anywhere. I pump in "LOAD DATA INFILE '_FILEPATH.txt_'" And the error that comes back tells me that it is looking for "LOAD DATA INFILE C:\ProgramData\MySQL\MySQL Server 8.0\Data\DBNAME\'_FILEPATH.txt_'" And I just don't know why it is doing this — Mi-krater, Apr 14 '23 at 05:54
Have you tried to specify complete filepath in LOAD DATA? `LOAD DATA INFILE 'X:/folder/FILEPATH.txt' ..` — Akina, Apr 14 '23 at 05:56
Yeah, that is exactly what am I am doing, substitute _FILEPATH_ for my filepath — Mi-krater, Apr 14 '23 at 05:59
Strange, usually the error message should be "Can't get stat of ..", and also there should be a slash after database name. — Georg Richter, Apr 19 '23 at 14:50
It is very strange and there should be, but that is how the error I get looks! — Mi-krater, Apr 19 '23 at 23:58

score 0 · Answer 1 · answered Apr 20 '23 at 00:04

0

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.

answered Apr 20 '23 at 00:04

Mi-krater

299
1
3
16

Did you tried with Pomelo? – Leandro Bardelli Apr 20 '23 at 00:11
I have not tried with Pomelo since I manged to get this working. Does Pomelo also work well for this? – Mi-krater Apr 20 '23 at 00:36
I found after several years that the only way to work with MySQL is not using their official driver, instead Pomelo and Entity Framework – Leandro Bardelli Apr 20 '23 at 00:37
You can found it at nuget – Leandro Bardelli Apr 20 '23 at 00:38
1

Hmmm, that is a good point, I will keep that in mind with my code moving forward! – Mi-krater Apr 20 '23 at 00:48

score 0 · Answer 2 · answered Apr 23 '23 at 20:44

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.

Sorry for the late response, but even after trying all those things and verifying that that the secure_file_priv was still blank I still had the issue. I am going to assume it is something weird. But for now just doing it the other way has worked for me — Mi-krater, May 29 '23 at 23:34

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

2 Answers2