Non-WP Custom Site: Exporting to OUTFILE secure-file-priv Error

[CodeSlayer2010]

What issue or error are you experiencing?

I’m learning MySQL and have set up a custom non-WordPress site (as per torquemag), deleted all the WordPress files, and imported a MySQL database not of WordPress origin. I am using MySQL Workbench and attempting to export the file to a TSV (tab-separated CSV) with this code:

/* Export data into TSV */

SELECT * INTO OUTFILE '/tmp/dump.csv'

FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

FROM videos;

When I run the command I get this error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I tried a couple possible solutions, but all failed:

1. Created a my.ini file in the conf/mysql folder as per this video, and added this code:

secure-file-priv=""

2. Renamed file to my.ini.hbs, but no-go. In fact, this is the error message from Local:

3. Ran each of these commands to get the secure file location, but the result is NULL for each:

select @@secure_file_priv;

show variables like "secure_file_priv";

SELECT @@GLOBAL.secure_file_priv;

I’m not sure where to go from here. I just want the SQL export command to be successful and I don’t see anything in the code that should be throwing that --secure-file-prive error.

… To make matters worse, I closed LocalWP, deleted the my.ini.hbs file, restarted Local. But, now the site won’t start and Local keeps throwing the my.ini site can't start error.

References:

• database - How should I resolve --secure-file-priv in MySQL? - Stack Overflow

• Mysql 5.7: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement: LOAD DATA INFILE · Issue #9528 · matomo-org/matomo · GitHub

---

What steps can be taken to replicate the issue? Feel free to include screenshots, videos, etc

1. Create a blank WordPress site by clicking the “+” in LocalWP

2. Delete all the wordpress files in app/public/

3. Import a non-wordpress MySQL file of proprietary/confidental data into Adminer

4. Ensure the database has a table called videos and it has at least 3 columns and 3 rows of data

5. Run the following command sequence:

/* Export data into TSV */

SELECT * INTO OUTFILE '/tmp/dump.csv'

FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

FROM videos;

---

System Details

• Local Version:

• Operating System (OS) and OS version:

---

Local Logs

Attach your Local Logs here (Help Doc - Retrieving Local’s Log)

---

Security Reminder

Local does a pretty good job of scrubbing private info from the logs and the errors it produces, however there’s always the possibility that something private can come through. Because these are public forums, always review the screenshots you are sharing to make sure there isn’t private info like passwords being displayed.

[ben.turner]

We don’t explicitly add that flag when MySQL starts, and a value of NULL means that reading/writing data is disabled.

I think the issue is that you need to explicitly configure MySQL to have a certain folder to write to. I didn’t try this with a blank site, but I think it’s the same process. Basically:

1. Update my.cnf.hbs to point to a specific folder
2. Restart the site so the configuration file is compiled and loaded
3. Ensure the value is set correctly
4. Run your query

I was able to do this with wp_posts data, so maybe this should work for you too?

[CodeSlayer2010]

Thanks for the quick reply @ben.turner ! The export worked, but only when I set secure-file-priv as empty like this:

When I tried to add a Windows-style path like this,

[mysqld]
{{#unless os.windows}}
skip-name-resolve
{{/unless}}

secure-file-priv="C:\Users\erich\Downloads"

I get that error code 1290 again:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.000 sec

I’m testing the SQL commands in MySQL Workbench, but I get the same results in Adminer:

That seems to rule Workbench out as the root of the issue.

Next, I tried a Unix-style path like this,

secure-file-priv="/c/Users/erich/Downloads"

But, that gives me the “Uh-oh! Unable to start site” error from before.

Any ideas? Perhaps this is Windows-specific and somebody can test on a Windows computer?

ALSO: As a side note, I tried adding that to my.cnf.hbs as an attempt before I posted for help originally. I added the secure-file-priv definition immediatedly under the [mysqld] and I also tried adding it at the bottom as the very last statement. Why didn’t any of those attempts work?

[ben.turner]

Oof, this was interesting!

I think there are a couple of things going on, but you probably can get this working by:

1. use either:
   • escaped backslashes
   • forward slashes instead of backslashes
2. use the full path to the secure-file-priv value

Here’s a screenshot of escaping the backslashes:

A little more detail about how I got there. I noticed that when I used single back slashes, my username of ben.turner was getting mangled with the inclusion of the bell character. For example, C:\Users\ben.turner\Downloads was turning the \b into a “bell” escape code. I fixed that by escaping the backslashes, so something like:

C:\\Users\\ben.turner\\Downloads

I would imagine that you would have something similar with \erich being interpreted as <escape-sequence>rich

You could use forward slashes instead, because it seems that Windows converts those correctly, and then you don’t need to type out backslash twice. Anyway, can you give that a shot and let me know how it goes?

[CodeSlayer2010]

Thanks Ben! I’m catching up with work after returning from vacation, so I honestly cannot say when, but hopefully I will find time to test this out soonish